Generated Columns, Defaults, Collations, Encoding, and Data-Integrity Design

Learning outcomes

Defaults and generated columns both produce values, but they solve different problems. A default supplies a value when an INSERT omits one; a generated column is derived from other row values and cannot be independently overridden. PostgreSQL 18 also changes an important historical assumption: generated columns can now be virtual as well as stored, and virtual is the default.

Text correctness has another layer: encoding decides how characters are represented, while collation decides language/order/comparison behavior. Collation provider versions can change after an operating-system or ICU upgrade, which can invalidate the ordering assumptions stored in indexes. This lesson joins these mechanisms into a practical integrity model.

1. Defaults answer “what if the caller omits this value?”

A default expression is evaluated for a new row when an INSERT does not provide the column. The caller can still provide another value unless other rules prevent it. Defaults may use volatile/contextual expressions such as now() or gen_random_uuid().

CREATE TABLE app.ch04_generated_probe (    line_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    created_at timestamptz NOT NULL DEFAULT now(),    quantity integer NOT NULL CHECK (quantity > 0),    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0));INSERT INTO app.ch04_generated_probe(quantity, unit_price)VALUES (2, 19.95)RETURNING line_id, created_at, quantity, unit_price;

The default does not continuously recompute. Updating other columns later leaves created_at unchanged unless an UPDATE explicitly changes it.

2. Generated columns answer “what is derived from this row?”

A generated column is always derived from row values. PostgreSQL 18 supports virtual generated columns (computed when read, no stored value) and stored generated columns (computed on write and stored). Virtual is the PostgreSQL 18 default if neither keyword is written. Older PostgreSQL versions only supported stored generated columns, so migrations targeting multiple majors must version-check.

ALTER TABLE app.ch04_generated_probeADD COLUMN line_total numeric(14,2)    GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL;ALTER TABLE app.ch04_generated_probeADD COLUMN line_total_stored numeric(14,2)    GENERATED ALWAYS AS (quantity * unit_price) STORED;SELECT line_id, quantity, unit_price, line_total, line_total_storedFROM app.ch04_generated_probe;UPDATE app.ch04_generated_probe SET quantity=3 WHERE line_id=1;SELECT line_id, quantity, unit_price, line_total, line_total_storedFROM app.ch04_generated_probe;

Both generated columns track the row expression automatically. The difference is when computation occurs and whether the derived value occupies storage.

3. Generation expressions must be immutable

PostgreSQL requires generated expressions to use immutable functions/operators and only the current row; subqueries and references to other rows/tables are not allowed. Generated columns cannot reference another generated column. PostgreSQL 18 virtual generated columns have additional restrictions: they cannot use user-defined types/functions directly or indirectly, whereas stored generated columns do not have that particular restriction.

A common wrong design is “generated current time”:

-- Expected to fail because now() is not immutable.ALTER TABLE app.ch04_generated_probeADD COLUMN recalculated_now timestamptzGENERATED ALWAYS AS (now()) STORED;

If the rule is “capture insertion time,” use DEFAULT now(). If the rule is “derive total from quantity and unit price,” a generated expression fits because the same row values deterministically determine the result.

4. Inspect generated/default metadata instead of inferring it

SELECT column_name,       column_default,       is_identity,       is_generated,       generation_expressionFROM information_schema.columnsWHERE table_schema='app' AND table_name='ch04_generated_probe'ORDER BY ordinal_position;

For exact PostgreSQL definitions, psql \d+ app.ch04_generated_probe and catalog helper functions are valuable. Migration tooling should compare actual definitions, not just column names.

5. Encoding: server/database representation versus client conversion

PostgreSQL databases have an encoding chosen when the database is created. A client session also has client_encoding; PostgreSQL can convert between compatible client/server encodings. Most modern application databases use UTF-8, but a production lesson should inspect rather than assume.

