Postgresql – Drop multiple databases in one script

pgadminpostgresqlpsqlscripting

I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. When I do:

DROP DATABASE db1;
DROP DATABASE db2;

I get following error:

DROP DATABASE cannot be executed from a function or multi-command string

Is there any way around this problem? I.e., is there any way to run each command separately (like when using "GO" statement on MSSQL), or some other sensible way to easily drop multiple databases? I need this functionality for maintenance scripts during development.

Best Answer

When executing multiple commands in a script in pgAdmin they are automatically wrapped into a transaction. You can explicitly begin and commit transactions, but not with commands that won't run in a transaction context. The manual:

DROP DATABASE cannot be executed inside a transaction block.

Similar in psql when called with -c command. The manual:

If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions.

However, when fed to psql via standard input:

This is different from the behavior when the same string is read from a file or fed to psql's standard input, because then psql sends each SQL command separately.

Because of this behavior, putting more than one SQL command in a single -c string often has unexpected results. It's better to use repeated -c commands or feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example:
psql <<EOF
\x
SELECT * FROM foo;
EOF

So you can use psql with standard input in default autocommit-on mode.
Else, you can only run separate commands.

You could use the shell-command dropdb - or write a shell-script with it that drops multiple databases.

BTW, the only difference between what you can run from pgAdmin and psql are the meta-commands of psql - which are not SQL. Those are interpreted by psql and not the database engine. To switch between meta-commands and SQL, use the separator meta-command \\. There is an example in the manual.

Related Solutions

PostgreSQL – Best Practices for Organizing Databases

Just type it into the "Group" field. Besides the listed options you can type a new group name freely.

It's a setting of pgAdmin, completely independent of the database itself and not stored in the database.

The handling is flexible, but renaming a bunch of "servers" can get tedious.

As to your comment: I repeat what I already wrote in my last answer:

Basically, each connection allows access to a whole database cluster

But if you follow my instructions over there, you connect to the right database automatically. I suggest, you read the manual about Managing Databases for basic understanding.

PostgreSQL – How to Enable Implicit Casts in PostgreSQL >=9.2

One can also just add ?compatible=7.1 to a jdbc connection url (before the 42 release)... people say 'Implicit casts to text were removed for a reason' but in 10 years I have never had an issue with them. (I have fixed other bugs is pgsql though)

https://www.postgresql.org/docs/current/static/sql-createcast.html

\dC
********* QUERY **********
SELECT pg_catalog.format_type(castsource, NULL) AS "Source type",
       pg_catalog.format_type(casttarget, NULL) AS "Target type",
       CASE WHEN castfunc = 0 THEN '(binary coercible)'
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = 'e' THEN 'no'
            WHEN c.castcontext = 'a' THEN 'in assignment'
            ELSE 'yes'
       END as "Implicit?"FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid
     LEFT JOIN pg_catalog.pg_type ts
     ON c.castsource = ts.oid
     LEFT JOIN pg_catalog.pg_namespace ns
     ON ns.oid = ts.typnamespace
     LEFT JOIN pg_catalog.pg_type tt
     ON c.casttarget = tt.oid
     LEFT JOIN pg_catalog.pg_namespace nt
     ON nt.oid = tt.typnamespace
WHERE ( (true  AND pg_catalog.pg_type_is_visible(ts.oid)
) OR (true  AND pg_catalog.pg_type_is_visible(tt.oid)
) )
ORDER BY 1, 2;
**************************
                                         List of casts
         Source type         |         Target type         |      Function      |   Implicit?   
-----------------------------+-----------------------------+--------------------+---------------
 abstime                     | date                        | date               | in assignment
 abstime                     | integer                     | (binary coercible) | no
 abstime                     | timestamp without time zone | timestamp          | yes
...

Best Answer

Related Solutions

PostgreSQL – Best Practices for Organizing Databases

PostgreSQL – How to Enable Implicit Casts in PostgreSQL >=9.2

Related Question