Tables, Data Types and Constraints

Table of Contents

1. Your First CREATE TABLE
2. Choosing Data Types
3. Why Money Is Never a Float
4. Identity Columns and Primary Keys
5. NULL: The Value That Means "Unknown"
6. Constraints: Rules the Database Enforces
7. The shopdb Tables: customers and products
8. Seeing the Rules Work: Real Error Messages
9. Changing and Dropping Tables
10. Recipe: Save schema.sql
11. Try It Yourself

1. Your First CREATE TABLE

In the previous chapter we created an empty database. Now we give it structure. The statement that creates a table is CREATE TABLE, and it is DDL: it defines the structure, not the data.

Connect to shopdb:

docker exec -it shopdb-pg psql -U postgres -d shopdb

Here is a tiny table to learn the shape. We will throw it away soon:

CREATE TABLE pets (
    name text,
    age  integer
);

Postgres answers CREATE TABLE. The pattern is:

CREATE TABLE table_name (
    column_name  data_type  [rules...],
    column_name  data_type  [rules...]
);

Each column has a name, a data type, and optionally some constraints (rules). Columns are separated by commas, and there is no comma after the last one. Look at it with \d:

shopdb=# \d pets
                Table "public.pets"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 name   | text    |           |          |
 age    | integer |           |          |

Note public.pets: public is the default schema, a namespace inside a database. Here "schema" means a folder of tables, a different meaning from "the design of the database" that we used before. You can ignore it for now. Remove the practice table:

DROP TABLE pets;

Use names that are lowercase, with words joined by underscores: created_at, order_items. This style, called snake_case, saves you from quoting names later. Avoid spaces and capital letters in names.

2. Choosing Data Types

Every column has one type, and the type decides what values are allowed and how they behave. These are the types you will use most often in PostgreSQL:

Some advice about the choices:

Text. In Postgres, text and varchar perform the same. Use text unless you have a real reason to cap the length, and enforce limits with a CHECK rule (section 6).

Whole numbers. integer is the normal choice. Use bigint when you expect more than 2 billion rows or values, for example the ID of a table that records every click on a big website.

Dates and times. Use date when you only need the day (a birthday). Use timestamptz ("timestamp with time zone") for moments in time, such as when an order was made. Despite its name, it does not store the zone text. It stores the exact moment in UTC and converts to your session's time zone when you display it. That makes it safe when your users are in different countries. Prefer it to the plain timestamp type, which silently drops zone information and causes confusing bugs.

uuid. A uuid is a long random-looking identifier such as 3f2a9c1d-7b44-4e0c-9a21-5d6b8e0f1a77. It is useful when records are created in many places (several servers or devices) and you cannot use a shared counter. We use plain integer IDs in shopdb, because they are simpler and smaller to read. UUIDs are a design choice you can revisit later.

Booleans. A boolean column holds true, false, or NULL (unknown). Name them like questions: is_active, has_paid.

Postgres checks types when you insert. Putting text into a number column fails right away, as you will see in section 8.

3. Why Money Is Never a Float

This is the most important type decision in this chapter. Run these two queries (the :: converts a value to a type):

SELECT 0.1::double precision + 0.2::double precision AS float_sum;
SELECT 0.1::numeric + 0.2::numeric AS numeric_sum;

      float_sum
----------------------
 0.30000000000000004
(1 row)

 numeric_sum
-------------
         0.3
(1 row)

Floating-point types (real, double precision) store numbers in binary and cannot represent values like 0.1 exactly. The tiny error is harmless for a measurement such as a temperature, but when you add millions of prices, the errors pile up and you lose or invent cents. If you read Math for Programmers, this is the same effect you saw in JavaScript.

numeric stores decimal digits exactly. You say how many digits you want: numeric(10, 2) means up to 10 digits in total with 2 after the decimal point. So the largest value is 99999999.99. It is the right type for prices, balances, and any value that must add up exactly.

SELECT 15.00::numeric(10,2) AS price,
       (15.00 * 3)::numeric(10,2) AS total;

 price | total
-------+-------
 15.00 |  45.00
(1 row)

A second common approach is to store money as an integer number of the smallest unit (cents, or riel) and convert for display. It is also exact. We use numeric(10, 2) in shopdb because it reads naturally.

4. Identity Columns and Primary Keys

Every table needs a column that identifies each row. For customers, the name is not good enough because two people can share it. The email can change. So we add a number that exists only to identify the row, called a surrogate key, and let the database generate it:

id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Three pieces:

• integer: a whole number.
• GENERATED ALWAYS AS IDENTITY: Postgres fills the column automatically with 1, 2, 3, and so on. You never choose the value. This is the modern, standard way. You may see older tutorials use serial, which does a similar job.
• PRIMARY KEY: a rule that the value must be unique and never NULL, and the column is the official identifier of the row.

The primary key gives you a reliable handle: "customer 3" means exactly one person. Other tables will use it to point at customers (chapter 6).

Because the column is GENERATED ALWAYS, Postgres refuses to let you supply your own value by accident:

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.

One more thing worth knowing: the counter never goes backwards. If an insert fails, or if you delete row 3, the next row still gets the next number, not the freed one. So IDs can have gaps (1, 2, 4, 5). This is normal. Do not use an ID as a "row count" and do not try to renumber it.

5. NULL: The Value That Means "Unknown"

NULL is a special marker meaning "no value here". Maybe the value is unknown, maybe it does not apply. For example, a customer may not have told us their city. Their city is NULL.

Three things to learn early:

1. NULL is not zero and not an empty string. A product with stock = 0 is out of stock. A product with stock = NULL means we do not know.
2. NULL cannot be compared with =. The test city = NULL is never true, not even when city is NULL. You must write city IS NULL. Chapter 5 digs into this.
3. Any arithmetic with NULL gives NULL: 5 + NULL is NULL.

SELECT 5 + NULL AS result, NULL = NULL AS same, NULL IS NULL AS is_null;

 result | same | is_null
--------+------+---------
        |      | t
(1 row)

An empty cell in psql is NULL. By default, columns accept NULL. For anything required, we add NOT NULL, the first constraint we meet next.

6. Constraints: Rules the Database Enforces

A constraint is a rule attached to a column or table. The database checks it on every insert and update and rejects any row that breaks it. Think of constraints as a bouncer at the door: bad data never gets in, no matter which program or person tries to add it.

Here they are in a scratch table so you can see each kind:

CREATE TABLE demo (
    id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    email      text NOT NULL UNIQUE,
    age        integer CHECK (age >= 0),
    is_active  boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now()
);

Reading it line by line:

• name text NOT NULL: a name is required.
• email text NOT NULL UNIQUE: required, and no duplicates.
• age integer CHECK (age >= 0): if given, it must not be negative. Note a CHECK passes when the value is NULL, since "unknown" is not a violation. Combine CHECK with NOT NULL when a value is required.
• is_active ... DEFAULT true: if you do not give a value, it is true.
• created_at ... DEFAULT now(): filled in with the current time. This is how you get automatic timestamps.

Let's insert a row giving only the required columns:

INSERT INTO demo (name, email) VALUES ('Test User', 'test@example.com');
SELECT * FROM demo;

 id |   name    |      email       | age | is_active |          created_at
----+-----------+------------------+-----+-----------+-------------------------------
  1 | Test User | test@example.com |     | t         | 2026-10-03 10:12:09.511203+00
(1 row)

We gave two values. Postgres supplied id, is_active, and created_at from the identity and the defaults, and left age as NULL. We cover INSERT fully in the next chapter. Drop the scratch table when you are done:

DROP TABLE demo;

7. The shopdb Tables: customers and products

Now the real thing. We start with the two tables that stand alone, which do not point at any other table: customers and products. Open shopdb/schema.sql and write:

-- shopdb/schema.sql
-- Structure of the shopdb database.

CREATE TABLE customers (
    id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    email      text NOT NULL UNIQUE,
    city       text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL,
    price    numeric(10, 2) NOT NULL CHECK (price >= 0),
    stock    integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category text
);

Let's justify the design choices:

There is one thing in products that you should look at carefully: the category column is plain text. It will hold values like 'Electronics'. This is a temporary design. Repeating category names as text invites typos (Electronic, electronics) and makes renaming a category painful. In chapter 6 we will fix that with a categories table and a foreign key. Starting with the simple version lets you feel the problem before the fix.

Run the file against the database from your normal terminal:

docker exec -i shopdb-pg psql -U postgres -d shopdb < schema.sql

CREATE TABLE
CREATE TABLE

Now inspect what you built:

shopdb=# \dt
          List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres
 public | products  | table | postgres
(2 rows)

And the details of one table:

shopdb=# \d customers
                                  Table "public.customers"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | integer                  |           | not null | generated always as identity
 name       | text                     |           | not null |
 email      | text                     |           | not null |
 city       | text                     |           |          |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

Postgres created two indexes for us to enforce the primary key and the unique rule. Their names, customers_pkey and customers_email_key, will appear in error messages. We study indexes in chapter 11.

8. Seeing the Rules Work: Real Error Messages

A rule you have never seen fire is a rule you do not trust. Let's break each one on purpose. These statements all fail, so they change nothing. (The error text can vary slightly between versions, but these are the typical messages.)

Missing required value (NOT NULL):

INSERT INTO customers (email) VALUES ('nobody@example.com');

ERROR:  null value in column "name" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (1, null, nobody@example.com, null, 2026-10-03 10:20:41.002931+00).

Wrong type:

INSERT INTO products (name, price, stock)
VALUES ('Mystery Box', 9.99, 'lots');

ERROR:  invalid input syntax for type integer: "lots"
LINE 2: VALUES ('Mystery Box', 9.99, 'lots');
                                     ^

Rule broken (CHECK):

INSERT INTO products (name, price) VALUES ('Free Money', -5);

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, Free Money, -5.00, 0, null).

Duplicate (UNIQUE). First add one real customer, then try to add the same email again:

INSERT INTO customers (name, email)
VALUES ('Sokha Chan', 'sokha@example.com');

INSERT INTO customers (name, email)
VALUES ('Another Sokha', 'sokha@example.com');

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(sokha@example.com) already exists.

The first insert succeeds (INSERT 0 1 means one row inserted) and the second fails.

Choosing the generated ID yourself:

INSERT INTO customers (id, name, email)
VALUES (99, 'Manual Id', 'manual@example.com');

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.

Look at how helpful the messages are. They name the column, the table, and the constraint, and the DETAIL shows the offending row. When you see an error in your own work, read all three lines before searching anything.

Pay attention to what these rules buy you. Your application code can have a bug, or a colleague can run a careless script, and still the data stays valid, because the database refuses. Rules in the database are the last line of defense, and unlike checks in application code, they cannot be bypassed.

Now clean up the one row we added, so chapter 4 starts clean. We will learn DELETE properly next chapter, but here is the line:

DELETE FROM customers WHERE email = 'sokha@example.com';

DELETE 1

Remember the "gaps" note: that row used up ID 1 and the failed inserts used up a few more. The identity counter does not reset. When you run seed.sql in chapter 4 the IDs would start higher than 1. To get exactly the IDs shown in this series, rebuild with the recipe from chapter 2 (drop database, create database, run schema.sql) before loading the seed data. We will remind you.

9. Changing and Dropping Tables

Designs change. ALTER TABLE modifies a table that already exists. Here are the common forms, using a throwaway table:

CREATE TABLE notes (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- add a column
ALTER TABLE notes ADD COLUMN body text;

-- add a rule to an existing column
ALTER TABLE notes ALTER COLUMN body SET NOT NULL;

-- remove that rule
ALTER TABLE notes ALTER COLUMN body DROP NOT NULL;

-- give a default
ALTER TABLE notes ALTER COLUMN body SET DEFAULT '';

-- rename a column
ALTER TABLE notes RENAME COLUMN body TO content;

-- change a column's type
ALTER TABLE notes ALTER COLUMN content TYPE varchar(500);

-- remove a column
ALTER TABLE notes DROP COLUMN content;

Each one answers ALTER TABLE. Finally remove the table:

DROP TABLE notes;

DROP TABLE deletes the table and all its data immediately. There is no undo and no confirmation. Two safety habits:

• DROP TABLE IF EXISTS notes; does nothing instead of failing when the table is missing, which is handy in scripts.
• Check the table name twice. In chapter 12 you will see how transactions let you try a risky change and roll it back.

Two cautions about ALTER TABLE on real systems:

1. Changing a column's type, or adding a rule such as NOT NULL, makes Postgres check every existing row. On a huge table that can be slow and can lock the table. On a table with bad data it fails.
2. Because of that, it is much better to get the design reasonably right early. Chapter 10 returns to this. We will also see in chapter 10 how teams store changes as numbered files, called migrations.

10. Recipe: Save schema.sql

This recipe makes sure your project matches the next chapter.

1. Open shopdb/schema.sql and make sure it contains exactly the two CREATE TABLE statements from section 7. Remove any scratch tables (pets, demo, notes) from the file. They were for practice.

2. Rebuild the database from scratch so there are no leftovers, no leftover rows, and the ID counters are fresh:

   docker exec -i shopdb-pg psql -U postgres -c "DROP DATABASE IF EXISTS shopdb;"
   docker exec -i shopdb-pg psql -U postgres -c "CREATE DATABASE shopdb;"
   docker exec -i shopdb-pg psql -U postgres -d shopdb < schema.sql
   

   Copy

3. Check the result with \dt and \d products:

   shopdb=# \d products
                                      Table "public.products"
     Column  |     Type      | Collation | Nullable |           Default
   ----------+---------------+-----------+----------+------------------------------
    id       | integer       |           | not null | generated always as identity
    name     | text          |           | not null |
    price    | numeric(10,2) |           | not null |
    stock    | integer       |           | not null | 0
    category | text          |           |          |
   Indexes:
       "products_pkey" PRIMARY KEY, btree (id)
   Check constraints:
       "products_price_check" CHECK (price >= 0)
       "products_stock_check" CHECK (stock >= 0)
   

   Copy

4. If you use Git, commit with a message such as "Add customers and products tables".

If DROP DATABASE complains that the database is being accessed by other users, you still have a psql session open on shopdb. Type \q in it and try again.

11. Try It Yourself

1. A bigger products idea. Add a column is_active boolean NOT NULL DEFAULT true to products with ALTER TABLE. Check it with \d products. Then remove it again, so your schema.sql still matches the recipe.
2. Design a reviews table. On paper or in a scratch file, write a CREATE TABLE reviews with: an identity primary key, a required rating that must be between 1 and 5 (use CHECK (rating BETWEEN 1 AND 5)), an optional comment, and a created_at that defaults to now. Run it, then try inserting a rating of 6 and read the error. Drop the table afterward.
3. Float vs numeric. Run SELECT 0.1::double precision * 3; and SELECT 0.1::numeric * 3;. Compare the output. Then run SELECT (0.1::double precision + 0.2::double precision) = 0.3::double precision; and the same with numeric. Which comparison is true?
4. Break each rule. Reload your two tables and trigger the NOT NULL, UNIQUE, CHECK, and wrong-type errors on customers and products. For each error, write down which line of the message names the constraint.
5. What would you pick? For each value choose a data type and justify it: a person's birthday, a phone number like +855 12 345 678, the exact time a payment happened, whether an item is gift wrapped, and a product weight in kilograms. (Hint: think about whether you will do math on a phone number.)
6. NULL puzzle. Predict the result of SELECT 10 + NULL, 'abc' || NULL, NULL IS NULL, NULL = NULL; and then run it. What is surprising?

Where to go next. You can now describe tables precisely and make the database enforce your rules. shopdb has two tables, but they are empty. In the next chapter, Inserting, Updating and Deleting Data, you fill them with INSERT, change them with UPDATE, remove rows with DELETE, and learn why the WHERE clause deserves your full respect.