Thread: 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. Afterlogging 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, thisis 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 possiblebug?
> 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
possiblebug?
The first statement just generates a line of text output that contains the statement. There's nothing in it that would
causethat 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
thatas 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 possiblebug? > > The first statement just generates a line of text output that contains the statement. There's nothing in it that wouldcause 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 thatas 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.
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=SQL;hb=HEAD 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.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.
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 possiblebug? > The first statement just generates a line of text output that contains the statement. There's nothing in it that wouldcause 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 thatas 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.