PostgreSQL / Basic: relational foundations / Lesson 2

Types, constraints and generated identity

Describe parcel facts with exact types, valid relationships and deliberate missing-value rules. Distinguish identity generation from uniqueness and time-zone display.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Choose numeric, text and timestamp types from meaning.
Explain identity, CHECK, NOT NULL and foreign-key contracts.

Choose meaning before storage convenience

Use numeric(9,2) for an exact fee with seven integer and two fractional digits, rather than approximate floating point. Text is appropriate for postal codes whose leading zeroes matter. PostgreSQL text and varchar are not universal performance opposites; choose a length constraint when the business requires it. A boolean stores a truth value, not a portable MySQL-style tiny integer convention. Explicit types help PostgreSQL reject incompatible expressions instead of relying on another product’s coercion rules.

Express several independent constraints

This definition assumes an already isolated quay_lab schema. GENERATED ALWAYS AS IDENTITY supplies values but does not by itself make a column unique, so PRIMARY KEY remains explicit. CHECK constrains known fee values, while NOT NULL separately rejects missing fees; CHECK alone accepts an unknown result. The nullable delivered_at column records an absent delivery instant. PostgreSQL supports transactional treatment of most ordinary DDL, unlike MySQL’s general implicit-commit behavior, but later lessons identify operations that cannot run inside transaction blocks.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE TABLE quay_lab.parcels (
 parcel_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 tracking_code text NOT NULL UNIQUE,
 fee numeric(9,2) NOT NULL CHECK (fee >= 0),
 delivered_at timestamptz
);

Identity syntax is available from PostgreSQL 10. It does not promise gap-free numbering.

An instant differs from a local clock reading

timestamp with time zone, commonly written timestamptz, represents an instant and displays it using the session time zone; it does not retain the original zone label. timestamp without time zone represents a wall-clock value without that conversion meaning. Capture a source zone separately when it is business data. NULL means unknown or absent, not zero or empty text. Equality with NULL is unknown; use IS NULL or a deliberate null-safe comparison when the question requires it.

Reason about values without a server fixture

The literal query returns the exact fee 12.30, true for the missing delivery test, and true for equal instants written with different offsets. The offset example compares values, not formatted display strings, so its boolean result does not depend on the current display time zone. Naming output columns makes the contract readable. An application should still bind typed parameters through its connector instead of concatenating text into these literals. Validation belongs at input and at the database constraint boundary.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT 12.30::numeric(9,2) AS fee,
 NULL::timestamptz IS NULL AS missing_delivery,
 timestamptz '2026-10-01 10:00:00+00' =
 timestamptz '2026-10-01 12:00:00+02' AS same_instant;

Expected output

fee   | missing_delivery | same_instant
12.30 | true             | true

Logical boolean values are true; psql commonly renders them as t. Client formatting may differ.

Keys and collation need separate review

A foreign key requires a referenced unique or primary key and compatible column types. PostgreSQL does not automatically create an index on referencing columns, so consider one for joins and parent-change checks. Unique constraints ordinarily permit multiple NULLs; NULLS NOT DISTINCT is available from PostgreSQL 15 when the business needs null values treated as equal. Text comparison also depends on collation and provider. Do not import MySQL collation names or assume changing a database default rewrites existing stored definitions. Choose parent-delete behavior such as RESTRICT or CASCADE from business consequences. A nullable child reference can be absent; add NOT NULL when the relationship is mandatory.

Common errors and fixes

null value violates not-null constraint (23502)

Why it happens: A required fact was omitted or explicitly NULL.
How to fix it: Supply a legitimate value or reconsider whether absence is valid.
Prevent it next time: Model missing-value rules independently from CHECK predicates.

duplicate key value violates unique constraint (23505)

Why it happens: The proposed identity or tracking code conflicts with existing data.
How to fix it: Identify the named constraint and distinguish retries from conflicting records.
Prevent it next time: Define idempotency and tracking-code normalization before import.

A foreign-key lookup is unexpectedly expensive

Why it happens: The child reference columns have no suitable index.
How to fix it: Inspect the workload and propose an appropriate referencing-side index.
Prevent it next time: Review foreign-key indexes explicitly rather than assuming automatic creation.

Check your understanding

1. Does CHECK (fee >= 0) alone prohibit NULL? Explain the smallest complete fee contract.

Show worked solution

No. A CHECK is satisfied when its expression is true or unknown, so NULL can pass. Use NOT NULL for mandatory presence and CHECK (fee >= 0) for the allowed known values. Keep an exact numeric type with a documented bound. These constraints answer different questions and remain necessary even if the form validates input.

2. Why can identity values have gaps after an aborted insert, and should a receipt policy use them as a gap-free legal sequence?