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.
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.
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.
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 guideNow open psql. On most systems you can run the command below. It connects as the default admin user, called postgres.
psql -U postgrespsql -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.
PostgreSQL always comes with a few databases. Let us list them with a psql shortcut.
\lThe 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.
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.
Creating a database does not move you into it. You are still connected to the old one. Use the connect command to switch.
\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.
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 PostgreSQLUsing 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.
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.
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.
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.
Quit psql with \q, then connect again using the new user and database.
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.
PostgreSQL also ships with a helper program, so you can skip psql for this step.
createdb -U postgres shop_dbcreatedb does the same job as CREATE DATABASE. It is handy in scripts. Its partner dropdb deletes a database.
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.
| Task | Command |
|---|---|
| List databases | \l |
| Create a database | CREATE DATABASE name; |
| Connect to a database | \c name |
| Create a user | CREATE USER name WITH PASSWORD 'pw'; |
| Give access | GRANT ALL PRIVILEGES ON DATABASE name TO user; |
| Delete a database | DROP DATABASE name; |
| Quit psql | \q |
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.
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.
Inside psql, type \l and press Enter. You can also run SELECT datname FROM pg_database; to get the same list using SQL.
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.
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.
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 FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.