Ignore duplicate rows in PostgreSQL

Question

I'm using python and psycopg2 to insert a dict to my PostgreSQL database.

I have postgres version 13.2

At the moment I only have the code below to execute :

 INSERT INTO movie_data(title, description, rating, published, cast_and_crew, age_group, country)
 VALUES ('movie name', 'something', 8, 2020, 'an actor', 'pg-13', 'GB')

But because I already have this row in my db I get the 'already exists' error.

I've tried the ON CONFLICT DO NOTHING but I get the error below :

there is no unique or exclusion constraint matching the ON CONFLICT specification

What's the best way to ignore the duplicate rows and keep on inserting?

I'm new to SQL so if anyone could explain and include the whole code for this purpose I would really appreciate it.

UPDATE : below is my CREATE TABLE statement

CREATE TABLE public.movie_data
(
    title text COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    published integer,
    cast_and_crew text COLLATE pg_catalog."default",
    age_group text COLLATE pg_catalog."default",
    country text COLLATE pg_catalog."default",
    rating integer,
    CONSTRAINT movie_data_pkey PRIMARY KEY (title, description)
)

TABLESPACE pg_default;

ALTER TABLE public.movie_data
    OWNER to postgres;

Please show the CREATE TABLE statement with all constraints. Don't you have a primary key? — Laurenz Albe
– Laurenz Albe, Commented May 14, 2021 at 14:09
I already have the table, I only want to insert the data into it. — Aryan Hab
– Aryan Hab, Commented May 14, 2021 at 14:10
What is is your complete INSERT statement with ON CONFLICT clause? Update your question with this information. — Adrian Klaver
– Adrian Klaver, Commented May 14, 2021 at 14:23

eshirvana · Accepted Answer · 2021-05-14 15:40:38Z

3

error is clear , you need to have a unique constraint on your table for the columns that needs to be unique

alter table movie_data
add constraint constraintname unique (title, description);

now you can use this constraint :

 INSERT INTO movie_data(title, description, rating, published, cast_and_crew, age_group, country)
 VALUES ('movie name', 'something', 8, 2020, 'an actor', 'pg-13', 'GB')
 on conflict on constraint constraintname do nothing;

as Adrian pointed out , instead of that , if your Primary key is on those column that needs to be unique , you simply could :

 INSERT INTO movie_data(title, description, rating, published, cast_and_crew, age_group, country)
 VALUES ('movie name', 'something', 8, 2020, 'an actor', 'pg-13', 'GB')
 on conflict (title, description) do nothing;

edited May 14, 2021 at 15:40

answered May 14, 2021 at 14:16

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

2 Comments

Adrian Klaver Over a year ago

Since you already have a PRIMARY KEY on (title, description) you could skip this and just do ON CONFLICT (title, description) DO NOTHING.

Aryan Hab Over a year ago

@eshirvana Thanks a lot man, it's working now :)

Jesus · Accepted Answer · 2021-05-14 14:27:55Z

0

Why would you like to have a primary key with duplicated rows? The main purpose of primary keys is to uniqueliy identify rows.

I think you should drop your primary key:

alter table public.movie_data drop constraint movie_data_pkey;

answered May 14, 2021 at 14:27

Jesus

331 silver badge4 bronze badges

4 Comments

Adrian Klaver Over a year ago

You seem to be misunderstanding what @AryanHab is trying to achieve. That is to not have duplicate rows by using ON CONFLICT DO NOTHING to skip the INSERT of any row that duplicates an existing row. This is more commonly known as an UPSERT, though that more accurately describes the other option: ON CONFLICT DO UPDATE. Dropping the PK is not the answer.

Jesus Over a year ago

I don't think that is what he meant. "Keep on inserting" he wrote, that doesn't mean UPDATE.

Adrian Klaver Over a year ago

The whole quote is: "What's the best way to ignore the duplicate rows and keep on inserting?" So @AryanHab is choosing to ignore any INSERT that is a duplicate row by using DO NOTHING and then proceeding with INSERTs that are not duplicate rows. That is why it is not a true UPSERT as I mentioned.

Jesus Over a year ago

Well, UPSERT is the most accurate reasoning.

Collectives™ on Stack Overflow

Ignore duplicate rows in PostgreSQL

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related