Drop all tables in postgresql using bash script

Question

I am a novice bash script user. I am trying to execute this postgresql command (which outputs commands that drops tables whose name is like r_395)

SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables where tablename like 'r_395%';

output of this query is:

?column?      
--------------------
 DROP TABLE r_395_0
 DROP TABLE r_395_1
 DROP TABLE r_395_2
 DROP TABLE r_395_3
 DROP TABLE r_395_4
 DROP TABLE r_395_5
 DROP TABLE r_395_6
 DROP TABLE r_395_7
 DROP TABLE r_395_8
 DROP TABLE r_395_9
(10 rows)

using bash with this command:

/bin/su - postgres -c "/usr/bin/psql database -c SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables where tablename like 'r_395%'" > droptables

But I am getting this error:

psql: FATAL:  role "DROP TABLE " does not exist
-bash: tablename: command not found

What am I doing wrong?

araqnid · Accepted Answer · 2011-10-14 10:36:32Z

2

Probably due to the quotes being reinterpreted as you're including a -c. You need something like:

su - postgres -c "/usr/bin/psql database -c \"SELECT 'DROP TABLE ' || ....

i.e. you need to quote the arg to psql's -c option again.

This rapidly turns into a mess. I suggest you instead get the command you want psql to execute produced as output and then just have psql execute stdin (by not passing -c at all). This avoids the quoting hell, and makes testing easier (just take out the pipe to psql and you see what it will be getting). That is:

echo "SELECT 'DROP TABLE ' || tablename ... " | su - postgres -c "psql database"

or use a "here document":

su - postgres -c "psql database" <<EOF
SELECT 'DROP TABLE ' || tablename ...
EOF

answered Oct 14, 2011 at 10:36

araqnid

135k25 gold badges163 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lamostreta Over a year ago

thanks for your kind answer. I also realized that using backslashes solves my problem. But I couldn't answer my question because I needed to wait 8 hours according to Stackoverflow swh. But I learned from your answer a lot, thanks again.

Collectives™ on Stack Overflow

Drop all tables in postgresql using bash script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related