创建表时计算布尔值列 [英] Computed boolean column while creating table
问题描述
我正在尝试使用计算的布尔值创建表
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)
);
但是遇到此错误
ERROR: syntax error at or near "AS"
LINE 7: IsCheap BOOL AS (CASE WHEN Price <= 1000 THEN True ELSE Fal...
我也尝试使用BIT代替BOOL-同样的错误。不知道为什么它不起作用。
Also I've tried with BIT instead BOOL - same error. Have no idea why it doesn't work.
推荐答案
如果要使用计算列,则需要Postgres 12(于2019-10-03发布)。
You need Postgres 12 (released 2019-10-03) if you want to use computed columns.
和 as
本身是不够的,语法要求使用始终以... 生成的 手册中所述:
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)
);
如果您未使用Postgres 12,则我建议创建一个仅包含 price< = 1000 as_cheap
的视图,因为该属性的计算确实很便宜,因此不需要存储它。
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.
这篇关于创建表时计算布尔值列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!