Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL refuses CREATE DATABASE because a transaction is open

ErrorERROR: CREATE DATABASE cannot run inside a transaction block

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

  • Applies to PostgreSQL 17, psql 17, psycopg2 2.9
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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/null
  2. 2Match what you see, then jump to its fix

    1. 1The command line contains -1 or --single-transaction together with -f or -cpsql runs the script with --single-transaction (-1)Fix
    2. 2The 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. 3The error comes from psycopg2 code, with no BEGIN in sightA Python driver opened a transaction for youFix

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=Makefile

2Fix

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

3Verify

List the databases.

psql -d postgres -c "SELECT datname FROM pg_database WHERE datname = 'appdb'"

Confirmed by postgresql.org

Did this fix it?

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

2Fix

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.