ENUM Types, Generated Columns & Numeric Precision

ENUM type

A user-defined type that constrains a column to a specific, ordered set of text values. Unlike a CHECK constraint, ENUM values are stored as integers internally (more efficient) and the valid values are self-documenting in the schema. You can add values with ALTER TYPE ADD VALUE but cannot remove values without recreating the type.

GENERATED ALWAYS AS (...) STORED

A column whose value is computed from an expression over other columns in the same row, and stored physically (not computed on read). The database recalculates it automatically on every INSERT and UPDATE that touches the source columns. You cannot write to a GENERATED column directly.

🔌 Connect to the Beer Database

Before exploring advanced column types, connect to beer_db. Run psql -U beer_db -d beer_db to open a psql session.

psql -U beer_db -d beer_db

beer_db=>

🔍 Examine the ENUM CHECK on ingredients

Run \d ingredients and look at the CHECK constraint on the type column. This is a CHECK-based approach to restricting values — the column is plain text but only a fixed set of values is accepted.

\d ingredients

Check constraints: "ingredients_type_check" CHECK (type = ANY (ARRAY['hop'::text, 'malt'::text, 'yeast'::text, 'adjunct'::text]))

💥 Trigger the CHECK Violation with a Typo

Try to insert an ingredient with type = 'hop ' (with a trailing space). The CHECK constraint will catch it. This is the exact bug from the scenario.

INSERT INTO ingredients (name, type) VALUES ('Citra Test', 'hop ');

ERROR: new row for relation "ingredients" violates check constraint "ingredients_type_check" DETAIL: Failing row contains (Citra Test, hop , ...).

📊 Examine the Generated Column in climate_anomalies

Connect to weather_db, then run \d climate_anomalies to see the anomaly_c generated column. Notice the GENERATED ALWAYS AS expression.

\c weather_db weather_db

\d climate_anomalies

anomaly_c | numeric(5,2) | | generated always as ((avg_temp_c - baseline_temp_c)) stored

⚡ Insert a Row and Watch the Generated Column Populate

Insert a row into climate_anomalies with avg_temp_c = 18.5 and baseline_temp_c = 15.0. Use RETURNING * to confirm anomaly_c was computed automatically.

SELECT id FROM stations LIMIT 1;

INSERT INTO climate_anomalies (station_id, period, avg_temp_c, baseline_temp_c) VALUES ((SELECT id FROM stations LIMIT 1), '2025-01-01', 18.5, 15.0) RETURNING *;

avg_temp_c | baseline_temp_c | anomaly_c ------------+------------------+----------- 18.50 | 15.00 | 3.50

🔍 Examine the review_tsv Generated Column in beer_ratings

The weather example stored a calculated number: two temperatures go in, their difference comes out. Now we will use the same idea with text: a review goes in, search terms come out.

Stay in psql. Switch back with \c beer_db beer_db, then run \d beer_ratings.

Find these two columns:

• review_text (text): the original sentence a person writes.
• review_tsv (tsvector): a search representation PostgreSQL calculates from that sentence. A tsvector stores normalized words and their positions, rather than the original prose.

Read the generated expression from the inside out:

to_tsvector('english', COALESCE(review_text, ''))

1. COALESCE(review_text, '') uses the review text, or an empty string if it is NULL.
2. to_tsvector('english', ...) processes it using English rules, including reducing word forms to search terms.
3. GENERATED ALWAYS AS (...) STORED makes PostgreSQL calculate and store the result when the row is inserted or updated.

Under Indexes, idx_beer_ratings_fts is a separate GIN index on review_tsv. The generated column holds the search terms; the index helps find matching rows efficiently. Updating a review recalculates that row's column and maintains its index entries automatically.

The new approach: your application writes only review_text. The database owns the rule that keeps review_tsv in sync. The next steps will make this visible with one short review.

\c beer_db beer_db

\d beer_ratings

review_tsv | tsvector | | generated always as (to_tsvector('english'::regconfig, COALESCE(review_text, ''::text))) stored

🧩 Turn a Sentence into Search Terms

Before using the generated column, try its main function on its own:

SELECT to_tsvector('english', 'Fresh beer') AS search_terms;

Expect 'beer':2 'fresh':1. The words are lowercased and listed in sorted order. The numbers record their positions in the original sentence: “Fresh” was first, “beer” second.

This SELECT only shows the conversion; it does not save a review. In beer_ratings, the generated-column formula performs this conversion for you whenever you write the source text.

SELECT to_tsvector('english', 'Fresh beer') AS search_terms;

'beer':2 'fresh':1

✍️ Insert Only the Review Text

Insert a practice review with the text Fresh beer. Supply a beer ID, reviewer name, and score because those columns are required. The subquery picks an existing beer ID for you.

INSERT INTO beer_ratings (beer_id, reviewer_name, score, review_text)
VALUES ((SELECT id FROM beers ORDER BY id LIMIT 1),
        'Generated column learner', 8.0, 'Fresh beer')
RETURNING review_text, review_tsv;

Leave review_tsv out of the INSERT. PostgreSQL fills it using the formula you just inspected. RETURNING shows both columns from the saved row: the original text and 'beer':2 'fresh':1.

We use a distinctive reviewer name so the next steps can find this practice row without changing other reviews.

INSERT INTO beer_ratings (beer_id, reviewer_name, score, review_text) VALUES ((SELECT id FROM beers ORDER BY id LIMIT 1), 'Generated column learner', 8.0, 'Fresh beer') RETURNING review_text, review_tsv;

Fresh beer | 'beer':2 'fresh':1

🔄 Change the Text and Watch the Search Terms Change

Change the practice review from Fresh beer to Bitter hops:

UPDATE beer_ratings
SET review_text = 'Bitter hops'
WHERE reviewer_name = 'Generated column learner'
RETURNING review_text, review_tsv;

The WHERE clause limits the change to your practice review. Again, you write only the source text.

Expect review_tsv to become 'bitter':1 'hop':2. The old terms fresh and beer disappear. English processing reduces “hops” to hop.

With an ordinary manually maintained column, changing the text alone would leave old search terms behind. With this generated column, PostgreSQL recalculates the stored vector as part of the UPDATE. There is no second application update to forget.

UPDATE beer_ratings SET review_text = 'Bitter hops' WHERE reviewer_name = 'Generated column learner' RETURNING review_text, review_tsv;

Bitter hops | 'bitter':1 'hop':2

🔎 Prove Searches Follow the Updated Review

Check whether your updated review matches its old word fresh or its new word hops:

SELECT review_text,
       review_tsv @@ plainto_tsquery('english', 'fresh') AS matches_old_word,
       review_tsv @@ plainto_tsquery('english', 'hops') AS matches_new_word
FROM beer_ratings
WHERE reviewer_name = 'Generated column learner';

plainto_tsquery converts the search input using the same English rules. @@ asks “does this vector match this search?” and returns a boolean.

Expect Bitter hops, f for the old word, and t for the new word. Searching for “hops” works even though the vector stores hop, because both sides use the same normalization.

You have now followed the whole chain: write review text → PostgreSQL generates search terms → searches use the current terms. The GIN index can speed up matching on a large table; it does not change which words match. You will explore full-text search in more depth later.

SELECT review_text, review_tsv @@ plainto_tsquery('english', 'fresh') AS matches_old_word, review_tsv @@ plainto_tsquery('english', 'hops') AS matches_new_word FROM beer_ratings WHERE reviewer_name = 'Generated column learner';

Bitter hops | f | t