Dropping all tables in a database
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?
On Aug 6, 2023, at 18:17, H <agents@meddatainc.com> wrote:
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?
The first statement just generates a line of text output that contains the statement. There's nothing in it that would cause that statement to be executed.
If you want to create a statement dynamically and then execute it, you can do that with pl/pgSQL:
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Otherwise, the solution is to do as you did: write the output to a file, trim out any extraneous lines, and then use that as a script.
On Mon, Aug 7, 2023 at 9:25 AM Christophe Pettus <xof@thebuild.com> wrote:
On Aug 6, 2023, at 18:17, H <agents@meddatainc.com> wrote:
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?
The first statement just generates a line of text output that contains the statement. There's nothing in it that would cause that statement to be executed.
If you want to create a statement dynamically and then execute it, you can do that with pl/pgSQL:
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Otherwise, the solution is to do as you did: write the output to a file, trim out any extraneous lines, and then use that as a script.
The easiest solution it to simply rely on the \gexec meta-command on
psql (see https://www.postgresql.org/docs/current/app-psql.html) which
is exactly meant for that.
On Sun, Aug 6, 2023, 18:25 Christophe Pettus <xof@thebuild.com> wrote:
On Aug 6, 2023, at 18:17, H <agents@meddatainc.com> wrote:
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?
The first statement just generates a line of text output that contains the
statement. There's nothing in it that would cause that statement to be
executed.If you want to create a statement dynamically and then execute it, you can
do that with pl/pgSQL:https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Otherwise, the solution is to do as you did: write the output to a file,
trim out any extraneous lines, and then use that as a script.
Or in psql execute it using the \gexec meta-command instead of a semi-colon.
David J.
Show quoted text
On Sun, 6 Aug 2023, H wrote:
I am running PostgreSQL 13.11 and tried to drop all tables in a
database without dropping the database or schema.
Comments welcome (especially a fix to the item still in TODO).
bye,
//mirabilos
--
Infrastrukturexperte • tarent solutions GmbH
Am Dickobskreuz 10, D-53121 Bonn • http://www.tarent.de/
Telephon +49 228 54881-393 • Fax: +49 228 54881-235
HRB AG Bonn 5168 • USt-ID (VAT): DE122264941
Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg
****************************************************
/⁀\ The UTF-8 Ribbon
╲ ╱ Campaign against Mit dem tarent-Newsletter nichts mehr verpassen:
╳ HTML eMail! Also, https://www.tarent.de/newsletter
╱ ╲ header encryption!
****************************************************
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.tmpThe 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.
On 08/06/2023 09:24 PM, Christophe Pettus wrote:
On Aug 6, 2023, at 18:17, H <agents@meddatainc.com> wrote:
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?
The first statement just generates a line of text output that contains the statement. There's nothing in it that would cause that statement to be executed.
If you want to create a statement dynamically and then execute it, you can do that with pl/pgSQL:
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Otherwise, the solution is to do as you did: write the output to a file, trim out any extraneous lines, and then use that as a script.
Oops, you are right, thank you. I worked around it by making sure the database to be restored is saved using the options --clean, --if-exists and --no-owner which solve my problem.