PostgreSQL: ERROR: duplicate key value violates unique constraint

Question

I have a table with the below structure:

CREATE TABLE admin.file_status
(
    feed_id bigint,
    run_id text COLLATE pg_catalog."default",
    extracted_date text COLLATE pg_catalog."default",
    file_name text COLLATE pg_catalog."default",
    start_time timestamp without time zone,
    end_time timestamp without time zone,
    file_size bigint,
    job_type text COLLATE pg_catalog."default",
    status text COLLATE pg_catalog."default",
    crypt_flag character(1) COLLATE pg_catalog."default",
    destination_path text COLLATE pg_catalog."default",
    header_file character(1) COLLATE pg_catalog."default",
    merge_status text COLLATE pg_catalog."default",
    compression_status text COLLATE pg_catalog."default",
    file_split_status text COLLATE pg_catalog."default",
    data_transfer_status text COLLATE pg_catalog."default",
    CONSTRAINT file_status_ukeyUNIQUE (run_id, file_name)
);

When I am running the below command, it executes successfully at first attempt, but I do not see any new data inserted into the table.

env 'PGOPTIONS=-c search_path=admin -c client_min_messages=error' psql -h hostname -U user -p 25011 -d xplatform -c "\copy admin.file_status(feed_id,run_id,extracted_date,file_name,start_time,file_size,file_split_status) FROM '../temp/213/split_file_list.csv' delimiter ',' csv;commit;"

When I run it the second time, I get the below error:

ERROR:  duplicate key value violates unique constraint "file_status_ukey"
DETAIL:  Key (run_id, file_name)=(1622722357003791, '20210420125933_NOTIFICATION_1_1.txt') already exists.
CONTEXT:  COPY juniper_extd_file_status, line 1

Why do I not see any data in the table?

What SQL query do you do to see the data in the table and what is the output ? — greg
– greg, Commented Jun 3, 2021 at 12:53
@greg I am running this command in PGadmin: SELECT * FROM admin.file_status where file_name='20210420125933_NOTIFICATION_1_1.txt' and I see no output. — djgcp
– djgcp, Commented Jun 3, 2021 at 13:04
same db? try refreshing the connection, just in case some caching occured — JGH
– JGH, Commented Jun 3, 2021 at 13:39

jjanes · Accepted Answer · 2021-06-03 14:42:36Z

2

PostgreSQL does not put quotes around the values in that error message. So the quote marks you see are part of the literal value being inserted.

So to see the existing row which conflicts, you would need to do:

SELECT * FROM admin.file_status where file_name='''20210420125933_NOTIFICATION_1_1.txt'''

or

SELECT * FROM admin.file_status where file_name=$$'20210420125933_NOTIFICATION_1_1.txt'$$

or similar.

answered Jun 3, 2021 at 14:42

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SQLpro · Accepted Answer · 2021-06-03 13:21:14Z

-1

This is due to the use of "text" type in an index. Uing a SQL type without any limitation like text is higly stupid despite the fact that even the PG stuff recommand it. This conducst to some catastrophic situation, that you are facing !

Never use a "text" type. Use a CHAR or VARCHAR with the dimension (as an example VARCHAR(256)).

Indexing has some limitation like the number of bytes... (above 2700), but the constraint error will overwrite the real cause of the problem because of the higly stupid auto truncation...

Reads : https://blog.josephscott.org/2005/08/05/postgresql-index-limitation-index-row-size-xxxxx-exceeds-btree-maximum-2713/

edited Jun 3, 2021 at 13:21

answered Jun 3, 2021 at 13:07

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

2 Comments

JGH Over a year ago

the reported error keys contain 51 chars only, so rather far away from the limit you mention

SQLpro Over a year ago

you use a text type for run_id, but you mismatched the data to insert as an interger...

Collectives™ on Stack Overflow

PostgreSQL: ERROR: duplicate key value violates unique constraint

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related