CODEWITHSUNDEEP
sqlsql-insertupsertstored-functionspostgresql-11
Prosenjit
Prosenjit

Reputation: 241

How to resolve duplicate key error without conflict with postgresql 11.2

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.

Upvotes: 0

Views: 346

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions