All posts
PostgreSQLNovember 13, 20267 min read

How to Create a Database in PostgreSQL (Beginner Guide)

Learn how to create a database in PostgreSQL with CREATE DATABASE, connect with psql, and add a safe user. Simple steps and examples for total beginners.

F
Fepiq Team
Fepiq

How do you create a database in PostgreSQL? Open psql and run CREATE DATABASE shop_db; That one line makes a new, empty database. In this guide you will learn what a database is, how to create one, how to connect to it, and how to create a safe user for it. No experience needed.

A database is a container that holds your tables. A table holds rows of data, like a spreadsheet. One PostgreSQL server can hold many databases, and each one is kept separate from the others. Usually you make one database for each app or project.

Before you start

You need PostgreSQL installed and a terminal where you can run psql, its command-line tool. If you have not done that yet, follow our install guide first.

Need PostgreSQL installed first? Start with our step-by-step install and psql guide.

Read the install guide

Now open psql. On most systems you can run the command below. It connects as the default admin user, called postgres.

bash
psql -U postgres

psql -U postgres starts psql and says you want to log in as the user named postgres. It may ask for the password you chose during install. When you see a prompt that ends in =#, you are in.

Step 1: See which databases already exist

PostgreSQL always comes with a few databases. Let us list them with a psql shortcut.

bash
\l

The backslash-l command (short for list) prints a table of every database on the server. You will see postgres, template0 and template1. Leave template0 and template1 alone. PostgreSQL uses them to make new databases.

Step 2: Create your first database

sql
CREATE DATABASE shop_db;

CREATE DATABASE is the SQL command. shop_db is the name you picked. The semicolon at the end tells PostgreSQL the command is finished. If it works, psql prints CREATE DATABASE. Run \l again and you will see shop_db in the list.

Naming rules for databases

  • Use lowercase letters, numbers and underscores, like blog_app.
  • Do not start the name with a number.
  • Avoid spaces. If you use capital letters, you must always wrap the name in double quotes, which gets annoying.
  • Pick a clear name. shop_db is better than test123.

Step 3: Connect to your new database

Creating a database does not move you into it. You are still connected to the old one. Use the connect command to switch.

bash
\c shop_db

\c is short for connect. psql replies that you are now connected to database shop_db, and the prompt changes to shop_db=#. Anything you create now goes inside this database.

Step 4: Create a table to check it works

sql
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO customers (name) VALUES ('Ada');

SELECT * FROM customers;

The first command makes a table called customers with two columns. id is a number that counts up by itself, and name is text that cannot be empty. The second command adds one row. The third command shows all rows. You should see Ada with id 1. Your database works.

Want to understand tables better? Learn how to create one properly.

Read: Create a table in PostgreSQL

Step 5: Create a user for your database

Using the postgres admin user for every app is risky. If your app has a bug or gets hacked, the attacker gets full power. A better habit is to make a separate user, which PostgreSQL calls a role, for each app.

sql
CREATE USER shop_user WITH PASSWORD 'change_me_please';

CREATE USER makes a new login role named shop_user. WITH PASSWORD sets its password, written inside single quotes. Use a long, strong password in real projects and never share it publicly.

sql
GRANT ALL PRIVILEGES ON DATABASE shop_db TO shop_user;

GRANT gives permissions. This line lets shop_user connect to and create things in shop_db. On PostgreSQL 15 and newer you may also need to run the next command while connected to shop_db, because the public schema is locked down by default.

sql
GRANT ALL ON SCHEMA public TO shop_user;

A schema is a folder inside a database that holds tables. This command lets shop_user create and use tables in the default one, called public.

Step 6: Log in as the new user

Quit psql with \q, then connect again using the new user and database.

bash
psql -U shop_user -d shop_db -h localhost

-U picks the user, -d picks the database, and -h localhost says the server is on your own computer. Enter the password when asked. If you see the shop_db=> prompt, it worked.

Create a database with a single terminal command

PostgreSQL also ships with a helper program, so you can skip psql for this step.

bash
createdb -U postgres shop_db

createdb does the same job as CREATE DATABASE. It is handy in scripts. Its partner dropdb deletes a database.

How to delete a database

sql
DROP DATABASE shop_db;

DROP DATABASE removes the database and everything inside it forever. There is no undo. You also cannot drop a database you are currently connected to, so connect to a different one first with \c postgres.

Quick reference

TaskCommand
List databases\l
Create a databaseCREATE DATABASE name;
Connect to a database\c name
Create a userCREATE USER name WITH PASSWORD 'pw';
Give accessGRANT ALL PRIVILEGES ON DATABASE name TO user;
Delete a databaseDROP DATABASE name;
Quit psql\q

Common mistakes

  • Forgetting the semicolon. psql then waits for more input. Type ; and press Enter.
  • Thinking CREATE DATABASE moves you into it. You must run \c yourself.
  • Using capital letters in names, then getting errors about missing databases.
  • Using the postgres admin user inside your app instead of a dedicated user.
  • Dropping a database without a backup.

What to learn next

Now that you have a database and a user, you can add tables, insert data and read it back. Practice by building a small database for something you know well, such as books, recipes or a to-do list.

Frequently asked questions

How do I create a database in PostgreSQL?+

Open psql and run CREATE DATABASE your_name; with the name you want. You can also run createdb your_name in a normal terminal. Then use \c your_name to connect to it.

How do I list all databases in PostgreSQL?+

Inside psql, type \l and press Enter. You can also run SELECT datname FROM pg_database; to get the same list using SQL.

What is the difference between a database and a table?+

A database is the big container for one project. A table lives inside a database and stores rows of data in columns, like one sheet in a spreadsheet file.

Why do I get 'permission denied to create database'?+

The user you logged in as is not allowed to create databases. Log in as the postgres admin user, or ask an admin to run ALTER USER your_user CREATEDB; for you.

Can I rename a PostgreSQL database?+

Yes. Connect to a different database first, then run ALTER DATABASE old_name RENAME TO new_name; Nobody else can be connected to the old database at that moment.

Building a real product on PostgreSQL? Fepiq designs and builds fast, reliable web apps, and we are happy to help.

Talk to Fepiq

Get new posts in your inbox

Occasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.

Keep reading

Related posts

All posts

Let's build something

Ready to ship your next product with Fepiq?

Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.