Unique constraint violation in PostgreSQL Trigger

Question

I have below two tables. I wrote after insert trigger on employees table. If i insert the record in employees table it will insert the record in employee_audits table.

Both table have primary key column (id). suppose if you try to insert record ID value which does not exists in employees table and exists in employee_audits table, it shows the error duplicate key value violates unique constraint "employee_audits_pkey" and also it is not inserting record in employees table. Both transaction failed. But i want to insert the record in employees table.

CREATE TABLE employees(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL
);

CREATE TABLE employee_audits (
   id SERIAL PRIMARY KEY,
   last_name VARCHAR(40) NOT NULL,
   changed_on TIMESTAMP(6) NOT NULL
)

The trigger function:

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS trigger AS
$BODY$
BEGIN
   INSERT INTO employee_audits(last_name,changed_on)
   VALUES(NEW.last_name,now());
   RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

The trigger definition:

CREATE TRIGGER last_name_changes
  AFTER INSERT
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

SELECT * FROM EMPLOYEES

id first_name   last_name
1  "John"        "Doe"

SELECT * FROM EMPLOYEE_AUDITS

ID   last_name  CHANGED_ON
1      "Doe"    "2019-12-27 17:21:13.934"

Manual insert on second table

insert into employee_audits values(2,'banu','2019-12-27 17:21:13.934')

Manual insert on first table

INSERT INTO employees (first_name, last_name)
VALUES ('David', 'Raj');

Error duplicate key value violates unique constraint "employee_audits_pkey"

is it possible to insert record in employees table?

Answer 1

If you have a serial column you should never provide the value for it manually. Manually providing a value for a serial will not advance the sequence behind that column, so the next time you insert without specifying the id column, the next sequence value will be taken which is 2 as the sequence was only advanced once.

So instead of:

insert into employee_audits values(2,'banu','2019-12-27 17:21:13.934')

Just use:

insert into employee_audits (last_name, changed_at)
values ('banu','2019-12-27 17:21:13.934');

This behaviour of the serial columns is one of the reasons why it's highly recommended to use identity columns with modern Postgres versions.