SHOW server_encoding;SHOW client_encoding;SELECT current_database() AS db,       pg_encoding_to_char(encoding) AS database_encodingFROM pg_catalog.pg_databaseWHERE datname=current_database();SELECT 'سلام · Bakı · PostgreSQL' AS unicode_round_trip;

Changing client_encoding is not a way to “convert the database.” It changes the session's client/server conversion contract. An incorrectly configured driver can misinterpret bytes before the data reaches business logic, so connection initialization and tests should include representative non-ASCII text.

6. Collation: how text compares and sorts

Encoding tells PostgreSQL how characters are represented; collation tells it how strings are ordered and classified for operations that use a collation. PostgreSQL 18 can expose collations backed by the builtin provider, ICU, or libc depending on build/platform. ICU availability must be verified; libc locale names/behavior can differ across operating systems.

SELECT collname,       collprovider,       collisdeterministic,       collencoding,       collversionFROM pg_catalog.pg_collationWHERE collname IN ('C','POSIX','ucs_basic','unicode')   OR collname LIKE 'en%'ORDER BY collnameLIMIT 30;

Do not copy a locale name from Linux into Windows and assume it exists or sorts identically. A portable deployment should document provider/locale expectations and validate them during provisioning/upgrades.

7. Make collation differences observable without assuming ICU

The C collation is widely available and provides byte/codepoint-oriented behavior appropriate for deterministic technical identifiers. Your database default may use another provider/locale. Compare results locally and label them observations because locale inventories differ.

CREATE TABLE app.ch04_collation_probe (    value text NOT NULL);INSERT INTO app.ch04_collation_probe(value)VALUES ('a'),('A'),('á'),('ä'),('z');SELECT value FROM app.ch04_collation_probe ORDER BY value COLLATE "C";SELECT value FROM app.ch04_collation_probe ORDER BY value;

If the orders match in your environment, that does not prove all collations are equivalent. It only records one dataset and one database's default. If an ICU collation is available, create a disposable explicit collation and compare it—but do not make ICU an unconditional lab dependency.

SELECT EXISTS (  SELECT 1 FROM pg_catalog.pg_collation WHERE collprovider='i') AS icu_collations_available;

8. Deterministic versus nondeterministic comparison

ICU can provide nondeterministic collations where different byte strings can compare equal under rules such as case/accent insensitivity. That can be powerful for user-facing search/uniqueness, but equality semantics, index behavior, and performance change. Treat it as an explicit data-model decision rather than a cosmetic ORDER BY option.

For identifiers, protocol keys, hashes, and security tokens, use semantics appropriate to exact technical equality. For human names, locale-aware order may be desirable, but “human equality” is a product rule that may require normalization and explicit matching strategy beyond one collation.

9. Collation version drift can invalidate stored order assumptions

When a collation object is created, PostgreSQL can record a provider version. An operating-system or ICU upgrade may change collation rules. PostgreSQL can then warn that the recorded version differs from the provider's current version. This matters because B-tree indexes and other stored structures may have been built using the old ordering rules.

SELECT c.oid::regcollation AS collation,       c.collprovider,       c.collversion AS recorded_version,       pg_collation_actual_version(c.oid) AS actual_versionFROM pg_catalog.pg_collation AS cWHERE c.collversion IS NOT NULLORDER BY 1LIMIT 30;

The correct response is not “run REFRESH VERSION until the warning disappears.” First identify and rebuild affected objects (for example with an appropriate REINDEX plan), validate application ordering/equality assumptions, and only then refresh the recorded version. ALTER COLLATION ... REFRESH VERSION updates metadata; PostgreSQL documentation explicitly notes that it does not prove dependent indexes were rebuilt correctly.

10. Integrated ServiceHub design

Combine the chapter mechanisms into one disposable table. The schema uses exact decimal money, a real instant, a UUID public ID, row-level constraints, an identity key, defaults, and a derived total.

