I try to create updatable views with check option
.
When I have joins in the filtering criteria, I use a where in
clause to simplify the queries.
However if I declare the view with both check option
and where in
, data validation is not reliable. I don't see this restriction in the doc: https://www.postgresql.org/docs/current/sql-createview.html
It looks like a bug.
create table tableA ( db_record_id serial NOT null PRIMARY KEY, "name" varchar(60) NOT null UNIQUE
);
create or replace view viewSimpleA
as select * from tableA where name like 'a%'
with check option;
create or replace view viewA
as select * from tableA
where db_record_id in ( select db_record_id from tableA where name like 'a%'
) with check option;
insert into viewSimpleA(name) values('abc'); -- OK
delete from tableA;
insert into viewA(name) values('abc'); -- KO, SQL Error [44000]: ERROR: new row violates check option for view "viewa"
delete from tableA;
insert into viewSimpleA
(name) values('abc');
update viewSimpleA
set name = 'fine'; -- OK: update is prevented by check option
delete from tableA;
insert into tableA
(name) values('abc');
update viewA
set name = 'fine'; -- KO: update is executed, view is now empty
delete from tableA;
select version(); -- PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit