column check constraint typeof(col) = 'integer' fails if col has declared type text
(1) By anonymous on 2020-07-23 10:50:04 [link] [source]
Hi, after update from 3.29.0 3.32.3 I found the following change in behaviour:
create table keyvalue ( k text not null primary key, v text check (k != 'Something' or typeof(v) = 'integer') );
insert into keyvalue(k, v) values ('Something', 10);
succeeds but in 3.23.3 it fails with
"CHECK constraint failed: keyvalue"
If one removes the "text" type declaration in v:
create table keyvalue ( k text not null primary key, v check (k != 'Something' or typeof(v) = 'integer') );
the insert statement succeeds in 3.32.3, too.
(2) By David Raymond (dvdraymond) on 2020-07-23 11:49:56 in reply to 1 [source]
So in both versions what's happening is that you're giving it the integer, and it's storing it as the text string '10' Type Affinity
In 3.32.0 there was this change Release History
INSERT and UPDATE statements now always apply column affinity before computing CHECK constraints. This bug fix could, in theory, cause problems for legacy databases with unorthodox CHECK constraints the require the input type for an INSERT is different from the declared column type. See ticket 86ba67afafded936 for more information.
Basically because it was always storing it as text, it meant that the check constraint passed as you were inserting it, but then in the resulting file that same record was in violation of that constraint.
(3) By anonymous on 2020-07-23 15:17:01 in reply to 2 [link] [source]
Thank you very much!