PostgreSQL - Generate Columns
Last Updated :
16 Aug, 2024
When working with databases, there are scenarios where you need a column’s value to be automatically computed based on other columns. In PostgreSQL, this can be achieved through generated columns. These special columns are calculated based on an expression using other columns in the table. The value of a generated column is updated automatically whenever any of the referenced columns change, ensuring that the derived value is always accurate.
What are Generated Columns?
A generated column is a computed column whose value is derived from other columns within the same row. You cannot directly insert or update data into these columns because their values are determined by the expression defined when the table is created. In PostgreSQL, generated columns can only use immutable functions and cannot involve subqueries.
Types of Generated Columns
In theory, generated columns can be of two types:
- Stored Generated Columns: These columns are computed and stored in the database. The values are calculated during INSERT or UPDATE operations and occupy physical storage. Stored generated columns function similarly to materialized views, except they are automatically updated.
- Virtual Generated Columns: These columns are calculated when they are accessed and do not occupy storage. They are similar to standard views, as the values are computed on the fly during SELECT queries.
Note: As of the latest PostgreSQL versions, only stored generated columns are supported.
Syntax of PostgreSQL for CREATE TABLE using Generated Columns as follows:
CREATE TABLE table_name (
column_name_1 datatype(length) column constrain (if any),
column_name_2 datatype(length) column constrain (if any) ,
.
.
.
.
column_name datatype GENERATED ALWAYS AS (expression) STORED
);
- datatype: Specifies the data type of the generated column.
- expression: Defines the formula or expression used to calculate the value of the generated column.
- STORED: Indicates that the generated column is physically stored in the database.
PostgreSQL Generate Columns Example
Let us take a look at an example to Generate Columns in PostgreSQL to better understand the concept.
CREATE TABLE Addition (
number_1 int,
number_2 int,
number_3 int,
add int GENERATED ALWAYS AS (number_1 + number_2 + number_3) STORED
)
INSERT INTO Addition(number_1, number_2, number_3) VALUES (1,2,3) , (8,-9,2) , (7,1 ,NULL);
TABLE Addition;
Output:

The 'total_sum' column will display the sum of the three numbers for each row. If a NULL value is encountered, the result will also be NULL unless you handle it in the expression using functions like 'COALESCE()'.
Important Points About PostgreSQL Generated Columns
- Generated columns automatically update their values whenever referenced columns change.
- The expression for a generated column can only use immutable functions. Functions that produce different results for the same inputs over time (e.g., 'random()', 'now()') cannot be used.
- You can apply constraints (e.g., NOT NULL, UNIQUE) to generated columns just like any other column.
Similar Reads
PostgreSQL - Identity Column In PostgreSQL, an identity column is a specialized column type that automatically generates unique values for each row, making it ideal for primary keys and other unique identifiers. Introduced in PostgreSQL 10, the GENERATED AS IDENTITY clause offers a SQL-standard alternative to the widely-used SE
4 min read
PostgreSQL - INSERT PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once. With the PostgreSQL INSERT INTO clause, we can spe
4 min read
PostgreSQL - ADD COLUMN In PostgreSQL, the ADD COLUMN statement is a powerful command used to modify an existing database table by adding one or more new columns. This feature is important for adapting table structures to meet evolving data requirements, and it plays a key role in database management and optimization.In th
4 min read
sp_columns - SQL Server In SQL Server, managing and understanding database schemas is crucial for effective database administration and development. The sp_columns stored procedure is a valuable tool for retrieving detailed metadata about the columns of a specified table or view. In this article, We will learn about sp_col
6 min read
PostgreSQL - RENAME COLUMN Renaming columns in PostgreSQL is a common task for developers and database administrators. When aligning with naming conventions, fixing typos, or restructuring database schemas. Using the PostgreSQL ALTER TABLE RENAME COLUMN statement, we can efficiently rename one or more columns without losing d
5 min read
PostgreSQL - WITH Clause The WITH clause in PostgreSQL, also known as a Common Table Expression (CTE), simplifies complex queries by breaking them into smaller, readable sections. It allows us to define temporary result sets that can be referenced later in our main query. This makes the PostgreSQL code easier to manage and
4 min read
PostgreSQL - CREATE TABLE In PostgreSQL, the CREATE TABLE statement is used to define a new table within a database. It allows us to specify the table's structure, including column names, data types, and constraints, ensuring data integrity and consistency. Understanding the PostgreSQL table creation process is essential for
5 min read
PostgreSQL - CREATE TABLE AS The CREATE TABLE AS statement in PostgreSQL is a powerful tool used to create a new table and populate it with data returned by a query. This functionality allows you to generate tables on the fly based on query results, which can be very useful for reporting, analysis, and other tasks.Let us better
3 min read
PostgreSQL- CONCAT Function The PostgreSQL CONCAT function allows us to combine multiple strings or column values into a single output, making it a flexible tool for data manipulation. This function is essential for string concatenation tasks, whether weâre working with static text, columns from a database table, or dynamic SQ
4 min read
PostgreSQL - Create Database Creating a database in PostgreSQL is an important task for developers and database administrators to manage data effectively. PostgreSQL provides multiple ways to create a database, catering to different user preferences, whether through the command-line interface or using a graphical interface like
5 min read