Postgres spooling file and other command line options

I wanted to drop all the tables in a particular postgres database, there were around 90 tables, dropping individual tables one by one is not a good idea and is time-consuming as well, 
So decided to create a DROP script and then execute it, the challenge for me was to spool the file in PSQL as it is slightly different from SQL language
In the below example I have spooled drop statements in the file and then executed the same by login to the postgres database

Login to database tech_db using tech_usr 

$ psql -d tech_db -U tech_usr

Password for user tech_usr:

psql.bin (9.6.2.7)

Type "help" for help.

tech_db=#

Check the connection information

 

tech_db=# \conninfo

You are connected to database "tech_db" as user "tech_usr" via socket in "/tmp" at port "5442".

tech_db=#

Check the tables in the database

tech_db=# \dt

                        List of relations

 Schema |             Name              | Type  |      Owner

--------+-------------------------------+-------+-----------------

 public | admin_events                        | table | tech_db

 public | associat_policy               | table | tech_db

 ...

 ...

 ...

 public | origins_tab                   | table | tech_db

(90 rows)

Create Drop script for drop tables 

tech_db=# \o drop_tables.sql

tech_db=# select 'drop table if exists "' || tablename || '" cascade;'   from pg_tables where schemaname = 'public';

tech_db=# \o

you will be able to see file drop_tables.sql is created under current directory

$ ls -lsrth
8.0K -rw-r--r-- 1 technodba technodba 4.9K Jan 24 08:42 drop_tables.sql

Execute script in PSQL command prompt

tech_db-# \o drop_tables.log

tech_db-# \i drop_tables.sql

tech_db-# \o

Now check the tables 

tech_db=# \dt

No relations found.

tech_db=#

Playing with the command line
As we see, by default query wont be written to output file, we have to specifically mention to write query to output file,
this is is how we can do it. We need to use \qecho before query

tech_db=# \o tables.sql

tech_db=# select * from origins_tab;

tech_db=# \qecho select * from origins_tab;

tech_db=# \o

tech_db=# \q

$

$ ls -lsrth

total 4.0K

4.0K -rw-r--r-- 1 technodba technodba 77 Jan 28 04:02 tables.sql

$

$ cat tables.sql

 client_id | value

-----------+-------

(0 rows)

 

select * from origins_tab;

$
at first query we did not use \qecho , it has directly written output and in second query as we used \qecho it has written query

To write the query in the output
Using \set ECHO queries we will be able to write query in output. Below is difference

tech_db=# select * from origins_tab;

 client_id | value

-----------+-------

(0 rows)

 

tech_db=#

tech_db=# \set ECHO queries

tech_db=# select * from origins_tab;

select * from origins_tab;

 client_id | value

-----------+-------

(0 rows)

 

tech_db=#

Feel free to comment and feedback on this. Check out other posts as well.