Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
There is a workaround ... USE [master] GO SELECT 'DROP DATABASE ['+name+']' FROM sys.databases WHERE name like N'StartsWith_%' Then copy the result to a new query, review the list and hit F5. –  user15298 Nov 13 '12 at 11:33
    
This code looks like something for SQL Server. The question is about PostgreSQL... –  dezso Nov 13 '12 at 11:47

1 Answer 1

up vote 3 down vote accepted

DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual:

DROP DATABASE cannot be executed inside a transaction block.

Whenever you run two commands in a script in pgAdmin, they are automatically wrapped into a transaction. You can explicitly begin and commit transactions, but you cannot send multiple commands at once in one session and "outside" of a transaction.

Similar in psql when called with -c command, per documentation:

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 fed 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 this as a separate command or wrapped into a BEGIN/COMMIT block separately.

You could use the program dropdb from the shell - or write a shell-script with it that drops multiple database in succession.

BTW, the only difference between what you can run from pgAdmin and from psql are the meta-comnmands of psql - which are not SQL. Those are interpreted by psql and not sent to the database engine. You cannot mix meta-commands and SQL. (Well, there are tricks with the the separator meta-command \\, there is an example in the manual ...)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.