CREATE DATABASE cannot run inside a transaction block

I have used turn on autocommit in PostgreSQL and it's working for me.

Here is the query to turn on the autocommit

SET AUTOCOMMIT = ON

Note that this only works for PostgreSQL 9.4 and below

With psql you can also run drop and and create your database in one command by using multiple --command arguments:

psql postgres -c "drop database if exists <your_db_here>;" -c "create database <your_db_here>;"

Note, for postgres 9.5+ you have to use:

psql -c '\set AUTOCOMMIT on'

But I'm going to guess, that what you really wanted to do is destroy the database and recreate it in a single command. Here you go:

printf '\set AUTOCOMMIT on\ndrop database <your_db_here>; create database <your_db_here>; ' |  psql postgres

In Postgres SQL 14.1, pgAdmin query tool, I see this same error when running the create database query with other queries. Running the create database query by itself completes successfully.

I had this issue when trying to execute PostgreSQL scripts in a python program like this:

with psycopg2.connect(
   dbname=database, host=server, port=port, user=user, password=password
) as connection:  
    connection.autocommit = True

what solved the issue for me it that I changed it to:

connection = psycopg2.connect(
    dbname=database, host=server, port=port, user=user, password=password
) 
connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

The actual reason for the problem has not been described yet. It can be found in the PostgreSQL documentation.

"[...] the server executes [the command] as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions."

It makes sense that some statements can not be executed in the same transaction. As of now the original sql command is not provided by @Nikunj K. I happened to run into this issue by creating a default database with a default user: sudo -u postgres psql -c \"CREATE DATABASE my_database; create user my_user with encrypted password 'JgiF?K430$cvi';

@mpoqq has already given a good non-version specific solution. His solution is in my opinion better than changing AUTOCOMMIT on setting that might change over time. It also separates the commands logically from each other which is easier to read.

So in my case it was:

sudo -u postgres psql -c "CREATE DATABASE my_database;" -c "CREATE USER my_user WITH ENCRYPTED PASSWORD 'JgiF?K430$cvi';"

For me, moving the CREATE DATABASE step to a different transaction worked.
This was throwing the error:

"CREATE USER user WITH PASSWORD 'somepassword'; CREATE DATABASE db WITH OWNER user;"

while this doesn't (as two separate transactions):

CREATE USER user WITH PASSWORD 'somepassword';
CREATE DATABASE db WITH OWNER user;