在postgresql 表中建了一个check约束使用了like判断： check (name like '[0-9]'); 结果insert into 表名 values('0'); 执行报错:违反了检醒约束

我来答

Jiang

2021-12-30

postgresql

在sql server中就没有这种问题，postgresql里是不能这么使用吗？

我来答

添加附件

问题补充

1条回答

默认

最新

严少安

可以使用similar to方法替换like

(postgres@[local]) [sbtest] 14:01:15# create table t_like (id int, name varchar(20) check (name like '[0-9]'));
CREATE TABLE
(postgres@[local]) [sbtest] 14:03:03# insert into t_like values (1,'1');
ERROR:  new row for relation "t_like" violates check constraint "t_like_name_check"
DETAIL:  Failing row contains (1, 1).

(postgres@[local]) [sbtest] 14:12:36# create table t_like2 (id int, name varchar(20) check (id like '[0-9]'));
ERROR:  operator does not exist: integer ~~ unknown

(postgres@[local]) [sbtest] 14:36:59# create table t_like3 (id int, name varchar(20) check (name similar to '[0-9]'));
CREATE TABLE
(postgres@[local]) [sbtest] 14:37:18# insert into t_like3 select 1,'0';
INSERT 0 1
(postgres@[local]) [sbtest] 14:37:28# insert into t_like3 select 1,'9';
INSERT 0 1
(postgres@[local]) [sbtest] 14:37:33# insert into t_like3 select 1,'10';
ERROR:  new row for relation "t_like3" violates check constraint "t_like3_name_check"
DETAIL:  Failing row contains (1, 10).

复制