Ι have the following view:
CREATE OR REPLACE VIEW public.my_view as
SELECT
col1,
col11
from
table_a JOIN table_b on table_a.id=table_b.a_id
join
(
select id,col56 from table_c where col56 > 3
) as filtered_c on table_a.id=filtered_c.a_id;
table_a is:
id: SERIAL PK
col1: INTEGER
table_b is:
id: SERIAL PK
col11: INTEGER
a_id FK table_a
table_c is:
id: SERIAL PK
col56 INTEGER
a_id FK table_a
As I saw upon postgresql wiki I can insert values to views that correspond to the appropriate tables. But in my case if I insert:
INSERT INTO my_view VALUES (7,88);
In which table the values will be inserted? As far as I know views are read-only tables.
Best Answer
A view that contains a join is not automatically updatable, that is, you cannot simply insert into such a view. The manual says (emphasis mine):
The only way to insert into a view that does not meet these requirements is to create an
INSTEAD OF INSERT
trigger on it, in which case what valuees go to what tables is determined by the logic implemented in that trigger.