Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 90 ยท Schema Design and Constraints

Designing Tables the PostgreSQL Way

Design PostgreSQL tables with confidence: naming rules, primary keys, normalization checks, data type choices, column order and padding, and timestamptz.

A table is a promise about your data. Every column type, key and rule you choose now decides what bad data can get in, how fast queries run and how painful future changes will be. PostgreSQL gives you a rich set of types and constraints, so it rewards a bit of planning. This lesson collects the habits that experienced PostgreSQL developers apply to nearly every table they create.

You can find general theory in Normalization, Keys and Data types. Here we focus on choices that are specific to PostgreSQL and on a checklist you can use for every new table.

What runs live here. The editors on this page use SQLite (sql.js), so the design exercises use portable SQL: keys, foreign keys, CHECK constraints and normalization. PostgreSQL-only parts, such as GENERATED ... AS IDENTITY, timestamptz, text and column padding, are read-only code blocks. The comments say what changes between the two.

A table design checklist

Name Lowercase snake_case, no quotes needed.
Key Every table gets a primary key.
Split One fact in one place.
Constrain NOT NULL, CHECK and foreign keys.
1 / 2

Naming

PostgreSQL folds unquoted names to lowercase. CREATE TABLE Orders creates orders, and SELECT * FROM Orders still works. But CREATE TABLE "Orders" creates a table whose name has a capital letter, and from then on you must quote it everywhere. Avoid that trap with a few rules:

  • Use lowercase snake_case for tables, columns, indexes and constraints: order_items, created_at.
  • Do not use reserved words (user, order, group, table) as table names. users and orders (plural) or account and purchase (singular) both work. Pick one style and keep it.
  • Name foreign key columns after the table and column they point to: customer_id references customers(id).
  • Do not use spaces or accented characters in names, and do not prefix every column with the table name.
  • Use short, predictable names for booleans and timestamps: is_active, created_at, deleted_at.

PostgreSQL also names constraints and indexes for you (orders_pkey, orders_customer_id_fkey), and the defaults are fine. Naming them yourself helps when you read an error message or write a migration.

Keys

Every table should have a primary key. It gives each row an identity, is the target of foreign keys, and is what tools and replication use to find a row.

For most tables, the modern choice is an identity column:

SQL
CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

GENERATED ALWAYS AS IDENTITY is the standard replacement for serial. It creates and owns a sequence, and with ALWAYS the database refuses manual values unless you say OVERRIDING SYSTEM VALUE. Use bigint unless the table is tiny for certain, because changing integer to bigint later rewrites the table and every table that points to it. More options, such as UUIDs, are in UUIDs, Serials and Identity Columns.

Two more key rules:

  • If rows also have a natural key that must be unique, such as an email or ISBN, keep the surrogate id as the primary key and add a UNIQUE constraint on the natural one.
  • A link table for a many-to-many relationship usually needs no id. Use a composite primary key of the two foreign keys.

Normalization checkpoints

Normalization means each fact lives in exactly one place. You do not need to recite normal forms; ask these questions about every table:

  1. Does each column hold one value, not a list of values or a repeating group such as phone1, phone2, phone3?
  2. Does every non-key column depend on the whole key? In a link table, a column about only one side belongs in the table for that side.
  3. Does any column depend on another non-key column? If city determines postal_code, that fact might belong in its own table.

When a table fails these checks you get anomalies. Update a customer's address in one order row and forget the others, and now two rows disagree. This live example starts with a flat table and shows the problem and the fix:

SQL From a flat table to two tables

Press Run (or Ctrl + Enter) to see the result here.

Ada ends up with 2 emails and Alan with 1. The fix is to store each customer once and point to them:

SQL Normalized: customers and orders

Press Run (or Ctrl + Enter) to see the result here.

Now the email exists once, and all of Ada's orders show [email protected]. The foreign key stops you from inserting an order for a customer that does not exist, and CHECK (price >= 0) rejects negative prices.

Normalization is a starting point, not a religion. Reporting tables or caches may repeat data on purpose, as long as you know why and know how it stays in sync.

Choosing data types

PostgreSQL has far more types than most databases. A short list covers most columns:

Need Use Avoid
Whole numbers integer, bigint smallint unless storage really matters
Exact decimals (prices, rates) numeric(12,2) real, double precision, money
Free text text char(n), varchar(n) just to "limit"
Yes or no boolean integer flags, 'Y'/'N' strings
A moment in time timestamptz timestamp without a zone, text dates
A calendar day date text such as '2025-03-01'
Unique random ids uuid text holding UUIDs
Flexible documents jsonb json

Some of these deserve explanation:

  • text is not slower than varchar(n). In PostgreSQL they are stored identically, and the length check is just a constraint. If you need a limit, add CHECK (length(code) <= 10) on that column, which is easy to change later, instead of a type change.
  • numeric is exact, floats are not. Money stored as double precision accumulates rounding errors. See Numeric Types.
  • The money type depends on a locale setting and is awkward for currencies. Use numeric and a separate currency code.

Timestamps

Use timestamptz for the time something happened. Despite the name, it does not store a time zone. It stores a point in time (internally in UTC) and converts to your session's time zone for display. The plain timestamp type stores a wall-clock reading with no zone, so the same value means different instants to different readers. Details in Dates, Times and Time Zones.

SQL
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  total       numeric(12,2) NOT NULL CHECK (total >= 0),
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

