skip to Main Content

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


  1. PostGreSQL does not support INSERT/UPDATE/DELETE in views that are constituted of many table. This include JOIN and subqueries.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search