How to Create a Table in PostgreSQL (Beginner Guide)

If you searched for "how to create a table in PostgreSQL," here is the short answer: you connect to a database with the psql tool or a GUI like pgAdmin, then run a CREATE TABLE statement that lists your column names, their data types, and any rules (called constraints) those columns must follow. That's it. In this guide, we'll walk through every part of that sentence with real, copy-pasteable examples, so by the end you'll be able to build your own tables with confidence.

PostgreSQL (often shortened to "Postgres") is a free, open-source database. A database is just an organized place to store data. Inside a database, data lives in tables — think of a table like a spreadsheet, with rows (individual records) and columns (the fields each record has, like name or email).

What you need before you start

You need PostgreSQL installed on your computer, or access to a PostgreSQL database someone else set up (many hosting providers offer this). You also need a way to run SQL commands. The two most common tools are psql, a command-line program that comes with PostgreSQL, and pgAdmin, a visual tool with a point-and-click interface. This guide uses psql because it works the same way on every operating system, but every example also works if you paste it into pgAdmin's query editor.

Step 1: Connect to a database with psql

Open your terminal (Command Prompt, Terminal, or PowerShell) and run this command to connect to a database:

psql -U postgres -d mydatabase

Here's what each part means: psql is the program you're running. -U postgres tells it which username to log in as (postgres is the default admin user). -d mydatabase tells it which database to connect to. If that database doesn't exist yet, you can create one first by running CREATE DATABASE mydatabase; from inside psql while connected to the default postgres database.

Step 2: Understand PostgreSQL data types (in plain English)

Every column in a table needs a data type, which tells PostgreSQL what kind of value that column can hold. You don't need to memorize all of them — here are the ones a beginner uses most often.

A quick tip: use SERIAL for ID columns because PostgreSQL will automatically fill in 1, 2, 3, and so on for you. Use NUMERIC instead of a plain number type whenever you're storing money, since it avoids the rounding errors that come with decimal math.

Step 3: Write your first CREATE TABLE statement

Let's create a table called students. Run this in psql:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

Let's go through this line by line. CREATE TABLE students ( tells PostgreSQL to start defining a new table named students. id SERIAL PRIMARY KEY creates an id column that auto-increments and uniquely identifies each row — this is called the primary key. full_name VARCHAR(100) NOT NULL creates a text column limited to 100 characters, and NOT NULL means every row must have a value here. email VARCHAR(255) UNIQUE NOT NULL works the same way, but UNIQUE means no two rows can share the same email. enrolled_on DATE DEFAULT CURRENT_DATE creates a date column that automatically fills in today's date if you don't provide one. The closing parenthesis and semicolon tell PostgreSQL the statement is finished.

Step 4: Understand the constraints you just used

Constraints are rules that protect your data from mistakes. Here are the four you'll use most as a beginner:

• PRIMARY KEY — uniquely identifies each row; every table should have one
• NOT NULL — the column can never be left empty
• UNIQUE — no two rows can have the same value in that column
• DEFAULT — automatically fills in a value if you don't provide one

You can combine several constraints on one column, just like we did with email VARCHAR(255) UNIQUE NOT NULL above.

Step 5: Check that your table was created

In psql, you can list a table's structure with a backslash command:

\d students

This prints every column, its data type, and its constraints, so you can confirm everything matches what you typed. \d is a psql shortcut, not standard SQL, so it only works inside psql (in pgAdmin, you'd look at the table in the sidebar instead).

Step 6: Insert a row to test your table

A table is only useful once it has data. Add a row with INSERT INTO:

INSERT INTO students (full_name, email)
VALUES ('Amara Okafor', 'amara@example.com');

INSERT INTO students names the table, and (full_name, email) lists which columns you're providing values for. We skipped id and enrolled_on on purpose — id fills itself in automatically because it's SERIAL, and enrolled_on fills itself in because of its DEFAULT. VALUES ('Amara Okafor', 'amara@example.com') supplies the actual data, in the same order as the column list.

Now confirm the row was saved:

SELECT * FROM students;

SELECT * FROM students; means "show me every column, for every row, in the students table." You should see one row with id set to 1, the name and email you typed, and today's date filled in automatically.

Common mistakes beginners make

• Forgetting the semicolon (;) at the end of a statement — psql will just wait for more input until it sees one
• Using VARCHAR without a length when TEXT would be simpler and just as fast for unlimited text
• Skipping PRIMARY KEY, which makes it hard to update or delete a specific row later
• Mixing up single quotes ('text') for values and double quotes ("names") for identifiers — PostgreSQL treats them differently
• Trying to insert a value into a NOT NULL column without providing one, which causes an error on purpose to protect your data

What to do next

You now know how to connect to PostgreSQL, choose the right data type for a column, create a table with useful constraints, and insert and view data. From here, try adding a second table — for example, a courses table — and explore how to connect two tables together using a foreign key, which is simply a column in one table that points to the primary key in another.