Computed boolean column while creating table

Question

I'm trying to create table with computed boolean values

CREATE TABLE parts
(
    Id SERIAL PRIMARY KEY,
    Weight INTEGER NOT NULL CHECK(Weight > 0),
    Color VARCHAR(10),
    Price INTEGER NOT NULL CHECK(Price > 0),
    IsCheap BOOL AS (CASE WHEN Price <= 1000 THEN True ELSE False END),
    Name VARCHAR(30)
);

But getting this error

ERROR:  syntax error at or near "AS"
LINE 7:  IsCheap BOOL AS (CASE WHEN Price <= 1000 THEN True ELSE Fal...

Also I've tried with BIT instead BOOL - same error. Have no idea why it doesn't work.

score 4 · Accepted Answer · 2019-10-14T10:44:15.267

You need Postgres 12 (released 2019-10-03) if you want to use computed columns.

And as on its own is not enough. The syntax requires to use generated always as ... as documented in the manual:

create table parts
(
  id       serial primary key,
  weight   integer not null check (weight > 0),
  color    varchar(10),
  price    integer not null check (price > 0),
  is_cheap boolean generated always as (price <= 1000) stored,
  name     varchar(30)
);

If you are not using Postgres 12, then I would recommend to create a view that simply includes price <= 1000 as is_cheap as the calculation of that attribute is really cheap and there is no need to store it.

Computed boolean column while creating table

1 Answers1