Skip site navigation (1) Skip section navigation (2)

Re: INSERT/UPDATE in views fail silently

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: hans_u(at)myself(dot)com
Cc: postgres <pgsql-general(at)postgresql(dot)org>
Subject: Re: INSERT/UPDATE in views fail silently
Date: 2000-05-26 22:00:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Johann Uhrmann <juhrman(at)fh-landshut(dot)de> writes:
> on my linux-systems a write access to views fails without any
> error message:

> hans=> create table demo (i integer);
> hans=> create view demoview as select * from demo;
> hans=> insert into demoview values (5);
> INSERT 151142 1
> hans=> select * from demo;
> i
> -
> (0 rows)

Yeah, a lot of people are surprised when they first try something like

> Does Postgres not support insert/update on views?

It does, but you have to write the appropriate rule to explain what
you want to happen.

> Why do I get a 'INSERT' answer from postgres when no data
> is inserted?

Actually, the data *is* inserted, but it goes into the view's underlying
table where you'll never be able to see it again.  A view in Postgres
is basically just a table with an ON SELECT DO INSTEAD rule.  (The
underlying table is normally empty, it's just there as a placeholder
for the view's catalog information.)

If you want insert/update/delete to behave usefully, you need to write
rules for ON INSERT DO INSTEAD and so forth to show what should happen.
We don't try to guess what you think they should do.

There has been some talk of rejecting insert/update/delete on a table
that has ON SELECT DO INSTEAD but no rule for the other cases.  Hasn't
been changed yet though.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Herbert LiechtiDate: 2000-05-26 22:09:09
Subject: Re: Performance issue 6.5 versus 7.0
Previous:From: Tom LaneDate: 2000-05-26 21:54:44
Subject: Re: Update Performance from 6.5.0 to 6.5.3 to 7.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group