Re: Dropping all tables in a database

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.