SQL - How to generate boolean based from condition of two columns-CodePudding

I have two tables named property and equipment_type.

CREATE TABLE IF NOT EXISTS equipment_type (
    class_code              class_code          NOT NULL,
    major_code              character(2)        NOT NULL,
    minor_code              character(2)        NOT NULL,
    estimated_useful_life   integer             NOT NULL,    -- in years
    PRIMARY KEY (class_code, major_code, minor_code)
);

CREATE TABLE IF NOT EXISTS PROPERTY(
    property_number         character(16)       PRIMARY KEY,
    class_code              class_code          NOT NULL,
    major_code              character(2)        NOT NULL,
    minor_code              character(2)        NOT NULL,
    date_acquired           date                NOT NULL,               
    warranty_period         integer,
    warranty_start_date     date,
    warranty_end_date       date
        GENERATED ALWAYS AS (
            warranty_start_date   (interval '1 year' *  warranty_period)
        ) STORED,
    is_beyond_ul            boolean
        GENERATED ALWAYS AS (
            -- condition
        ) STORED,
    FOREIGN KEY (class_code, major_code, minor_code)
        REFERENCES equipment_type (class_code, major_code, minor_code)
);

Sample data:

INSERT INTO equipment_type (class_code, major_code, minor_code, estimated_useful_life)
    VALUES
        ('CE', '01', '01', 10),
        ('CE', '02', '01', 10);

INSERT INTO PPE (property_number, class_code, major_code, minor_code, date_acquired, warranty_period, warranty_start_date)
    VALUES
        ('10-0518IT39020042', 'CE', '01', '01', '2014-12-01', 1, '2014-12-01'),
        ('10-0518IT39020034', 'CE', '02', '01', '2015-03-15', 3, '2015-03-18');

I want to generate the value for is_beyond_UL column, where it will be true if CURRENT_DATE - date_acquired > equipment_type.estimated_useful_life, and false otherwise. How do i do this?

CodePudding user response：

A generated column can only access column values from other columns in the same row of the table.

You can't have a generated column the way you want because it not only uses information from a completely different table, but it also depends on a function that changes its value every day. So the generated column would change its value without any changes to the table at all - which is also not supported.

If you want to avoid writing that expression, then create a view that joins the two tables and calculates that expression.

select ... other columns ....,
       CURRENT_DATE - p.date_acquired > et.estimated_useful_life as is_beyond_ul            
from property p
   join equipment_type et on ...;

CodePudding user response：

Generated column has its limits:

The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.

Besides that, your calculation “CURRENT_DATE - date_acquired > equipment_type.estimated_useful_life” has CURRENT_DATE that changes over time, which means the value can’t be persisted.

PostgreSQL currently implements only stored generated columns.

What you need is something that is computed at query time.