postgreMake约束与字符串和整数SQL

Question

我是初学者。我在PostgreSQL上创建了一个查询。我想用字符串和整数做约束。它成功地运行了，但是当我插入数据时，它给了我一个错误

CREATE TABLE customer
(
id_customer char(5) PRIMARY KEY NOT NULL,
CONSTRAINT cek_id_customer CHECK ((left(id_customer,2) in ('CU')) 
and  substring(id_customer,3) LIKE '%[0-9]%'), 
nama_customer varchar(30) NOT NULL,
gender_customer varchar(15) NOT NULL,
CONSTRAINT  cek_gender_customer CHECK(gender_customer = 'Male' OR gender_customer = 'Female')
);
INSERT INTO customer 
VALUES ('CU001', 'Sayaa', 'Male')

的消息错误:新行为关系"客户"违反了检查约束&;cek_id_customer&;细节:失败行包含(CU001, Sayaa, Male)。SQL state: 23514

Answer 1

要执行您想要的匹配模式，您可以在约束中使用正则表达式匹配规则，如id_customer ~ '^CU[0-9]*$'。

例如:

CREATE TABLE customer (
id_customer char(5) PRIMARY KEY NOT NULL,
CONSTRAINT cek_id_customer CHECK (id_customer ~ '^CU[0-9]*$'),
nama_customer varchar(30) NOT NULL,
gender_customer varchar(15) NOT NULL,
CONSTRAINT cek_gender_customer CHECK(
gender_customer = 'Male' OR
gender_customer = 'Female'
)
);
INSERT INTO customer (id_customer, nama_customer, gender_customer)
VALUES ('CU001', 'Sayaa', 'Male'); -- succeeds
INSERT INTO customer (id_customer, nama_customer, gender_customer)
VALUES ('CU1X', 'Sayaa', 'Male'); -- fails!

参见DB Fiddle的运行示例。