Cannot update view in PostgreSQL?

Question

My site was developed using Drupal 6 running on a Postgresql 8.3 server on Ubuntu 11.10. Also webmin version 1.590.

Now I want to update records in a table, but when I run:

UPDATE uac_institution_view SET status = '2' WHERE nid = '9950'

it gives me an error like:

Failed to execute SQL : SQL UPDATE uac_institution_view SET status = '2' WHERE nid = '9950' failed : ERROR: cannot update a view HINT: You need an unconditional ON UPDATE DO INSTEAD rule.

The problem is that only SELECT queries work. UPDATE, INSERT and DELETE commands are not working; they fail with the above error.

Is this a permisssion problem? A syntax error? Something else?

Answer 1

PostgreSQL views are not updateable by default. You must tell PostgreSQL how you want the view to be updated.

Do this using "an unconditional ON UPDATE DO INSTEAD rule" (as the error message you pasted said) or preferably on PostgreSQL 9.1 and above using a view trigger. I provided links to all that in my answer to your previous post, but here's some more info:

• updateable views in PostgreSQL 9.1 using INSTEAD OF trigger
• updateable views (for Pg 9.0 and below using rules)
• CREATE TRIGGER
• CREATE VIEW
• rules vs triggers
• rules
• triggers in PL/pgSQL

In many cases it's better to leave the view read-only and just update the underlying table. Since you have not provided a definition of the view it's hard to say what that would actually involve. Update your question with the output of running \d uac_institution_view in psql and comment to say you've done so; maybe I can point out a way to run the update directly on the underlying table(s).

You are using a very obsolete version of PostgreSQL (8.3) so you cannot use the preferred INSTEAD OF trigger approach, you must either use rules or update the underlying table directly.

Answer 2

FYI, after the answer involving rules/triggers was posted, PostgreSQL 9.3 came out with auto-updatable views. Version 9.3 is in beta 2 as of June 27, 2013, so it's not yet GA.

Here is an example: https://web.archive.org/web/20160322164044/http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-auto-updatable-views/

Answer 3

I am on postgres 9.5 and views are updatable by default. Example :

CREATE TABLE UP_DATE (id number, name varchar2(29));
insert into up_date values(1, 'Foo');
select * from up_date;
CREATE OR REPLACE VIEW UPDATE
AS
Select 
name from up_date;
select * from update;
insert into update values('Bar');
select * from update;

Will out put Foo and Bar