CODEWITHSUNDEEP
sqlpostgresql
pi3.14
pi3.14

Reputation: 53

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

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?

Upvotes: 0

Views: 567

Answers (2)

JHH
JHH

Reputation: 1499

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.

Upvotes: 1

user330315
user330315

Reputation:

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 ...;

Upvotes: 4

Related Questions