Postgresql ERROR: duplicate key value violates unique constraint when trigger insert

Question

I've created a function which is called when a trigger is fired while insert. But every time I try to insert the data in the table I get the duplicate key value violates unique constraint

This is my trigger function:

CREATE OR REPLACE FUNCTION trg_new_rows()
 RETURNS trigger AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM organization WHERE organization.name = 'TV' AND organization.country = 'D') THEN
 --RAISE '1';
 INSERT INTO organization VALUES ('TV1','TV1',NULL,'TV1',NULL,NULL);
ELSE 
 --RAISE '2';
 INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL);
 INSERT INTO organization VALUES ('Tivoli2','Tivoli2 organization',NULL,'GB',NULL,NULL);
END IF;
RETURN NULL;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE;

And this is the trigger

CREATE TRIGGER delaft
 AFTER INSERT
 ON organization
 FOR EACH ROW
 EXECUTE PROCEDURE trg_new_rows();

And then when I try executing this:

INSERT INTO organization VALUES ('TV','Tivoli D organization',NULL,'D',NULL,NULL);

I get this error:

ERROR: duplicate key value violates unique constraint "organization_pkey"
DETAIL: Key (abbreviation)=(Tivoli1) already exists.
CONTEXT: SQL statement "INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)"
PL/pgSQL function trg_new_rows() line 8 at SQL statement
SQL statement "INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)"
PL/pgSQL function trg_new_rows() line 8 at SQL statement
, Time: 0.000000s

Although when I check the organization table I can't find any abbreviation having the value Tivoli1. Any advice please?

This is the create and insert queries for table organization:

CREATE TABLE Organization
(Abbreviation VARCHAR(12) PRIMARY KEY,
 Name VARCHAR(80) NOT NULL,
 City VARCHAR(35) ,
 Country VARCHAR(4) , 
 Province VARCHAR(35) ,
 Established DATE,
 CONSTRAINT OrgNameUnique UNIQUE (Name));
 INSERT INTO organization VALUES ('EFTA','European Free Trade Association','Geneva','CH','Geneve','1960-01-04');
 INSERT INTO organization VALUES ('EIB','European Investment Bank','Luxembourg','L','Luxembourg','1957-03-25');
 INSERT INTO organization VALUES ('CERN','European Organization for Nuclear Research','Geneva','CH','Geneve','1953-07-01');
 INSERT INTO organization VALUES ('ESA','European Space Agency','Paris','F','Ile de France','1973-07-31');
 INSERT INTO organization VALUES ('EU','European Union','Brussels','B','Brabant','1992-02-07');
 INSERT INTO organization VALUES ('FAO','Food and Agriculture Organization','Rome','I','Lazio','1945-10-16');

I'm using navicat for postgresql.

Answer 1

Another possibility, your trigger is executing on insertions to organization. It also inserts into organization. This can be trigger-looping.

You may want to check (RAISE NOTICE/DEBUG) pg_trigger_depth()

• How do I debug trigger recursion in PostgreSQL?

Answer 2

You're calling this

INSERT INTO organization VALUES ('Tivoli11','Tivoli organization',NULL,'F',NULL,NULL)

Which is triggering this.

INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)

Your primary key, organization_pkey, is on abbreviation. Here you have two Tivoli1 abbreviations. That triggers the exceptions which causes the trigger to fail, and the commit to abandon.

I have no idea what you want to do, but perhaps you need a consultant to audit your schema and workflow. It seems like you may be in over your head.

Re: "there is no Tivoli11"

Right,

1. You tried to insert it
2. That triggered a trigger trg_backup_row which you did not show in the question (please do)
3. trg_backup_row attempted another insert.
4. That attempted insert violated the unique constraint of the primary key.
5. That violation threw an exception.
6. That exception caused the transaction and the initial commit to rollback.

Transactions, even AFTER INSERT that throw exceptions cause the insert to rollback.

• Tak: Let us continue this discussion in chat.