Computed / calculated / virtual / derived / generated columns in PostgreSQL

Question

Does PostgreSQL support computed columns like MS SQL Server?

I can't find anything in the docs, but the feature is included in many other DBMS so maybe I am missing something?

Kenleigh

Using the lateral subquery expression (Postgres feature) you can easely add more columns to each row. – Outbound

Answer 1

Postgres 12 or newer

STORED generated columns are introduced with Postgres 12 - as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL, and Oracle. Or the similar "computed columns" of SQL Server.

Trivial example:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

fiddle

VIRTUAL generated columns may come with one of the next iterations. (Not in Postgres 16, yet).

Related:

• Attribute notation for function call gives error

Postgres 11 or older

Up to Postgres 11 "generated columns" are not supported.
You can emulate VIRTUAL generated columns with a function using attribute notation (tbl.col) that looks and works much like a virtual generated column. That's a bit of a syntax oddity which exists in Postgres for historic reasons and happens to fit the case. This related answer has code examples:

• Store common query as column?

The expression (looking like a column) is not included in a SELECT * FROM tbl, though. You always have to list it explicitly.

Can also be supported with a matching expression index - provided the function is IMMUTABLE. Like:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

Alternatives

Alternatively, you can implement similar functionality with a VIEW, optionally coupled with expression indexes. Then SELECT * can include the generated column.

"Persisted" (STORED) computed columns can be implemented with triggers in a functionally equivalent way.

Materialized views are a related concept, implemented since Postgres 9.3.
In earlier versions one can manage MVs manually.

Insincere

Depending on how much data you're loading at once.. trigger can slow things down drastically. May want to consider updates instead. – Mansour

These solutions are pretty much useless (without huge code changes to a codebase with no test cases) when migrating from oracle to postgres. Are there any solutions from the migration perspective ? – Inarticulate

@happybuddha: Please ask your question as question. Comments are not the place. You can always link to this question for context (and add a comment here to get my attention and link to the related question). – Insincere

@ErwinBrandstetter Done. Just didn't think it warranted a new question. #39824744 Cheers – Inarticulate

The functionality is in development right now: commitfest.postgresql.org/16/1443 – Hagio

so there are so many alternative ways to do something so simple and common in Postgres, anyone can explain how to go about selecting one of the ways? – Jacquejacquelin

@cryanbhu: Depends on the details of your setup and requirements. You might ask a new question with the necessary information. – Insincere

Maybe update the answer to mention PostgreSQL 12 Beta1 ? – Melindamelinde

@ChristopheRoussy: It was mentioned already. I added a code example now. – Insincere

Can't wait for Postgresql 12 to come out... :( – Feuillant

What is "VIRTUAL" column supposed to be? – Gablet

Found the answer: "virtual generated column occupies no storage and is computed when it is read." postgresql.org/docs/current/ddl-generated-columns.html – Gablet

Answer 2

YES you can!! The solution should be easy, safe, and performant...

I'm new to postgresql, but it seems you can create computed columns by using an expression index, paired with a view (the view is optional, but makes makes life a bit easier).

Suppose my computation is md5(some_string_field), then I create the index as:

CREATE INDEX some_string_field_md5_index ON some_table(MD5(some_string_field));

Now, any queries that act on MD5(some_string_field) will use the index rather than computing it from scratch. For example:

SELECT MAX(some_field) FROM some_table GROUP BY MD5(some_string_field);

You can check this with explain.

However at this point you are relying on users of the table knowing exactly how to construct the column. To make life easier, you can create a VIEW onto an augmented version of the original table, adding in the computed value as a new column:

CREATE VIEW some_table_augmented AS 
   SELECT *, MD5(some_string_field) as some_string_field_md5 from some_table;

