错误文本中的列名:值对于类型字符太长

Question

我们有一个有2列(都有相同的类型和大小)和2个约束的表:

create table colors
(
color varchar(6)
constraint color_check check 
((color)::text ~ '^[0-9a-fA-F]{6}$'::text),
color_secodandry varchar(6)
constraint color_secondary_check check 
((color_secodandry)::text ~ '^[0-9a-fA-F]{6}$'::text),
);

在插入长值的情况下:

insert into colors (color, color_secondary) values ('ccaabb', 'TOO_LONG_TEXT');
insert into colors (color, color_secondary) values ('TOO_LONG_TEXT', 'ccaabb');

对于两种错误情况，我们将得到相同的错误:

ERROR: value too long for type character varying(6) (SQLSTATE 22001)

PostgreSQL在插入之前验证这些列的长度，所以我们的检查永远不会运行。是否有一种方法可以了解哪个列具有无效数据?

Answer 1

您遇到的问题是预期值的求值顺序。你告诉Postgres不允许长度超过6 (character varying(6))，你还指定了这些值必须满足的附加条件。发生的事情是Postgres验证长度标准，并在值失败时抛出异常，在这种情况下，检查约束不执行，因为Postgres在exit on first failure上工作。只有在长度通过后才处理检查约束。例子:

create table test1( id integer generated always as identity 
, color6   character varying (6)
constraint color6_check check (color6 ~ '^[0-9a-fA-F]{6}$') 
, color60  character varying (60)
constraint color60_check check (color60 ~ '^[0-9a-fA-F]{6}$')                  
) ;

insert into test1( color6 ) values ('aabbccdd') ;  
/* Result
SQL Error [22001]: ERROR: value too long for type character varying(6)
ERROR: value too long for type character varying(6)
*/
insert into test1( color60 ) values ('aabbccdd') ; 
/* Result
SQL Error [23514]: ERROR: new row for relation "test1" violates check constraint "color60_check"
Detail: Failing row contains (3, null, aabbccdd).
ERROR: new row for relation "test1" violates check constraint "color60_check"
*/

注意，它们之间的唯一区别是插入的列的长度规范。然而，他们失败了，但原因不同。由于长度规范和检查约束都强制执行长度，因此您现在需要决定如何处理这两个条件:是每个条件单独出现错误，还是两个条件都出现一个错误。(IMHO:分开消息)