I am running PostgreSQL 13.11 and tried to drop all tables in a database without dropping the database or schema.
Afterlogging in as the correct user, the following SQL statement does not work:
SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' FROM pg_tables WHERE schemaname = 'public' AND tableowner
='xxx';
The above statement does not drop any tables, nor are there any error messages.
SELECT * FROM pg_tables;
The above shows all tables are still present in the database.
Dropping individual tables works fine but since I need to drop all tables in the database in a develop environment,
thisis not workable.
I had to resort to the following:
-- turn off headers:
\t
SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' FROM pg_tables WHERE schemaname = 'public' AND tableowner
='livraddarpaket';
\g out.tmp
\i out.tmp
The SQL statements above run fine.
Is there some setting I have to change in the database to have the first SQL statement to work or have I run into a
possiblebug?
