H schrieb am 07.08.2023 um 03:17: > I am running PostgreSQL 13.11 and tried to drop all tables in a > database without dropping the database or schema. After logging 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, this is 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 possible bug? David already mentioned that you can use \gexec instead of the ; to run the generated statements directly. Does that user have other objects (e.g. types or sequences) as well? Maybe "DROP OWNED BY xxx;" is an alternative? However, that would really drop _everything_ that the users own - not just tables. In my experience one usually wants to get rid of the other things as well.