Now any queries using some_table_augmented will be able to use some_string_field_md5 without worrying about how it works..they just get good performance. The view doesn't copy any data from the original table, so it is good memory-wise as well as performance-wise. Note however that you can't update/insert into a view, only into the source table, but if you really want, I believe you can redirect inserts and updates to the source table using rules (I could be wrong on that last point as I've never tried it myself).

Edit: it seems if the query involves competing indices, the planner engine may sometimes not use the expression-index at all. The choice seems to be data dependant.

Solis

Could you please explain or give an example of if the query involves competing indices? – Cynthiacynthie

Answer 3

One way to do this is with a trigger!

CREATE TABLE computed(
    one SERIAL,
    two INT NOT NULL
);

CREATE OR REPLACE FUNCTION computed_two_trg()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $BODY$
BEGIN
    NEW.two = NEW.one * 2;

    RETURN NEW;
END
$BODY$;

CREATE TRIGGER computed_500
BEFORE INSERT OR UPDATE
ON computed
FOR EACH ROW
EXECUTE PROCEDURE computed_two_trg();

The trigger is fired before the row is updated or inserted. It changes the field that we want to compute of NEW record and then it returns that record.

Noonan

When does the trigger acutally fire ? I ran the above and did this insert into computed values(1, 2); insert into computed values(4, 8); commit; select * from computed; and it just returned :1 2 and 4 8 – Inarticulate

try insert into computed(one) values(1); insert into computed(one) values(4); commit; select * from computed; the value of the two column will be calculated automagically! – Noonan

Answer 4

PostgreSQL 12 supports generated columns:

PostgreSQL 12 Beta 1 Released!

Generated Columns

PostgreSQL 12 allows the creation of generated columns that compute their values with an expression using the contents of other columns. This feature provides stored generated columns, which are computed on inserts and updates and are saved on disk. Virtual generated columns, which are computed only when a column is read as part of a query, are not implemented yet.

---

Generated Columns

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) STORED
);

db<>fiddle demo

Chor

Blog article: 2ndquadrant.com/en/blog/generated-columns-in-postgresql-12 – Melindamelinde

Answer 5

Well, not sure if this is what You mean but Posgres normally support "dummy" ETL syntax. I created one empty column in table and then needed to fill it by calculated records depending on values in row.

UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/

1. It is so dummy I suspect it is not what You are looking for.
2. Obviously it is not dynamic, you run it once. But no obstacle to get it into trigger.

Augustaaugustan

Answer 6

Example on creating an empty virtual column

,(SELECT *
  From (values (''))
  A("virtual_col"))

Example on creating two virtual columns with values

SELECT *
From (values (45,'Completed')
    , (1,'In Progress')
    , (1,'Waiting')
    , (1,'Loading')
   ) A("Count","Status")
order by "Count" desc

Spermato

Answer 7

I have a code that works and use the term calculated, I'm not on postgresSQL pure tho we run on PADB

here is how it's used

create table some_table as
    select  category, 
            txn_type,
            indiv_id, 
            accum_trip_flag,
            max(first_true_origin) as true_origin,
            max(first_true_dest ) as true_destination,
            max(id) as id,
            count(id) as tkts_cnt,
            (case when calculated tkts_cnt=1 then 1 else 0 end) as one_way
    from some_rando_table
    group by 1,2,3,4    ;

Solvable

What is PADB exactly? – Penile

ParAccel Analytic Database it's old but nice...en.wikipedia.org/wiki/ParAccel – Solvable

But how does it relate to a question about Postgres? Sure there are a lot of DBs with support of computed columns. – Penile

ah sorry I didnt take the time to get back in context.... PADB is postgress based! – Solvable

Answer 8

A lightweight solution with Check constraint:

CREATE TABLE example (
    discriminator INTEGER DEFAULT 0 NOT NULL CHECK (discriminator = 0)
);

Arteriotomy

How is this related to the concept of a calculated column? Would you care to explain? – Insincere

Agreed, it's not directly related. But is a substitution for a simple case when you just need to do somthing like field as 1 persisted. – Arteriotomy

A description would indeed have been nice. I think this answer is that if the computation can be done with the default clause then you can use a default and a check constraint to prevent anyone from changing the value. – Curricle

@Ross Bradbury: Agreed, but that only works for insert. It wouldn't work if a dependant column got updated. – Hygrophilous