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 doesn’t seem reliable.
I don’t see this restriction in the doc: https://www.postgresql.org/docs/current/sql-createview.html
Is it a bug? Is there a workaround?
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 table viewA(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
EDIT: I created a bug report here
2
Answers
PostGreSQL does not support INSERT/UPDATE/DELETE in views that are constituted of many table. This include JOIN and subqueries.
A PostgreSQL view is updatable when it meets the following conditions:
-The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view.
-The defining query must not contain one of the following clauses at the top level: GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT.
-The selection list must not contain any window function, any set-returning function, or any aggregate function such as SUM, COUNT, AVG, MIN, and MAX.