Postgres Create Database: How-To & Examples

To create a database in PostgreSQL, you use the CREATE DATABASE SQL command or the createdb command-line utility. You specify a database name and optionally set owner, encoding, and other properties; the command returns nothing on success and an error if the database already exists or you lack permission.

What Is a PostgreSQL Database?

In PostgreSQL, a database is a named collection of SQL objects: tables, views, functions, indexes, sequences, and data types. Each database is isolated from others on the same server cluster; you cannot directly query a table in another database without cross-database extensions. A single PostgreSQL server instance (called a cluster) typically hosts multiple databases, and each database has its own system catalog (pg_catalog) that stores metadata for that database.

Databases are created by the superuser or a user with the CREATEDB privilege. By default, the initial superuser (often postgres) can create databases. The default database, named postgres, exists for administrative tasks and is a common target for the first connection.

Prerequisites and Permissions

Before you create a database, you need:

• A running PostgreSQL server (local or remote).
• Connection credentials for a user with CREATEDB privilege or superuser status.
• Knowledge of the server host, port (default 5432), and the name of an existing database to connect to (usually postgres).

To check if your user can create databases, run SELECT rolcreatedb FROM pg_roles WHERE rolname = current_user; in psql. If it returns t, you are allowed. The pg_hba.conf file controls authentication; typical local setups use peer or md5 authentication.

Method 1: Using CREATE DATABASE SQL Command

The most direct way is the SQL command. Connect to an existing database (e.g., postgres) using psql or any client, then run:

CREATE DATABASE mydb;

This creates a database named mydb with default settings: owner is the current user, encoding matches the template database (usually UTF8), and tablespace is the default. You can specify options:

CREATE DATABASE mydb
  OWNER = app_user
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE = template0
  CONNECTION LIMIT = 100;

• OWNER: sets the role that owns the database (default: current user).
• ENCODING: character set (default: from template).
• LC_COLLATE and LC_CTYPE: locale settings; must match the server’s available locales.
• TEMPLATE: template database to copy; template0 is recommended for custom locale/encoding.
• CONNECTION LIMIT: max concurrent connections (default: unlimited).

If the database name already exists, you get an error: ERROR: database "mydb" already exists. Use IF NOT EXISTS to suppress the error: CREATE DATABASE IF NOT EXISTS mydb; (supported since PostgreSQL 9.5).

Method 2: Using createdb Command-Line Utility

The createdb program is a wrapper around the SQL command. It is installed with PostgreSQL and works from the shell. Basic syntax:

createdb mydb

Common options include:

• -U username: connect as this user.
• -h host: server host (default: local socket).
• -p port: port (default: 5432).
• -O owner: set owner.
• -E encoding: set encoding (e.g., -E UTF8).
• -T template: use a specific template.
• --lc-collate and --lc-ctype: locale settings.

Example with options:

createdb -U postgres -E UTF8 -O app_user -T template0 mydb

If the database exists, createdb prints an error and exits with a non-zero status. Use --if-not-exists to avoid the error.

Choosing Database Name, Encoding, and Locale

Database names in PostgreSQL must start with a letter or underscore, followed by letters, digits, or underscores. They are case-insensitive when unquoted; quoted names preserve case but are discouraged. Avoid reserved keywords like user or table unless quoted.

Encoding matters for storing non-ASCII text. UTF8 is the most common and supports all languages. If you need a specific locale (sorting rules, case mapping), set LC_COLLATE and LC_CTYPE. Changing locale after creation is not possible without recreating the database, so choose carefully. To see available locales on your system, run locale -a in the shell.

The TEMPLATE option copies an existing database. template1 is the default; any objects you add to template1 appear in new databases. template0 is a pristine copy that allows different encoding/locale. Do not use template0 for routine creation unless you need custom settings.

Common Errors and Troubleshooting

• Permission denied: You lack CREATEDB privilege. Ask a superuser to grant it: ALTER USER your_user CREATEDB;.
• Database already exists: Use IF NOT EXISTS or drop the old one first (careful!).
• Encoding mismatch: If you specify an encoding not supported by the template, use TEMPLATE = template0.
• Invalid locale: The locale name must be available on the server OS. Check with locale -a.
• Connection refused: Server not running or wrong host/port. Check pg_isready and pg_hba.conf.
• Identifier too long: Database names are limited to 63 characters by default (can be increased at compile time).

If you get ERROR: source database "template1" is being accessed by other users, terminate other connections or use template0.

Creating a Database in Different Environments

psql interactive: Connect and run CREATE DATABASE directly.

Shell script: Use createdb with environment variables PGHOST, PGPORT, PGUSER to avoid hardcoding credentials.

Graphical tools (pgAdmin): Right-click on “Databases” → “Create” → “Database”, fill the form, and save.

Application code (Python/psycopg2): Use conn.autocommit = True before executing CREATE DATABASE, because it cannot run inside a transaction block.

Docker: Use the POSTGRES_DB environment variable in the official image to create a database on first start.

Frequently Asked Questions

How do I create a database if I’m not a superuser?

You need the CREATEDB privilege. A superuser can grant it with ALTER USER your_user CREATEDB;. After that, you can run CREATE DATABASE or createdb as that user.

What is the difference between CREATE DATABASE and createdb?

createdb is a command-line utility that connects to the server and issues the CREATE DATABASE SQL command. They are functionally identical; createdb is convenient for scripts and non-interactive use.

Can I create a database with a different owner?

Yes, use the OWNER option. The user you specify must exist. If you are not a superuser, you can only set the owner to yourself or a role you are a member of.

How do I create a database with a specific encoding like UTF8?

Use CREATE DATABASE mydb ENCODING 'UTF8' TEMPLATE template0;. The template0 is necessary if the default template has a different encoding. Verify with l mydb in psql.

What happens if I create a database with a name that already exists?

You get an error and the command fails. To avoid this in scripts, use CREATE DATABASE IF NOT EXISTS mydb; or createdb --if-not-exists mydb, which will silently skip if it exists.

Can I create a database inside a transaction?

No. PostgreSQL does not allow CREATE DATABASE inside a transaction block. In application code, ensure autocommit is enabled. In psql, it runs in its own transaction automatically.

Related

• Partition 1 Does Not Start on Physical Sector Boundary
• Galaxy S21 No Charger or Earphones? Box Contents
• Python Palindrome Test: Exact Strings or Normalize?