ERROR: duplicate key value violates unique text constraint

Question

i have a table 'A' that looks like this:

 key |   name
-----+-----------------------
 aa  | AName
 bb  | BName
 cc  | CName

another table 'B' that looks like this

P_name | p_key | key
-----+-------+-----
dd   | 1801  | aa
ee   | 1108  | aa

this is the full function

 CREATE OR REPLACE FUNCTION add_B( p_name text, p_key varchar(9), key char(2), name text )
 RETURNS boolean AS $$
 DECLARE
    did_insert boolean := false;
    found_count integer;
    the_key text;
    the_name text;
 BEGIN

SELECT a.key INTO the_key
FROM A a
WHERE a.key = the_key
LIMIT 1;

IF the_key IS NULL THEN
        INSERT INTO A (key, name)
        VALUES (key, name)
        RETURNING add_B.key INTO the_key;
        did_insert := true;
 END IF;

RAISE NOTICE 'A is found %', the_key;
    INSERT INTO B (p_name, p_key, key)
    VALUES (p_name, p_key, key);

    RETURN did_insert;
END;
$$ LANGUAGE plpgsql;

problem is that whenever i run

SELECT add_key('newname','p_key','aa','AName');

it returns

ERROR: duplicate key value violates unique constraint "A_pkey"

How do I go about this error?

Include the code that populates the_key. Which version of Postgres are you using? — Kamil Gosciminski
Is this code part from working function? you missed table name here: INSERT INTO (key, name) — Oto Shavadze

Kamil Gosciminski Kamil Gosciminski · Accepted Answer · 2018-09-21T19:07:16

the_key is null, so you probably mean some other variable in WHERE clause for below SELECT:

SELECT a.key INTO the_key
FROM A a
WHERE a.key = the_key
LIMIT 1;

You should probably rephrase it to:

SELECT A.key INTO the_key
FROM A
WHERE A.key = key -- note the change of variable here
LIMIT 1;

Then, your INSERT statement should also include your table, so add it

INSERT INTO A (key, name) ...

You can either specify where the value is coming from with an alias like add_B.name like you did, or simply use variable behaviour setting for this function like so:

CREATE FUNCTION ...
#variable_conflict use_variable -- add this
DECLARE
...

ERROR: duplicate key value violates unique text constraint

1 Answers