pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Dropping all tables in a database
7+ messages / 6 participants
[nested] [flat]

* Dropping all tables in a database
@ 2023-08-07 01:17  H <agents@meddatainc.com>
  0 siblings, 3 replies; 7+ messages in thread

From: H @ 2023-08-07 01:17 UTC (permalink / raw)
  To: PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>

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?








^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Dropping all tables in a database
@ 2023-08-07 01:24  Christophe Pettus <xof@thebuild.com>
  parent: H <agents@meddatainc.com>
  2 siblings, 3 replies; 7+ messages in thread

From: Christophe Pettus @ 2023-08-07 01:24 UTC (permalink / raw)
  To: H <agents@meddatainc.com>; +Cc: PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>



> 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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Dropping all tables in a database
@ 2023-08-07 01:29  Julien Rouhaud <rjuju123@gmail.com>
  parent: Christophe Pettus <xof@thebuild.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Julien Rouhaud @ 2023-08-07 01:29 UTC (permalink / raw)
  To: Christophe Pettus <xof@thebuild.com>; +Cc: H <agents@meddatainc.com>; PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>

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.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Dropping all tables in a database
@ 2023-08-07 01:31  David G. Johnston <david.g.johnston@gmail.com>
  parent: Christophe Pettus <xof@thebuild.com>
  2 siblings, 0 replies; 7+ messages in thread

From: David G. Johnston @ 2023-08-07 01:31 UTC (permalink / raw)
  To: Christophe Pettus <xof@thebuild.com>; +Cc: H <agents@meddatainc.com>; PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>

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.


>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Dropping all tables in a database
@ 2023-08-07 02:19  Thorsten Glaser <tg@evolvis.org>
  parent: H <agents@meddatainc.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Thorsten Glaser @ 2023-08-07 02:19 UTC (permalink / raw)
  To: H <agents@meddatainc.com>; +Cc: PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>

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.

See:
https://evolvis.org/plugins/scmgit/cgi-bin/gitweb.cgi?p=useful-scripts/useful-scripts.git;a=tree;f=S...

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!
                        ****************************************************





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Dropping all tables in a database
@ 2023-08-07 05:50  Thomas Kellerer <shammat@gmx.net>
  parent: H <agents@meddatainc.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Thomas Kellerer @ 2023-08-07 05:50 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.







^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Dropping all tables in a database
@ 2023-08-08 00:59  H <agents@meddatainc.com>
  parent: Christophe Pettus <xof@thebuild.com>
  2 siblings, 0 replies; 7+ messages in thread

From: H @ 2023-08-08 00:59 UTC (permalink / raw)
  To: PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>

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.






^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-08-08 00:59 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-07 01:17 Dropping all tables in a database H <agents@meddatainc.com>
2023-08-07 01:24 ` Christophe Pettus <xof@thebuild.com>
2023-08-07 01:29   ` Julien Rouhaud <rjuju123@gmail.com>
2023-08-07 01:31   ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-08 00:59   ` H <agents@meddatainc.com>
2023-08-07 02:19 ` Thorsten Glaser <tg@evolvis.org>
2023-08-07 05:50 ` Thomas Kellerer <shammat@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox