Data Types and Constraints

A column's type decides what values it can hold, how much space each takes, and what operations make sense. A constraint decides which of those values are actually allowed. Together they are the cheapest data-quality tool you will ever get: they run on every write, from every client, forever.

Getting these wrong rarely hurts on day one. It hurts eight months later, when somebody is cleaning twelve thousand corrupt rows by hand.

Numeric types#

NUMERIC(10,2) means up to 10 digits in total, 2 of them after the decimal point. unit_price NUMERIC(10,2) stores 499.00 exactly.

Floating-point types store binary approximations. Some decimal fractions cannot be written exactly in binary, the same way one third cannot be written exactly in decimal.

SELECT 0.1::double precision + 0.2::double precision = 0.3::double precision AS float_equal,
       0.1::numeric          + 0.2::numeric          = 0.3::numeric          AS numeric_equal;

 float_equal | numeric_equal
-------------+---------------
 f           | t

That is why money never goes in a FLOAT. Use NUMERIC, or store whole minor units (paise, cents) in a BIGINT.

Integer division also catches people. In PostgreSQL, 10 / 4 is 2, because both operands are integers. 10 / 4.0 is 2.5000000000000000. Cast one side if you want a fraction.

String types#

CHAR(n) pads with spaces to length n. VARCHAR(n) stores up to n characters. TEXT stores any length.

In PostgreSQL all three are stored the same way internally, and TEXT is exactly as fast as VARCHAR(n). Use VARCHAR(n) when the limit is a real business rule (a 2-letter country code) and TEXT otherwise. Avoid CHAR(n) almost always. Padding causes comparison surprises and saves nothing.

MySQL and SQL Server behave differently here, so check your target system before assuming.

Date and time types#

For anything that records when something happened, prefer TIMESTAMPTZ. A plain TIMESTAMP stores 2024-03-15 09:00 with no idea whether that was Bengaluru or Berlin. The ambiguity is invisible until you have users in two time zones.

The course schema uses DATE for order_date (the day matters, the minute does not) and TIMESTAMP for created_at to keep examples simple. In a real system created_at would be TIMESTAMPTZ.

Others you will meet#

BOOLEAN holds TRUE, FALSE or NULL. UUID holds 128-bit identifiers, useful when IDs are generated outside the database. JSONB holds structured documents and gets its own lesson in Module 7.

Constraints: rules the database enforces#

NOT NULL#

The column must have a value. Make this your default and relax it only when "no value" is a real, meaningful state. customers.phone is nullable because some customers legitimately have none.

PRIMARY KEY#

Uniquely identifies each row. It implies NOT NULL and UNIQUE, and PostgreSQL builds a unique B-tree index behind it, which is why lookups by key are fast. A table has at most one primary key, but it can span several columns, as in order_items (order_id, product_id).

UNIQUE#

No two rows may share the value. customers.email is unique, so a second signup with the same email fails.

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

A subtle rule: UNIQUE allows multiple NULLs, because NULL is not equal to anything, including another NULL. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT for the cases where you want otherwise.

FOREIGN KEY#

The value must exist in the referenced table. It stops orphans.

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

It also blocks deletes that would leave orphans behind:

DELETE FROM customers WHERE customer_id = 1;
ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=(1) is still referenced from table "orders".

You choose what happens on delete:

customer_id INTEGER REFERENCES customers (customer_id) ON DELETE CASCADE

Be careful with CASCADE. It is correct for owned data (an order's lines die with the order) and dangerous anywhere else.

CHECK#

An arbitrary condition that must hold.

unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
status     VARCHAR(20)   NOT NULL CHECK (status IN ('pending','paid','shipped','delivered','cancelled'))

ERROR:  new row for relation "products" violates check constraint "products_unit_price_check"

One trap deserves a mention: a CHECK passes when its condition evaluates to NULL, not just when it is TRUE. CHECK (salary > 0) accepts a NULL salary. Pair it with NOT NULL when you mean "a positive number must be present".

DEFAULT#

The value used when an INSERT omits the column. status ... DEFAULT 'pending' means new orders start pending without the application saying so.

Naming and adding constraints#

PostgreSQL invents names like products_unit_price_check. They are readable, but name important ones yourself so migrations and error handling can refer to them.

ALTER TABLE products
  ADD CONSTRAINT chk_products_price_nonneg CHECK (unit_price >= 0);

What a foreign key does not do#

PostgreSQL creates an index for primary keys and unique constraints automatically. It does not index the referencing column of a foreign key.

That matters because every parent delete has to check the child table for referencing rows. Without an index on orders.customer_id, that check scans the whole orders table.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Index your foreign keys unless you have a measured reason not to. Module 6 shows why.

Common mistakes#

Using FLOAT for money. Rounding errors accumulate and invoices stop matching.

Making everything nullable. Then every query needs IS NOT NULL guards and every report quietly has holes.

Enforcing rules only in application code. The second service, the import script and the late-night manual fix will not run your validation. The database will.

Storing dates as strings. '03/04/2024' is March 4th or April 3rd depending on who reads it, and it sorts alphabetically, not chronologically.

Choosing INTEGER for an ID that will outgrow it. Altering a 2-billion-row column type later means rewriting the table. Use BIGINT when growth is plausible.

Practice#

1. Create a table loyalty_cards with: an auto-numbered primary key, a customer_id that must exist in customers and be unique (one card per customer), a points column that is never negative and defaults to 0, and a tier restricted to 'silver', 'gold', 'platinum'.
2. Write four INSERT statements that each violate a different constraint. Run them and read the four error messages.
3. Run SELECT 7 / 2, 7 / 2.0, 7 % 2; and explain each result.
4. Decide the right type for each: a product weight in grams, a price in INR, the exact moment a payment was received, a two-letter country code, a user's date of birth.