CREATE TABLE

Objectives

By the end of this lesson, you should be able to:

• Write a CREATE TABLE statement with columns
• Confirm a table was created successfully
• Explain what happens when a table already exists

💡 Why this matters: This is the statement that turns a table design into something real, an actual structure PostgreSQL will accept data into. Every table used for the rest of this course starts here.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

The CREATE TABLE Statement

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees names the table. Inside the parentheses, each line defines one column: a name, a data type (covered in full next lesson), and optionally one or more constraints. PRIMARY KEY and NOT NULL are both constraints, covered in their own lessons shortly, for now just know they’re there.

Running this creates a real, empty table, confirmed by checking what tables exist:

table_name
----------
employees

This is exactly what psql’s \dt command shows, or what appears in pgAdmin’s object browser after a refresh.

Trying to Create a Table That Already Exists

Running the exact same CREATE TABLE employees statement a second time fails:

CREATE TABLE employees (id SERIAL PRIMARY KEY);

ERROR: relation "employees" already exists

This is intentional, PostgreSQL won’t silently let you overwrite a table’s structure by accident. CREATE TABLE IF NOT EXISTS avoids this error, creating the table only if it doesn’t already exist:

CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY
);

This is especially useful in setup scripts that might run more than once.

Try It

1. Write a CREATE TABLE statement for a departments table with an auto-incrementing id and a required name.
2. Run it, then confirm the table exists.
3. Try creating the same departments table again without IF NOT EXISTS, confirm you get an error, then try it again with IF NOT EXISTS and confirm it doesn’t.

Recap

• CREATE TABLE table_name (column definitions...) builds a new, empty table.
• Creating a table that already exists raises an error, CREATE TABLE IF NOT EXISTS avoids that error safely.

Next lesson: PostgreSQL’s data types, the building blocks every column definition uses.