From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Dropping all tables in a database |
Date: | 2023-08-07 05:50:50 |
Message-ID: | c0841bba-ac20-f9a1-1637-cd1ef5a876d0@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro Horiguchi | 2023-08-07 05:54:42 | Re: How does pg parse 'select '(1,2)'' |
Previous Message | negora | 2023-08-07 05:48:23 | Re: My 1st TABLESPACE |