created_at with DEFAULT now() is worth adding to nearly every table. You will want it the first time someone asks "when did this appear?". SQLite has no timestamptz; it stores dates as text, which is why the live editors use text dates.

Column order and padding

PostgreSQL stores each row with its fields lined up on alignment boundaries: an integer starts at a multiple of 4 bytes, a bigint or timestamptz at a multiple of 8. If a small column sits before a large one, the gap is filled with padding bytes.

SQL
-- Wasteful: 1 + 7 padding + 8 + 1 + 7 padding
CREATE TABLE a (flag boolean, big bigint, flag2 boolean, big2 bigint);

-- Tighter: 8 + 8 + 1 + 1
CREATE TABLE b (big bigint, big2 bigint, flag boolean, flag2 boolean);

Putting wide fixed-size columns first, then narrow ones, and variable-length columns such as text last can save a few bytes per row. On a table with a few hundred million rows that is gigabytes. For small tables it does not matter, so do not make a table hard to read for it. Set the order when you create the table, because changing it later means rewriting the table.

ALTER TABLE essentials

Real tables change after they are created. These everyday forms were run on PostgreSQL 18 against a table village (id, name) that already held two rows. The risky parts of changing live tables (locks, rewrites) are covered in Schema Migrations.

SQL
ALTER TABLE village ADD COLUMN population integer;                   -- existing rows get NULL
ALTER TABLE village ADD COLUMN IF NOT EXISTS population integer;     -- a notice, no error, when it exists
ALTER TABLE village
  ADD COLUMN active boolean NOT NULL DEFAULT true,                   -- existing rows get true
  ADD COLUMN founded date;                                           -- several changes in one statement
ALTER TABLE village ADD COLUMN region text NOT NULL;
-- ERROR:  column "region" of relation "village" contains null values

A new NOT NULL column needs a DEFAULT when the table already has rows, because the old rows must receive some value. Since PostgreSQL 11, adding a column with a constant default does not rewrite the table. Continue with the other forms:

SQL
ALTER TABLE village ALTER COLUMN population SET DEFAULT 0;           -- applies to future inserts only
ALTER TABLE village ALTER COLUMN population TYPE bigint;             -- rewrites the table for most type changes
ALTER TABLE village ALTER COLUMN founded TYPE text USING to_char(founded, 'YYYY');  -- USING says how to convert
ALTER TABLE village RENAME COLUMN name TO village_name;
ALTER TABLE village RENAME TO hamlet;                                -- indexes and constraints follow the table

ALTER TABLE hamlet DROP COLUMN notes;
ALTER TABLE hamlet DROP COLUMN IF EXISTS notes;                      -- a notice instead of an error
ALTER TABLE hamlet DROP COLUMN missing;
-- ERROR:  column "missing" of relation "hamlet" does not exist

When something depends on a column, such as a view, DROP COLUMN stops and names the dependent object (view v depends on column region of table hamlet). Add CASCADE to drop the view as well (NOTICE: drop cascades to view v), but read the notice: CASCADE removes things you may not have meant to lose. Renaming a table does not rename its indexes: the primary-key index of hamlet is still called village_pkey until you rename it with ALTER INDEX.

A design routine

Before you create a table, write it out in this order:

  1. Name the table and its columns in lowercase snake_case.
  2. Choose the primary key (bigint identity by default).
  3. For every column, pick the plain type first and mark it NOT NULL unless "unknown" is a real state.
  4. Add CHECK constraints for rules the type cannot express, and foreign keys for every reference.
  5. Add created_at, and updated_at if rows change.
  6. Reread it and ask what would happen if two rows disagreed about the same fact.

Constraints are covered fully in Constraints in Depth.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You run CREATE TABLE Orders (id bigint). What name does PostgreSQL give the table?

    Unquoted identifiers are folded to lowercase. Only a quoted name like "Orders" keeps its capital, and then you must quote it in every query.
  2. 2. Which type is the best default for the time an event happened?

    timestamptz stores an exact instant in time and converts to the session time zone for display. Plain timestamp has no zone, so the same value can mean different moments.
  3. 3. Is varchar(50) faster or smaller than text in PostgreSQL?

    text and varchar(n) use the same storage. A limit is just a constraint, so a CHECK on length is often a more flexible way to enforce one.
  4. 4. Why put wide fixed-size columns such as bigint before boolean columns?

    Columns are aligned to 4 or 8 byte boundaries. Small columns before big ones leave padding gaps, which wastes space across millions of rows.
  5. 5. A flat orders table repeats the customer's email on every row. What is the main risk?

    When one fact is stored many times, changing one copy and missing another leaves the data inconsistent. Moving the fact to its own table fixes it.

Summary

  • Use lowercase snake_case, avoid reserved words and avoid quoted mixed-case names.
  • Give every table a primary key, usually bigint GENERATED ALWAYS AS IDENTITY, plus UNIQUE on natural keys.
  • Store each fact once: one value per column, whole-key dependence, no column depending on a non-key column.
  • Pick plain types: text, numeric, boolean, timestamptz, date, uuid, jsonb. Avoid money and zone-less timestamps.
  • Order wide columns first to save padding, and add created_at by default.
  • The live editors used portable SQL on SQLite; identity columns, timestamptz and padding are PostgreSQL features shown read-only.

Next: Constraints in Depth.