How to create a PostgreSQL database

Jul 27, 2021updated Aug 3, 2026

Create a PostgreSQL database with CREATE DATABASE, assign an owner role, verify it with the psql list command, and connect to it.

~~~

When you have PostgreSQL installed you can create a new database by opening the console with:

psql postgres

and then running the command CREATE DATABASE:

CREATE DATABASE databasename;

Don’t forget the semicolon ;

Without the semicolon, psql thinks the statement continues on the next line and just waits. The prompt changes from =# to -# while it waits, which is the clue.

You’ll then see the newly created database by running the \l command:

                    List of databases
     Name     | Owner  | Encoding | ... | Access privileges
--------------+--------+----------+-----+-------------------
 databasename | flavio | UTF8     |     |
 postgres     | flavio | UTF8     |     |
 template0    | flavio | UTF8     |     | =c/flavio
 template1    | flavio | UTF8     |     | =c/flavio

Behind the scenes PostgreSQL does not create the database from nothing. It clones the template1 database, which is why a brand-new database already contains a public schema.

Setting an owner

By default the new database belongs to the role that created it. For an application database, I prefer giving ownership to a dedicated role instead of a person:

CREATE DATABASE notes_app OWNER notes_owner;

Ownership then follows the role built for that job, and it survives people changing laptops or leaving the project. You can fix ownership later with ALTER DATABASE notes_app OWNER TO notes_owner;.

Connecting to the new database

Creating a database does not move you into it. Reconnect with:

\connect databasename

or the short form \c databasename. Run \conninfo after that to confirm which database you are in before creating tables. Skipping this check is how tables end up in the postgres maintenance database by accident.

If the command fails

Two errors come up regularly.

ERROR: permission denied to create database means your role lacks the CREATEDB attribute. An administrator can grant it with ALTER ROLE yourname CREATEDB; or create the database for you.

CREATE DATABASE cannot run inside a transaction block shows up when a migration tool wraps the statement in BEGIN ... COMMIT. Create databases as a separate manual step, and let migrations manage what lives inside the database.

Tagged: Database · All topics

~~~