Creating tables

Every table starts life with a CREATE TABLE statement. You name the table, then list its columns inside parentheses, giving each one a name and a data type. The server stores that definition and from then on enforces it on every row you insert. Creating tables is DDL — data definition language — and it is almost always written and run as plain SQL, whether you type it into psql or feed it to a migration tool.

We will build a products table for a small shop and grow it as the lesson goes on. Every output below is real, from PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop on a Mac, September 2026); each fence runs in an empty database, top to bottom.

The basic shape

The minimum is a table name and one or more columns. Each column is a name followed by a type, with columns separated by commas. In psql, \d then describes the result: columns, types, nullability, and defaults.

-- sql/loose-table.sql
CREATE TABLE products (
  id     integer,
  name   text,
  price  numeric(10, 2)
);
\d products

CREATE TABLE
                 Table "public.products"
 Column |     Type      | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
 id     | integer       |           |          |
 name   | text          |           |          |
 price  | numeric(10,2) |           |          |

That creates a table with three columns: an id whole number, a name of text, and a price stored as an exact decimal with up to ten digits and two after the point. It works, but it is too loose — every column is nullable and there is no default. Nothing stops two products from sharing an id, or a row from being entirely empty:

-- sql/loose-rows.sql
INSERT INTO products VALUES (1, 'Notebook', 4.50), (1, 'Pen', NULL), (NULL, NULL, NULL);
SELECT * FROM products;

INSERT 0 3
 id |   name   | price
----+----------+-------
  1 | Notebook |  4.50
  1 | Pen      |
    |          |
(3 rows)

Three rows went in, two with the same id, one with nothing at all. We fix that with a primary key and constraints next.

In pgAdmin: after running the statement in the Query Tool, refresh the Tables node in the browser tree and the new products table appears. Expanding it reveals a Columns node listing each column and its type.

Generating primary keys automatically

You rarely want to assign id numbers by hand. PostgreSQL can do it for you. The modern way is GENERATED ALWAYS AS IDENTITY, which makes the server supply the next value automatically. The older shortcut is SERIAL, which you will still see in plenty of existing code.

-- sql/identity-table.sql
DROP TABLE products;
CREATE TABLE products (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  price  numeric(10, 2) NOT NULL
);
\d products

DROP TABLE
CREATE TABLE
                           Table "public.products"
 Column |     Type      | Collation | Nullable |           Default
--------+---------------+-----------+----------+------------------------------
 id     | bigint        |           | not null | generated always as identity
 name   | text          |           | not null |
 price  | numeric(10,2) |           | not null |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)

Now id is filled in by the server, declared the primary key (not null is implied and an index products_pkey is created), and name and price can no longer be left empty. \d shows the generated always as identity default. The same table written with the older style would use id bigserial PRIMARY KEY. Both produce an auto-incrementing key; the IDENTITY form is the SQL-standard one and is preferred in modern PostgreSQL (docs).

-- sql/identity-insert.sql
INSERT INTO products (name, price) VALUES ('Notebook', 4.50), ('Pen', 1.20) RETURNING id, name;

 id |   name
----+----------
  1 | Notebook
  2 | Pen
(2 rows)

INSERT 0 2

ALWAYS, BY DEFAULT, and what serial does differently

GENERATED ALWAYS refuses a value you supply, with an error that names the fix (SQLSTATE 428C9):

-- @expect-error 428C9
INSERT INTO products (id, name, price) VALUES (100, 'Stapler', 8.00);

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

OVERRIDING SYSTEM VALUE is the deliberate escape hatch (bulk loads, restores). Note that it does not advance the sequence:

-- sql/overriding.sql
INSERT INTO products (id, name, price) OVERRIDING SYSTEM VALUE VALUES (100, 'Stapler', 8.00);
SELECT id, name FROM products ORDER BY id;

INSERT 0 1
 id  |   name
-----+----------
   1 | Notebook
   2 | Pen
 100 | Stapler
(3 rows)

GENERATED BY DEFAULT accepts explicit values, which is exactly how you get a collision later: the generator has no idea you inserted 1 by hand.

