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.
