GENERATED columns (stored and virtual) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a GENERATED column in PostgreSQL?

A GENERATED column is a special column in a table whose value is automatically computed from other columns using an expression. PostgreSQL only supports stored generated columns.

intermediate

What is a stored GENERATED column in PostgreSQL?

A stored GENERATED column saves the computed value physically in the table. Virtual GENERATED columns are not supported.

beginner

How do you define a stored GENERATED column in PostgreSQL?

Use the syntax: <column_name> data_type GENERATED ALWAYS AS (expression) STORED. The expression calculates the value stored in the column.

beginner

Can you insert or update values directly into a GENERATED column?

No, you cannot insert or update values directly into a GENERATED column because its value is always computed automatically from the expression.

intermediate

Why might you choose a stored GENERATED column?

Stored GENERATED columns improve query performance because values are precomputed and saved, but they use more storage. Virtual columns are not supported in PostgreSQL.

Which keyword is required to define a stored GENERATED column in PostgreSQL?

A STORED

B VIRTUAL

C COMPUTED

D CALCULATED

In PostgreSQL, the keyword STORED is used to define a stored GENERATED column.

Can you manually insert a value into a GENERATED column?

A Only if it is virtual

B No, values are computed automatically

C Yes, always

D Only if the column is nullable

GENERATED columns compute their values automatically; manual inserts or updates are not allowed.

What happens when you query a GENERATED column in PostgreSQL?

A The value is computed on the fly

B The query fails

C The value is retrieved from stored data

D The value is null

PostgreSQL only supports stored GENERATED columns; values are retrieved from storage.

Which of these is a benefit of stored GENERATED columns?

A Faster query performance

B Less disk space used

C Values can be manually changed

D No need to define an expression

Stored GENERATED columns improve query speed because values are precomputed and saved.

Which PostgreSQL version introduced support for stored GENERATED columns?

A 9.6

B 10

C 13

D 12

PostgreSQL 12 introduced support for stored GENERATED columns.

Explain what a GENERATED column is and how it works (stored vs virtual concept, noting PostgreSQL support).

Think about when the value is calculated and where it is saved.

Describe how to create a stored GENERATED column in PostgreSQL with an example.

Focus on the exact keywords and structure.