-- sql/by-default.sql
CREATE TABLE tags (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, label text NOT NULL);
INSERT INTO tags (id, label) VALUES (1, 'imported');

-- @expect-error 23505
INSERT INTO tags (label) VALUES ('first generated');

ERROR:  duplicate key value violates unique constraint "tags_pkey"
DETAIL:  Key (id)=(1) already exists.

SERIAL is shorthand for a separate sequence plus a nextval(...) default. Look at the Default column, and compare which sequence each kind of column owns:

-- sql/serial.sql
CREATE TABLE legacy (id bigserial PRIMARY KEY, name text NOT NULL);
\d legacy
SELECT pg_get_serial_sequence('legacy', 'id') AS serial_sequence,
       pg_get_serial_sequence('products', 'id') AS identity_sequence;

CREATE TABLE
                            Table "public.legacy"
 Column |  Type  | Collation | Nullable |              Default
--------+--------+-----------+----------+------------------------------------
 id     | bigint |           | not null | nextval('legacy_id_seq'::regclass)
 name   | text   |           | not null |
Indexes:
    "legacy_pkey" PRIMARY KEY, btree (id)

   serial_sequence    |   identity_sequence
----------------------+------------------------
 public.legacy_id_seq | public.products_id_seq
(1 row)

That separate sequence is the “ownership quirk” people mean: the table is one object, the sequence another, and privileges are checked on both. A role that may INSERT into the serial table but has no privilege on its sequence fails; the identity column, whose sequence is internal, does not:

-- sql/sequence-privileges.sql
CREATE ROLE writer LOGIN PASSWORD '<writer-password>';
GRANT INSERT ON legacy, products TO writer;
SET ROLE writer;
INSERT INTO products (name, price) VALUES ('Eraser', 0.80);
RESET ROLE;

CREATE ROLE
GRANT
SET
INSERT 0 1
RESET

-- @expect-error 42501
BEGIN;
SET ROLE writer;
INSERT INTO legacy (name) VALUES ('Eraser');

BEGIN
SET
ERROR:  permission denied for sequence legacy_id_seq

Sequences never roll back. A rolled-back insert still consumes a value, so identity columns have gaps by design (never use them as a gapless counter). Above, Eraser took id 3, the rolled-back Discarded burned 4, and Ruler got 5:

-- sql/identity-gaps.sql
BEGIN;
INSERT INTO products (name, price) VALUES ('Discarded', 1.00);
ROLLBACK;
INSERT INTO products (name, price) VALUES ('Ruler', 2.10) RETURNING id;

BEGIN
INSERT 0 1
ROLLBACK
 id
----
  5
(1 row)

INSERT 0 1

Creating a table from a driver

Most applications run their CREATE TABLE statements once, at setup or through a migration step, rather than on every request. Here is the same migration sent from each driver — useful when your application owns its schema.

psql

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10, 2) NOT NULL
);

Node.js

await pool.query(`
  CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10, 2) NOT NULL
  )
`);

Python

cur.execute("""
    CREATE TABLE products (
      id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      name  text NOT NULL,
      price numeric(10, 2) NOT NULL
    )
""")

Go

_, err := conn.Exec(ctx, `
  CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10, 2) NOT NULL
  )`)

Rust

sqlx::query(
    "CREATE TABLE products (
       id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       name  text NOT NULL,
       price numeric(10, 2) NOT NULL
     )",
)
.execute(&pool)
.await?;

The SQL is identical everywhere; only the way you hand it to the server differs. Because DDL does not take user input, there are no parameters to bind here. Each snippet was run once against a scratch database and then asked how many columns products has:

$ node app.mjs        # pg 8.23.0 (count(*) is a bigint, which node-postgres returns as a string)
{ columns: '3' }

$ python app.py        # psycopg 3.3.6
(3,)

$ go run .        # pgx v5.11.0
columns: 3

$ cargo run        # sqlx 0.9.0
columns: 3

IF NOT EXISTS and DROP TABLE

Running CREATE TABLE products a second time is an error, because the table already exists (SQLSTATE 42P07):

