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.
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?
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 )
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
import_temp_table has a duplicate value, and isn't unique.
SELECT * FROM import_temp_table WHERE profileid = 108;
Remove all but one row.