How to resolve duplicate key error without conflict with postgresql 11.2

Question

insertion code

INSERT INTO employee (pid, pname, desig, dept, lts_i, lts_O, p_status) VALUES %s \
                    ON CONFLICT (pid) DO UPDATE SET \
                    (pname, desig, dept, lts_i, lts_O, p_status) = \
                    (EXCLUDED.pname, EXCLUDED.desig, EXCLUDED.dept, EXCLUDED.lts_i, EXCLUDED.lts_O, EXCLUDED.p_status) \
                    RETURNING *

If i insert such like above then it's working good. Instead of CONFLICT i have used a function the following

CREATE FUNCTION employee_db(
  pid1 integer,
  pname1 text,
  desig1 text,
  dept1 text,
  lts_i1 time,
  lts_o1 time,
  p_status1 text
) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
-- note that "a" must be unique
UPDATE employee SET (lts_i, lts_o, p_status) = (lts_i1, lts_o1, p_status1) WHERE pid = pid1;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO employee(pid, pname, desig, dept, lts_i, lts_o, p_status) VALUES (pid1, pname1, desig1, dept1, lts_i1, lts_o1, p_status1);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

that takes some argument

SELECT merge_db(12, 'Newton', 'director', 'd1', '10:00:26', '00:00:00', 'P-Status')"

but when i update lts_i, lts_O and p_status within same id(12)

SELECT merge_db(12, 'Newton', 'director', 'd1', '12:10:22', '02:30:02', 'active')"

then it also showing duplicate key error. I don't want to use here CONFLICT, because of i have a UPDATE RULE on the same Table and already postgresql says that "The event is one of SELECT, INSERT, UPDATE, or DELETE. Note that an INSERT containing an ON CONFLICT clause cannot be used on tables that have either INSERT or UPDATE rules. Consider using an updatable view instead."

Update Rule

CREATE RULE log_employee AS ON UPDATE TO employee
    WHERE NEW.lts_i <> OLD.lts_i or NEW.lts_O <> OLD.lts_O
    DO UPDATE employee set today = current_date where id = new.id;

if lts_i, lts_o or p_status is update then will be insert current_date into "today" field in the same employee table.

But definitely i need RULE, In this situation what should i do?

Any help would be appreciated. Thanks.

What rule do you have? Please edit your question and add the definition of that rule. And why do you think you need it? In almost all cases triggers are much easer to implement and to understand compared to rules. — user330315
– user330315, Commented Mar 27, 2019 at 7:45
Apparently you want to update a date column if something changes. That is done much better in a trigger, rather than a rule — user330315
– user330315, Commented Mar 27, 2019 at 9:50

score 1 · Accepted Answer · 2019-03-27 10:20:15Z

1

You should use a trigger for that.

The trigger function:

create function emp_trigger_func()
  returns trigger
as
$$
begin
   new.today := current_date;
   return new;
end;
$$
language plpgsql;

The condition on when that columns should be update is better done in the trigger definition to avoid unnecessary firing of the trigger

create trigger update_today
  before update on employee
  for each row
  when (NEW.lts_i <> OLD.lts_i or NEW.lts_O <> OLD.lts_O)
  execute procedure emp_trigger_func();

Note that <> doesn't properly deal with NULL values. If lts_i or lts_o can contain null values, then firing condition is better written as:

  when (   NEW.lts_i is distinct from OLD.lts_i 
        or NEW.lts_O is distinct from OLD.lts_O)

This will also catch a change from or to a null value.

edited Mar 27, 2019 at 10:20

answered Mar 27, 2019 at 9:59

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Prosenjit Over a year ago

But i'm getting this error here "psycopg2.InternalError: control reached end of trigger procedure without RETURN"

Prosenjit Over a year ago

I've written such like pastebin.com/V6r28Wrr but getting same error

user330315 Over a year ago

@Prosenjit: sorry, forgot the return

Prosenjit Over a year ago

@ a_horse_with_no_name: Sorry, exactly i didn't understand.

user330315 Over a year ago

@Prosenjit: my trigger code was missing the return new; statement. If fixed that.

|

Collectives™ on Stack Overflow

How to resolve duplicate key error without conflict with postgresql 11.2

insertion code

that takes some argument

but when i update lts_i, lts_O and p_status within same id(12)

Update Rule

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

insertion code

that takes some argument

but when i update lts_i, lts_O and p_status within same id(12)

Update Rule

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related