Postgres: updatable view (where in) with check option

Question

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

Answer 1

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

Answer 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.