PostgreSQL CREATE DATABASE Guide with psql and DbSchema
For a PostgreSQL beginner with a running server and nothing of their own in it yet; every command is shown with what psql prints back.
On this page
A PostgreSQL server that was just initialized holds three databases, and all three belong to the server rather than to you. Your tables need one of your own, which is a single statement at the postgres=# prompt:
CREATE DATABASE bookstore;
The account you run it under must be a superuser or hold the CREATEDB privilege, and the statement cannot be executed inside a transaction block. Everything below runs on PostgreSQL 18.
Install PostgreSQL and the psql client
psql is the terminal client that ships with PostgreSQL, and the installer for your operating system brings both:
- Download the installer for Windows, macOS or Linux.
- Run it and keep the "Command Line Tools" component selected, because that component installs
psql. - Open a terminal and ask psql which version it is.
psql --version
On an 18.0 installation the answer is:
psql (PostgreSQL) 18.0
A "command not found" here means the installation directory is missing from your PATH, not that the server failed to install. The number that comes back is psql's own version, which may differ from the server's: once you are connected, SHOW server_version; reports the server.
The server listens on port 5432 by default, and that is the port psql uses when you give it none. If the installer asked you for a port and you changed it, add -p with your port to every command below.
Create the database from psql
Log in as a role that may create databases. The postgres role the installer sets up is a superuser, so it qualifies:
psql -U postgres
Password for user postgres:
Nothing appears on screen while you type the password. Once it is accepted, the postgres=# prompt appears, and the statement from the top of this page runs there:
CREATE DATABASE bookstore;
CREATE DATABASE
What PostgreSQL does with that line is less obvious than the line itself. CREATE DATABASE works by copying an existing database, template1 unless you name another one, so anything you add to template1 is copied into every database created afterwards. An extension you install there once, for example, is present in each new database without being installed again.
You can also copy one of your own databases by naming it as the template:
CREATE DATABASE bookstore_copy TEMPLATE bookstore;
This form fails while anyone else is connected to the database being copied. The manual is explicit about it: "no other sessions can be connected to the template database while it is being copied". Disconnect the other sessions first, or copy from a database nobody uses.
From a shell script the same job is one command instead of a psql session, because createdb is a wrapper around the statement:
createdb bookstore
List the databases to confirm it exists
The \l meta-command lists the databases on the server with "their names, owners, character set encodings, and access privileges". For the names alone, query the catalog:
SELECT datname FROM pg_database ORDER BY datname;
| datname |
|---|
| bookstore |
| postgres |
| template0 |
| template1 |
The three you did not create are the ones the server started with: template1 is the template every new database is copied from, template0 is the pristine copy that is never modified, and postgres is a default database for clients that have to connect somewhere. Where you want more than the names, \l+ adds the database sizes, the default tablespaces and the descriptions to the listing.
Creating a database does not connect you to it. \c does that, and psql confirms the switch:
\c bookstore
You are now connected to database "bookstore" as user "postgres".
The prompt changes to bookstore=#, and every statement from here lands in the new database. Leave the session with \q.
Create the database while you connect with DbSchema
DbSchema is a PostgreSQL client and visual designer that creates the database from the same dialog you use to connect, so setting up a new project costs you one dialog rather than a terminal session.
Pick PostgreSQL from the list
Click Connect to Database in DbSchema and choose PostgreSQL in the Choose Your Database list. DbSchema downloads the PostgreSQL JDBC driver and opens the Connection Dialog with the fields that driver needs.
Fill in the server and name the database
Under Server Location, keep This computer, default port for a server on your own machine, or switch to Remote computer or custom port and give the host. Fill in Database User and Password, and tick Remember to store the password locally. Next to the Database field, Create New asks the server for a new database: that click leaves DbSchema and reaches PostgreSQL itself, exactly as the statement above does. Select the new database in the list and click Connect.
DbSchema then reverse-engineers the database and draws it as a diagram. The diagram belongs to DbSchema, so arranging the tables on it changes nothing in PostgreSQL. Adding an object does: right-click the canvas, choose New Table, and while the connection is open DbSchema executes the statement against the database and lists it in the SQL History panel.
Connecting, reverse-engineering and the diagram are in the free Community Edition, so the whole path above costs nothing to try. Download DbSchema at https://dbschema.com/download.html, and start at the Choose Your Database list with the server you just installed.