-- @expect-error 42P07
CREATE TABLE products (id integer);

ERROR:  relation "products" already exists

Adding IF NOT EXISTS makes the statement a no-op when the table is already there, and tells you so with a NOTICE, which is handy in setup scripts that may run more than once. Note that it only checks the name: it does not check that the existing table has the columns you asked for.

-- sql/if-not-exists.sql
CREATE TABLE IF NOT EXISTS products (id integer);

NOTICE:  relation "products" already exists, skipping
CREATE TABLE

To remove a table and all of its rows, use DROP TABLE. It is permanent and immediate, so treat it with the same care as DELETE. DROP TABLE IF EXISTS avoids an error when the table is already gone. PostgreSQL has transactional DDL, so a drop can be rolled back — the safest way to check what a DROP would do:

-- sql/drop-in-transaction.sql
BEGIN;
DROP TABLE legacy;
SELECT to_regclass('legacy') AS inside_tx;
ROLLBACK;
SELECT to_regclass('legacy') AS after_rollback;

BEGIN
DROP TABLE
 inside_tx
-----------

(1 row)

ROLLBACK
 after_rollback
----------------
 legacy
(1 row)

In pgAdmin: you can also drop a table by right-clicking it in the browser tree and choosing Delete/Drop, but running DROP TABLE in the Query Tool keeps the action in your script history.

Names are folded to lowercase unless double-quoted, so Widgets and "Widgets" are two different tables:

-- sql/case-folding.sql
CREATE TABLE Widgets (Id int);
CREATE TABLE "Widgets" ("Id" int);
\dt *idgets

CREATE TABLE
CREATE TABLE
           List of tables
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | Widgets | table | postgres
 public | widgets | table | postgres
(2 rows)

Reading AI-generated code

An assistant asked for “a seed script with fixed ids so tests are stable” writes this. It reads fine:

🐛 Spot the bug

-- seed.sql (AI comment: "seed a few products with fixed ids so tests are stable")
CREATE TABLE products (
id    serial PRIMARY KEY,
name  text NOT NULL,
price numeric(10, 2) NOT NULL
);

INSERT INTO products (id, name, price) VALUES (1, 'Notebook', 4.50), (2, 'Pen', 1.20);

-- later, from the application:
INSERT INTO products (name, price) VALUES ('Stapler', 8.00);

Show the answer

The bug appears only when the application inserts its first row. serial gives the column a sequence, but explicit ids in the seed never touch it, so the sequence still starts at 1 and the first generated id collides with the seeded one:

-- sql/seed-buggy.sql
CREATE TABLE seeded (id serial PRIMARY KEY, name text NOT NULL);
INSERT INTO seeded (id, name) VALUES (1, 'Notebook'), (2, 'Pen');

-- @expect-error 23505
INSERT INTO seeded (name) VALUES ('Stapler');

ERROR:  duplicate key value violates unique constraint "seeded_pkey"
DETAIL:  Key (id)=(1) already exists.

Two fixes work. Best is not to hand-assign ids at all (identity column, ids come out 3 after two seeded rows are inserted by the generator):

-- sql/seed-fixed.sql
CREATE TABLE seeded_fixed (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);
INSERT INTO seeded_fixed (name) VALUES ('Notebook'), ('Pen');
INSERT INTO seeded_fixed (name) VALUES ('Stapler') RETURNING id;

CREATE TABLE
INSERT 0 2
 id
----
  3
(1 row)

INSERT 0 1

If you must seed explicit ids, move the sequence past them afterwards with setval:

-- sql/seed-resync.sql
CREATE TABLE seeded_serial (id serial PRIMARY KEY, name text NOT NULL);
INSERT INTO seeded_serial (id, name) VALUES (1, 'Notebook'), (2, 'Pen');
SELECT setval(pg_get_serial_sequence('seeded_serial', 'id'), (SELECT max(id) FROM seeded_serial));
INSERT INTO seeded_serial (name) VALUES ('Stapler') RETURNING id;

CREATE TABLE
INSERT 0 2
 setval
--------
      2
