How to trim output from a psql file - equivalent to SET commands in sqlplus

I have the following query, which will generate DML:

SELECT 'drop table ' || table_schema || '.' || table_name ||';'
FROM information_schema.tables
WHERE table_schema = 'myschema'
AND table_name like '%2020%'
AND table_type = 'BASE TABLE';

I put it in a file called gen_drop.sql

When executed like this:

 psql  testdb01 -f gen_drop_tab.sql -L gen_drop_tab.log

The file output will show header and row count, like this:

                   ?column?
-----------------------------------------------
 drop table myschema.tabA_2020_09;
 drop table myschema.tabB_2020_09;
 drop table myschema.tabC_2021_05;

(27 rows)

To avoid this, add the -t flag to your command line. -t is "print tuples only"

psql  testdb01 -f gen_drop_tab.sql -o drop_tab.sql -t

To translate into the world of Oracle, I consider the above to be the equivalent of spooling a file using sqlplus and adding the directives:

set trimspool on
set pages 0
set heading off
set feedback off
set verify off
set echo off

How to set timing in a posgreSQL session

Open session in psql:

psql mydb
psql (11.11)
Type "help" for help.

Toggle timing on/off like this:

mydb=# \timing
Timing is on.
mydb=#  \timing
Timing is off.

How to turn off output generated by psql

I guess this could be viewed as the equivalent to oracles "set" statements in sqlplus, for example "set heading off verify off feedback off echo off":

proddb01=# \dn
           List of schemas
           Name           |  Owner
--------------------------+----------
 sales                    | postgres
 hr                       | postgres
 manufacturing            | postgres
 public                   | postgres
 (4 rows)

Turn off unneccessary output like this:

proddb01=# \t
Tuples only is on.

Try again:

proddb01=# \dn
 sales                    | postgres
 hr                       | postgres
 manufacturing            | postgres
 public                   | postgres

How to execute a *.sql file using psql in a PostgreSQL database

There are two ways:

1. Already connected to the correct database:

postgres=# \connect proddb01
You are now connected to database "proddb01" as user "postgres".
proddb01=# \conninfo
You are connected to database "proddb01" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
proddb01=#

Then use the -i notation in psql to execute your file, like this:

proddb01=# \i yourfile.sql

2. directly from the command line:

proddb01=# psql proddb01 -f yourfile.sql

You can also direct the output to a log file, using the -o flag:

proddb01=# psql proddb01 -f test.sql -o test.log

The -o flag will redirect everything to a log file and not show anything on your screen.
If you would like to see the result of the script on your screen AND save the output in a log file, use the -L flag instead of the -o flag. This will also log the actual query sent to the postgres server:

proddb01=# psql proddb01 -f test.sql -L test.log

Note that no "exit" statement is necessary in your .sql scripts that you execute through psql directly from the command line. psql quits the session automatically.

How to show current connect info in PosgreSQL

[postgres@myserver.mydomain.com ~]$ psql
psql (11.2)
Type "help" for help.

postgres=# \connect musicdb
You are now connected to database "musicdb" as user "postgres".
musicdb=# \conninfo
You are connected to database "musicdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".