Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I use this query everything is fine:

UPDATE users 
   SET f_name='Mike', 
       l_name='MyLastName', 
       username='blabla', 
       "password"='asdfsdaf', 
       "class"=12, 
       lang='en' 
 WHERE id=50;

I created a view which combines two tables:

CREATE OR REPLACE VIEW students_data AS 
SELECT students.id, 
       users.f_name, 
       users.l_name, 
       users.username, 
       users."password", 
       users."class", 
       users.permission, 
       users.sessionid, 
       users.lastlogin, 
       users.lang
  FROM students
  LEFT JOIN users ON students.id = users.id;

And I also created this rule:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD  
       UPDATE users 
          SET f_name = new.f_name, 
              l_name = new.l_name, 
              username = new.username, 
              "password" = new."password", 
              "class" = new."class", 
              permission = new.permission, 
              sessionid = new.sessionid, 
              lastlogin = new.lastlogin, 
              lang = new.lang;

When I execute this query I get an error:

UPDATE students_data 
   SET f_name='Mikaa', 
       l_name='MikeL', 
       username='blabla', 
       "password"='asdfsdaf', 
       "class"=12, 
       lang='en' 
 WHERE id=50;

ERROR: duplicate key violates unique constraint "username" SQL status:23505

I have no idea why I get this error, username is a unique column but I shouldn't give any problems when I update this column.

-- Table: users

-- DROP TABLE users;

CREATE TABLE users
(
  id serial NOT NULL,
  f_name character varying,
  l_name character varying,
  username character varying NOT NULL,
  "password" character varying NOT NULL,
  "class" integer NOT NULL,
  permission integer NOT NULL,
  sessionid character varying,
  lastlogin integer,
  lang character varying(5),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT "class" FOREIGN KEY ("class")
      REFERENCES "class" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT username UNIQUE (username)
)
WITHOUT OIDS;
share|improve this question
    
and can you show the UPDATE code that you are using on students_data? –  Lamak Sep 12 '13 at 17:44
    
is the column id of the students table unique? –  kasia.b Sep 12 '13 at 18:05
    
id of students is unique, the update code on students_data is already present –  ObAt Sep 12 '13 at 18:24
1  
Are the students and users table in 1:1 relationship? Or, how many rows are there for students.id = 50? –  dezso Sep 12 '13 at 20:11
    
Postgres 9.3 (released this week) introduces automatically updatable views, but only for views with a single table in the FROM list. –  Erwin Brandstetter Sep 12 '13 at 21:50
show 1 more comment

1 Answer

up vote 6 down vote accepted

Your rule does not have a WHERE clause and any update is trying to modify the whole users table.

You can/should add WHERE id = old.id:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD  
UPDATE users 
SET f_name = new.f_name, 
    l_name = new.l_name,
    --- ...
WHERE id = old.id ;               --- added

Note: I had never used the RULE system before in Postgres. But the above suggestion worked when tested at SQL-Fiddle.

share|improve this answer
    
Nice catch with the missing WHERE clause. –  Erwin Brandstetter Sep 12 '13 at 21:29
    
Thanks! It's pretty stupid that I forget the WHERE clause –  ObAt Sep 13 '13 at 6:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.