11

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?

Nux
  • 9,276
  • 5
  • 59
  • 72
srinu
  • 259
  • 3
  • 7
  • 14
  • 3
    The first error message is quite clear on what the problem is. – Marc B Oct 31 '12 at 05:08
  • 1
    Did you read my explanatory answer on how to provide enough information, or the advice on the PostgreSQL wiki that it linked to? Where's the `psql` `\d` output? The PostgreSQL version (1.590 is not a PostgreSQL version; use `select version()` if in doubt)? http://stackoverflow.com/a/13151381/398670 How would *you* answer this question if you didn't have access to the DDL for `uac_institution_view`? Also, this is a *different error message* to the one you gave before. What's changed? – Craig Ringer Oct 31 '12 at 05:11
  • See also http://stackoverflow.com/q/11664302/398670 and http://stackoverflow.com/questions/11681852/geoserver-wont-write-to-my-postgresql-updateable-view – Craig Ringer Oct 31 '12 at 05:27

4 Answers4

25

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.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • sorry my poor english and the version is:PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.1-3ubuntu3) 4.4.1 and the output of \d was:Failed to execute SQL : Command Unsupported.Thank your for your reply. – srinu Oct 31 '12 at 05:18
  • 1
    @srinu Your English is better than my grasp of your language. That's fine. You are using an extremely old version of PostgreSQL that you should plan to upgrade as soon as possible. It does not support `INSTEAD OF` triggers so you must use rules to implement update support on the view, or just `UPDATE` the underlying table directly. I cannot show you how to do that because *you still failed to provide the definition of the view*! (Answer updated) – Craig Ringer Oct 31 '12 at 05:20
  • Hi sir, i was newbe in postgresql db how to see the definition of view and how to see the structure of a table. – srinu Oct 31 '12 at 05:31
  • @srinu Either connect with the graphical PgAdmin-III program and examine the view/table there, or (preferably for Stack Overflow) open the `psql` command-line tool, connect to the desired database, and run `\d view_or_table_name` then copy and paste the output. See http://www.postgresql.org/docs/current/interactive/app-psql.html and http://www.postgresql.org/docs/current/interactive/tutorial.html . You might want to consider hiring someone to help you upgrade your application and database to a current version at some point; see http://www.postgresql.org/support/professional_support/ – Craig Ringer Oct 31 '12 at 05:35
  • Hi sir i solved the problem by using left join i updated the records.There is a common field in original table and view,so by using joins i solved the problem. – srinu Nov 06 '12 at 05:22
  • the query is this: $query = "select a.nid,a.status,a.editiontitle,a.uac_edition_details_editiontype,b.title from uac_institution_view as a LEFT JOIN node as b on b.nid = a.nid where a.status='1' order by a.uac_edition_details_editiontype asc, a.editiontitle asc, a.title asc"; – srinu Nov 06 '12 at 05:54
  • 4
    You might want to add that since 9.3 simple views are automatically updateable: http://www.postgresql.org/docs/current/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS –  Oct 22 '15 at 11:26
8

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/

runejuhl
  • 2,147
  • 1
  • 17
  • 17
Jeff French
  • 1,151
  • 1
  • 12
  • 19
6

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

happybuddha
  • 1,271
  • 2
  • 20
  • 40
  • FYI - If you are adding a column using "CREATE OR REPLACE", the new column must be added to the end of the list of selected columns. – Nate Wanner Jan 10 '18 at 15:24
  • The datatypes `NUMBER` and `VARCHAR2` are Oracle specific - it should be `NUMERIC` and `VARCHAR (n)`. You might want to change that? – Vérace May 18 '19 at 17:46
0

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;
CREATE TABLE user_table (
    id serial,
    lastname varchar(100),
    user_type varchar(2) DEFAULT 'nn',
    PRIMARY KEY (id)
);
-- initial data
INSERT INTO user_table(lastname) VALUES('Foo');
SELECT * FROM user_table;

-- simple view (note, no id here)
CREATE OR REPLACE VIEW user_view
AS
    SELECT lastname, user_type 
    FROM user_table
;

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

-- insert into user_table via view
INSERT INTO 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 view
UPDATE 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:

CREATE OR REPLACE VIEW user_view
AS
    SELECT lastname as last_name, user_type
    FROM user_table
    WHERE user_type = 'v'
;
INSERT INTO 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.

Nux
  • 9,276
  • 5
  • 59
  • 72