The Developer Space

Developer's Cafe

  • Cloud
  • Database
  • Open Source
  • Programming
  • Web Dev
  • Mobile
  • Security
  • QuickRef
  • Home
  • Database
  • Generated Columns in PostgreSQL

Generated Columns in PostgreSQL

Shameel Ahmed - CodeProject, Database, Open Source, PostgreSQL
September 18, 2019April 18, 2021 No Comments
0 0
Read Time5 Minute, 42 Second

Introduction

A generated column or a computed column is to columns what a view is to a table. PostgreSQL uses the term ‘Generated‘ columns for Computed columns. The value of the column is always computed or generated from other columns in the table. A generated column can either be virtual or stored. The values for virtual columns are computed on the fly during query time and they don’t take storage space. The values for stored columns are pre-computed and stored as part of table data.

Background

Modern databases like SQL Server and Oracle have long had Computed Columns and the lack of computed columns in PostgreSQL made migrations from other Databases quite difficult. This article attempts to explore the different ways of achieving the same functionality in different versions of PostgreSQL.

PostgreSQL 12

PostgreSQL 12 is the next major release of the world’s most popular and feature-rich open source database. The stable version of PostgreSQL 12 is scheduled to be released in late 2019 and supports Generated Columns.

The GENERATED ALWAYS AS clause is used to create Generated columns. The expression used to define a generated column is called generation expression.

-- PostgreSQL syntax
CREATE TABLE employee (
    ...,
    dob timestamp,
    age integer GENERATED ALWAYS AS _
                (date_part('year', CURRENT_TIME) - date_part('year', dob)) STORED
);

Comparison with SQL Server Computed Column

Contrast this to SQL Server syntax. There is no special keyword in SQL Server to declare a computed column. You just specify the expression that makes us the computed column after the AS clause.

-- SQL Server Syntax
CREATE TABLE Employee (
    ...,
    dob datetime,
    age AS DATEDIFF(year,dob,GETDATE()) PERSISTED
);

Note the PERSISTED clause which is the equivalent of PostgreSQL’s STORED clause. SQL Server also supports non-persisted computed columns, you just don’t specify the PERSISTED clause. But PostgreSQL currently implements only stored generated columns.

So How Is a Generated Column Different From a Regular Column With a DEFAULT Clause?

  1. The column default is evaluated once when the row is first inserted if no other value was provided; a generated column is updated whenever the row changes and cannot be overridden.
  2. A column with DEFAULT constraint can be given a value in an INSERT or UPDATE statement. Generated columns cannot be given values, they’re always computed.
  3. A column default cannot refer to other columns of the table, whereas a generated columns is specifically meant to do so.
  4. A column default can use volatile functions, for example, random() or current_time, generated columns cannot.

Limitations and Restrictions

Several restrictions apply to the definition of generated columns and tables involving generated columns:

  1. The generation expression can only use immutable functions and not volatile functions. In SQL Server terminology, they’re called deterministic and non-deterministic functions, respectively. posgtres doc2
  2. The generation expression cannot use subqueries or reference anything other than the current row in any way. posgtres doc2
  3. A generation expression cannot reference another generated column. posgtres doc2
  4. A generation expression cannot reference a system column, except tableoid. posgtres doc2
  5. A generated column cannot have a column default or an identity definition. posgtres doc2
  6. A generated column cannot be part of a partition key. posgtres doc2

Additional Considerations

  1. Foreign tables can have generated columns. posgtres doc2
  2. Access privileges for Generated columns are maintained separately from their underlying base columns. So, you can grant access to roles to read from a generated column but not from the underlying base columns. posgtres doc2
  3. Generated columns are, conceptually, updated after BEFORE triggers have run. Therefore, changes made to base columns in a BEFORE trigger will be reflected in generated columns. But generated columns themselves cannot be accessed in BEFORE triggers. posgtres doc2

PostgreSQL 11.x and Older

The stable version of PostgreSQL 12 is yet to be released as of this writing and workloads running on older versions might still need this functionality. In PostgreSQL 11.x and older, there are two ways to achieve this:

Use a View

In this approach, the table doesn’t have the ‘age‘ column. The view is used wherever ‘age‘ column is needed.

CREATE VIEW v_employee AS
SELECT dob, date_part('year', CURRENT_TIME) - date_part('year', dob) as age
FROM employee;

Use a Normal Column and Update It With a Trigger

In this approach, ‘age‘ is declared as a regular integer column and a trigger populates the column during inserts and updates.

CREATE TABLE employee (     
    ...,     
    dob timestamp,     
    age integer 
);
CREATE OR REPLACE FUNCTION calc_age() RETURNS TRIGGER AS $body$
BEGIN
  NEW.age := date_part('year', CURRENT_TIME) - date_part('year', dob);
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

The major drawback to this approach is the additional maintenance overhead of creating and maintaining views/triggers and remembering to update them when business logic changes, Upgrading your databases to PostgreSQL 12 when the stable version is released would be a good idea to mitigate the drawbacks and remove the maintenance overheads.

I hope you found this article useful. Please leave a comment if you have any questions, feedback or suggestions to improve the article or expand its content.

More Reading

PostgreSQL 12: New Features and Enhancements
PostgreSQL finally gets Stored Procedures

Share

Facebook
Twitter
LinkedIn
Email

Post navigation

PostgreSQL 12: New Features and Enhancements
New Features and Enhancements in .NET Core 3.0

Related Articles

Migrate your SQL Server Workloads to PostgreSQL: Quick Reference: Second Edition

Book: Migrate your SQL Server Workloads to PostgreSQL: Quick Reference – Second Edition

Shameel Ahmed
October 18, 2022October 22, 2022 1 Comment

How to fix Redis Error “ERR DEBUG command not allowed.”

Shameel Ahmed
July 4, 2022July 5, 2022 No Comments

Re:Link for Mac is here!!!

Shameel Ahmed
November 14, 2021November 14, 2021 No Comments

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

.NET Architecture Artificial Intelligence ASP.NET AWS Azure Books C# Career Cloud CodeProject Conversational Bots Database Data Security Facade IDEs Java Mobile MongoDB MySQL Open Source Patterns PostgreSQL Programming Python Redis Security SQL Server Tools Uncategorized Web Development Windows Phone

Recent Posts

  • Developer to Architect Series (Red Hat Enable Architect) January 16, 2023
  • Can ChatGPT replace Google Search? January 11, 2023
  • Learn Python with ChatGPT December 26, 2022
  • Book: Migrate your SQL Server Workloads to PostgreSQL: Quick Reference – Second Edition October 18, 2022
  • Increase Visual Studio Code Terminal Buffer Size July 14, 2022

Archives

  • January 2023 (2)
  • December 2022 (1)
  • October 2022 (1)
  • July 2022 (2)
  • February 2022 (1)
  • November 2021 (1)
  • July 2021 (1)
  • June 2021 (1)
  • September 2020 (1)
  • May 2020 (2)
  • April 2020 (1)
  • October 2019 (1)
  • September 2019 (4)
  • July 2019 (2)
  • May 2018 (1)
  • September 2017 (1)
  • April 2017 (1)
  • April 2014 (1)
  • August 2011 (1)
  • June 2009 (1)
Copyright 2022. The Developer Space | Theme: OMag by LilyTurf Themes
  • DbStudio
  • Re:Link
  • shameel.net
  • Privacy Policy
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
SAVE & ACCEPT