PostgreSQL refuses CREATE DATABASE because a transaction is open
ERROR: CREATE DATABASE cannot run inside a transaction blockShort answer
PostgreSQL does not allow CREATE DATABASE inside a transaction block. Something around your command opened one: a psql run with --single-transaction, a BEGIN in your script, or a driver such as psycopg2 that starts a transaction by itself. Run the command outside any transaction, or switch the connection to autocommit.
Fix it fast
1Find out which cause you have
Find what opened the transaction. Look at how the command is sent: a psql flag, a BEGIN line in the script, or application code using a driver.
grep -n -i -E "begin|start transaction|single-transaction" setup.sql run.sh 2>/dev/null2Match what you see, then jump to its fix
- 1
The command line contains -1 or --single-transaction together with -f or -cpsql runs the script with --single-transaction (-1)Fix - 2
The script has BEGIN; before CREATE DATABASE, or one -c argument holds several statementsSeveral commands are packed into one -c string, or the file has its own BEGINFix - 3
The error comes from psycopg2 code, with no BEGIN in sightA Python driver opened a transaction for youFix
- 1
What does the error look like?
ERROR: CREATE DATABASE cannot run inside a transaction block
Cause 1 of 3
psql runs the script with --single-transaction (-1)
1Check
That option makes psql send BEGIN before the first command and COMMIT after the last. The psql manual says that a command that cannot run in a transaction block makes the whole transaction fail.
grep -rn -E "psql .*(-1|--single-transaction)" . --include=*.sh --include=Makefile2Fix
Remove the option for the script that creates the database, or move CREATE DATABASE into its own psql call.
psql -d postgres -c "CREATE DATABASE appdb"
psql -d appdb -1 -f schema.sql3Verify
List the databases.
psql -d postgres -c "SELECT datname FROM pg_database WHERE datname = 'appdb'"Confirmed by postgresql.org
Cause 2 of 3
Several commands are packed into one -c string, or the file has its own BEGIN
1Check
The psql manual says a -c string is sent as one request, so the server treats it as a single transaction even with several commands in it, unless explicit BEGIN and COMMIT split it up.
grep -n -i -B2 "create database" setup.sql2Fix
Send CREATE DATABASE as its own -c argument or its own line outside any BEGIN ... COMMIT block.
psql -d postgres -c "CREATE DATABASE appdb" -c "CREATE ROLE appuser LOGIN"3Verify
Run the script again and check the result.
psql -d postgres -c "SELECT datname FROM pg_database WHERE datname = 'appdb'"Confirmed by postgresql.org
Cause 3 of 3
A Python driver opened a transaction for you
1Check
psycopg2 starts a transaction at the first command, and its docs name CREATE DATABASE as a command that needs autocommit mode, outside any transaction.
grep -rn "CREATE DATABASE" --include=*.py .2Fix
Switch the connection to autocommit before running the command.
import psycopg2
conn = psycopg2.connect("dbname=postgres user=postgres")
conn.autocommit = True
cur = conn.cursor()
cur.execute("CREATE DATABASE appdb")3Verify
Run the script again.
python create_db.py && psql -d postgres -c "SELECT datname FROM pg_database WHERE datname = 'appdb'"Confirmed by psycopg.org
How do I stop it coming back?
- Keep database creation in its own step, apart from schema scripts that you run in one transaction.
- Use the createdb program, a wrapper around CREATE DATABASE, for setup scripts.
- In driver code, use a separate autocommit connection for administrative commands.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix