Cannot Update View In Postgresql?

August 20, 2024 Post a Comment

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

Solution 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:

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.

Solution 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/

Solution 3:

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

CREATETABLE UP_DATE (id number, name varchar2(29));
insertinto up_date values(1, 'Foo');
select*from up_date;
CREATEOR REPLACE VIEWUPDATEASSelect 
name from up_date;
select*fromupdate;
insertintoupdatevalues('Bar');
select*fromupdate;

Will out put Foo and Bar

Solution 4:

Everything just works from PG 9.3 onwards as noted by Jeff French... With some exceptions (more info below).

Simple example

You can test this code on your PostgreSQL. Use cascade drop when you are done (to drop view with the table).

-- create table--DROP TABLE user_table CASCADE;CREATETABLE user_table (
    id serial,
    lastname varchar(100),
    user_type varchar(2) DEFAULT'nn',
    PRIMARY KEY (id)
);
-- initial dataINSERTINTO user_table(lastname) VALUES('Foo');
SELECT*FROM user_table;

-- simple view (note, no id here)CREATEOR REPLACE VIEW user_view
ASSELECT lastname, user_type 
    FROM user_table
;

-- check view (will have initial data)SELECT*FROM user_view;

-- insert into user_table via viewINSERTINTO user_view VALUES('Bar');
-- check (both will have both records)SELECT*FROM user_view;
SELECT*FROM user_table;
-- you can run above many times-- (id will auto-increment even though it is not in the view)-- update user_table via viewUPDATE user_view SET user_type='v'WHERE lastname ='Bar';
SELECT*FROM user_table;

Limitations

There are some limitations though and that will depend on you PG version.

In PG 9.3 views cannot have any expressions etc. Also only one table is allowed... So more or less a simple select limiting, reordering, or renaming columns.
In PG 9.4 views can be partially updatable. So you can have expression but you will not be able to update them (not out of the box at least).

If you have WHERE in your view then you might get a bit weird results. So this will still work with insert:

CREATEOR REPLACE VIEW user_view
ASSELECT lastname as last_name, user_type
    FROM user_table
    WHERE user_type ='v'
;
INSERTINTO user_view VALUES('Bar');

But update might not work. At least this will not work in that it will update 0 rows:

UPDATE user_view SET user_type='v';

Because effectively that would be equivalent to below query (so makes sense if you think about it):

UPDATE user_view SET user_type='v'WHERE user_type ='v';

I wonder if at some point they might support joins... But at the time of writing PG 14 is out and it doesn't support joined tabled in views (for updates I mean).

Alternatives

You can still use INSTEAD OF triggers, especially for more complicated views. And you can use rules... But (as noted in the CREATE RULE docs) automatically updatable views will be faster then manually created rules.

stiellledm310