CREATE TABLE app.ch04_invoice_line (    line_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    public_id uuid NOT NULL DEFAULT uuidv7() UNIQUE,    created_at timestamptz NOT NULL DEFAULT now(),    description text NOT NULL CHECK (length(description) > 0),    quantity integer NOT NULL CHECK (quantity > 0),    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),    line_total numeric(14,2)        GENERATED ALWAYS AS (quantity * unit_price) STORED);INSERT INTO app.ch04_invoice_line(description, quantity, unit_price)VALUES ('Replacement seal', 3, 12.50)RETURNING line_id, public_id, created_at, description, quantity, unit_price, line_total;

Why STORED here instead of virtual? There is no universal answer. If the value is cheap to compute and frequently changed, virtual may avoid storage/write work. If it is frequently read, useful for downstream replication/export, or the expression is expensive, stored can be attractive. Measure the real workload and consider version/topology limitations before standardizing.

11. Deliberately wrong integrity designs

Wrong 1: use a generated column for now(). Time is volatile, not a deterministic function of row values; use a default or explicit application/event time.

Wrong 2: refresh collation version metadata without rebuilding dependencies. This hides a warning while leaving structures built under old comparison rules.

Wrong 3: treat client encoding as the database encoding. A client setting governs conversion for one connection; the database encoding is a creation-time property.

Wrong 4: rely on implicit default collation for stable technical identifiers. Document the comparison semantics that technical keys require.

12. Hands-on lab: prove data-integrity behavior

1. Create app.ch04_generated_probe, add both virtual and stored totals, and update quantity to verify recomputation.
2. Attempt the now() generated column and capture the expected immutability error.
3. Record server/database/client encodings and round-trip multilingual text.
4. Inventory available collation providers and compare C order with the database default using the small dataset.
5. Query recorded/actual collation versions; do not modify system collations as part of the mandatory lab.
6. Create app.ch04_invoice_line and explain every type/default/constraint/generation choice in one sentence.

13. Chapter cleanup

Inspect the prefix before dropping anything. The following list targets only this chapter's disposable objects.

DROP TABLE IF EXISTS app.ch04_invoice_line;DROP TABLE IF EXISTS app.ch04_generated_probe;DROP TABLE IF EXISTS app.ch04_collation_probe;DROP TABLE IF EXISTS app.ch04_maintenance_window;DROP TABLE IF EXISTS app.ch04_route_stop;DROP TABLE IF EXISTS app.ch04_validation_probe;DROP TABLE IF EXISTS app.ch04_work_order;DROP TABLE IF EXISTS app.ch04_customer;DROP TABLE IF EXISTS app.ch04_external_ref_default;DROP TABLE IF EXISTS app.ch04_external_ref_strict;DROP TABLE IF EXISTS app.ch04_type_lab;DROP TABLE IF EXISTS app.ch04_numeric_probe;DROP TABLE IF EXISTS app.ch04_text_probe;DROP TABLE IF EXISTS app.ch04_uuid_probe;

14. Chapter 04 summary and bridge to Chapter 05

PostgreSQL integrity begins before a query runs. Built-in and custom types narrow valid states; constraints protect row and relationship invariants; identity/sequences allocate identifiers under concurrency; generated columns derive deterministic row data; encoding/collation define text representation and comparison semantics. The most important habit is to make those mechanisms observable through metadata and failure cases instead of relying on ORM declarations or assumptions.

Chapter 05 shifts from definition to querying: joins, subqueries, CTEs, set operations, and PostgreSQL's LATERAL feature. The richer types and constraints from this chapter will make those queries more meaningful because the underlying data contract is now explicit.

Authoritative references

• PostgreSQL 18 — Default Values
• PostgreSQL 18 — Generated Columns
• PostgreSQL 18 — CREATE TABLE
• PostgreSQL 18 — Character Set Support
• PostgreSQL 18 — Locale Support
• PostgreSQL 18 — Collation Support
• PostgreSQL 18 — ALTER COLLATION
• PostgreSQL 18 — pg_collation
• PostgreSQL 18 — Release Notes (virtual generated columns and UUIDv7)