(1 row)

 id
----
  3
(1 row)

INSERT 0 1

Finally, remove the practice role from the permissions demo:

-- sql/cleanup.sql
REVOKE ALL ON legacy, products FROM writer;
DROP ROLE writer;

REVOKE
DROP ROLE

Mental Model

A table definition is a contract the server enforces on every write. The primary key is the row’s identity, and an identity column is a sequence owned by the column, taken out of the transaction system on purpose: it hands out unique numbers, not consecutive ones, and never takes them back. Everything about auto-generated keys follows from that: gaps after rollbacks, collisions after manual inserts, and a sequence you must keep in sync when you bypass it.

Tips / gotchas

• Prefer GENERATED ALWAYS AS IDENTITY over SERIAL for new tables. It is the SQL-standard syntax, avoids the separate-sequence privilege quirk proved above, and reads more clearly.
• Use bigint rather than integer for identity keys on tables you expect to grow. A four-byte integer runs out at 2,147,483,647 rows (or sooner, since sequences leave gaps), and switching the type later is disruptive.
• Add IF NOT EXISTS to creation scripts that may run repeatedly, but leave it off one-time migrations where a duplicate table really should be an error you notice.
• DROP TABLE removes both the structure and every row with no confirmation. Double-check the table name, and prefer dropping inside a transaction you can roll back if you are unsure.
• Column and table names are folded to lowercase unless double-quoted, so pick lowercase, snake_case names to avoid surprises.

🐘 IDENTITY is the modern default

When you reach for an auto-incrementing key, write bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY. It expresses intent clearly, follows the SQL standard, and sidesteps the legacy behavior of SERIAL. Reserve SERIAL for reading older code, not writing new tables.

Trade-offs

Option	Benefit	Cost
Design the full schema up front	Clear structure, migration discipline from day one	Takes longer to gather requirements before writing any code
Add columns later as real requirements appear	Flexible, adapts quickly to the business	Schema drift — you must retrofit constraints and migrations later
bigint GENERATED ALWAYS AS IDENTITY primary key	Sortable by insert time, small, fast to index	Next id is guessable, and it reveals the row count
uuid primary key	Globally unique, can be generated on the client with no round trip to the server	Larger (16 bytes vs 8); random v4 values scatter the index (measured in the next lesson)

Common Misconceptions

• “Identity/serial ids are consecutive.” They are unique, not gapless: a rolled-back insert burns a value (proved above: 3, 5).
• “IF NOT EXISTS makes the table right.” It only skips creation when the name exists, whatever its columns are.
• “A table without a primary key is fine.” It is legal (the first table above accepted duplicates and an all-NULL row), but updating or deleting one specific row is then hard, and logical replication needs a replica identity.
• “varchar(255) is the safe default.” In PostgreSQL text and varchar(n) are stored the same way; a copy-pasted 255 protects nothing. The next lesson covers this.
• “DROP TABLE cannot be undone.” Outside a transaction it cannot; inside one it can be rolled back (proved above).

Which clause makes PostgreSQL supply primary-key values automatically using the SQL-standard syntax?

AUTO_INCREMENT GENERATED ALWAYS AS IDENTITY PRIMARY AUTO DEFAULT NEXTVAL

What does adding IF NOT EXISTS to a CREATE TABLE statement do?

Drops the old table first Skips creation without error when the table already exists Creates a temporary table Renames the existing table

What does DROP TABLE products; do?

Empties the rows but keeps the structure Removes the table structure and all of its rows permanently Hides the table from pgAdmin only Creates a backup before deleting

A table has id serial PRIMARY KEY. A seed script inserts rows with explicit ids 1 and 2. What happens on the next INSERT without an id?

It gets id 3 It fails with a duplicate key error on id 1 PostgreSQL detects the maximum and continues from 3 The seeded rows are renumbered

You run BEGIN; INSERT ... (identity column); ROLLBACK; and then insert again. What about the identity value?

It is reused, so there is no gap It is consumed by the rolled-back insert, so the ids have a gap The sequence is reset to 1 The second insert fails

Submit

Mark as complete