Computed calculated virtual derived columns in PostgreSQL

Question

Does PostgreSQL support computed   calculated columns  like MS SQL Server   I can t find anything in the docs  but as this feature is included in many other DBMSs I thought I might be missing something   Eg  http   msdn microsoft com en-us library ms191250 aspx

User · Answer

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    ;

User · Answer

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      It is so dummy I suspect it is not what You are looking for  Obviously it is not dynamic  you run it once  But no obstacle to get it into trigger

User · Answer

Up to Postgres 11 generated columns are not supported - as defined in the SQL standard and implemented by some RDBMS including DB2  MySQL and Oracle  Nor the similar  computed columns  of SQL Server   STORED generated columns are introduced with Postgres 12  Trivial example   CREATE TABLE tbl     int1    int   int2    int   product bigint GENERATED ALWAYS AS  int1   int2  STORED      db lt  fiddle here  VIRTUAL generated columns may come with one of the next iterations   Not in Postgres 13  yet       Related    Attribute notation for function call gives error     Until then  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 identical way   Materialized views are a closely related concept  implemented since Postgres 9 3  In earlier versions one can manage MVs manually

User · Answer

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

User · Answer

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 lt  fiddle demo

User · Answer

A lightweight solution with Check constraint   CREATE TABLE example       discriminator INTEGER DEFAULT 0 NOT NULL CHECK  discriminator   0

User · Answer

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

[postgresql] Computed / calculated / virtual / derived columns in PostgreSQL

Examples related to postgresql

Examples related to calculated-columns

Examples related to sql-view

Examples related to materialized-views

Examples related to generated-columns