Duplicated id error, key is actually NOT duplicated

by codeninja   Last Updated November 10, 2017 18:06 PM

I have the following query:

WITH data AS (
    SELECT
        profileid as id_user,
        cast(terms_accepted as boolean) as terms_accepted_passport,
        lastname as last_name_user,
        firstname as first_name_user,
        picture_serving_url as picture_user,
        is_active as status_user,
        is_passport_active as status_passport,
        language as language_id_user,
        to_timestamp(created, 'YYYY-MM-DD HH24:MI:SS') as created_user,
        to_timestamp(modified, 'YYYY-MM-DD HH24:MI:SS') as modified_user,
        passport_completion_level as completion_level_passport,
        email as email_user,
        about_me as description_user,
        uni_code as institution_id_user,
        to_json(metadata) as metadata_misc
    FROM import_temp_table
)
INSERT INTO passport (
    id,
    public,
    status,
    completion_level,
    user_id,
    terms_accepted
)
SELECT
    (SELECT MAX(id) + 1 FROM passport),
    TRUE,
    CASE status_passport
        WHEN LOWER('true') THEN 1
        WHEN LOWER('false') THEN 2
    END AS status_passport,
    completion_level_passport,
    id_user,
    terms_accepted_passport
FROM data

So I was trying this query and it throws the following error:

ERROR: duplicate key value violates unique constraint "passport_pkey" DETAIL: Key (id)=(108) already exists.

Problem is, id 108 DOESN'T exist. When you perform a single insert with the hardcoded id as 108, it performs it, however, when you try and loop it like this here, it doesn't. Any idea how can I accomplish this?

EDIT

Table definition for Passport is here:

CREATE TABLE public.passport
(
    id integer NOT NULL DEFAULT nextval('passport_id_seq'::regclass),
    public boolean,
    status integer,
    completion_level character varying COLLATE pg_catalog."default",
    user_id character varying COLLATE pg_catalog."default",
    application_id integer,
    terms_accepted boolean,
    CONSTRAINT passport_pkey PRIMARY KEY (id),
    CONSTRAINT passport_application_id_fkey FOREIGN KEY (application_id)
        REFERENCES public.application (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT passport_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)


Answers 1


You're trying to insert the same id twice.

test=# CREATE TABLE f ( x int UNIQUE );
CREATE TABLE
test=# INSERT INTO f(x) VALUES (5),(5);
ERROR:  duplicate key value violates unique constraint "f_x_key"
DETAIL:  Key (x)=(5) already exists.

Given the context with the query, that means simply that profileid in import_temp_table has a duplicate value, and isn't unique.

SELECT *
FROM import_temp_table
WHERE profileid = 108;

Remove all but one row.

Evan Carroll QWER HJKL
Evan Carroll QWER HJKL
November 10, 2017 17:42 PM

Related Questions


PostgreSQL UPSERT issue with NULL values

Updated October 05, 2016 09:02 AM

Uniqueness constraint in union of two columns' values

Updated September 09, 2016 09:02 AM