Avoid duplicate data PostgreSQL

Question

How do i insert these artist into database only if does not exist in database. I have written following code. But it is wrong. Will you please help me ? thanks

Artist List

Lata Mangeshkar  
Lata Mangeshkar
Lata Mangeshkar
Asha Bhosle
Lata Mangeshkar
Mahendra Kapoor  
Lata Mangeshkar
Kishore Kumar
Lata Mangeshkar

My code

for l in artist:
        l = l.strip()
        cursor.execute("SELECT id FROM song_artist WHERE name = %s ;" ,(l,) ) # Id exist?
        artist_rows = cursor.fetchone()
        if not artist_rows:
            artist_slug = self.makeSlug(artist[0])
            cursor.execute( 'INSERT INTO song_artist (name,slug) VALUES (%s,%s) RETURNING id;',(artist[0],artist_slug))
            print k

Output :

INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Asha Bhosle,asha-bhosle) RETURNING i
d;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Mahendra Kapoor,mahendra-kapoor) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Mahendra Kapoor,mahendra-kapoor) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;

OUTPUT SHOULD BE:

INSERT INTO song_artist (name,slug) VALUES (Asha Bhosle,asha-bhosle) RETURNING i
d;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Mahendra Kapoor,mahendra-kapoor) RET
URNING id;

UPDATE:

I have already Added constraint on the column name to enforce the column is unique.

Szymon Lipiński · Accepted Answer · 2011-11-22 14:19:56Z

2

Enforce that on the database side. Add constraint on the column name to enforce the column is unique. This should be enough:

ALTER TABLE song_artist ADD UNIQUE (name);

This way you can be sure that whatever you do, you won't have any duplicated values.
You could insert the data using queries like this:

INSERT INTO song_artist (name, slug)
SELECT 'Mahendra Kapoor','mahendra-kapoor'
WHERE NOT EXISTS
(
SELECT 42
FROM song_artist
WHERE name = 'Mahendra Kapoor'
);

answered Nov 22, 2011 at 14:19

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

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

3 Comments

user514310 Over a year ago

It is complicated. what is 42?

user514310 Over a year ago

I tried your code. But not working. Getting error message.

psycopg2.IntegrityError: duplicate key value violates unique constraint "song_ar tist_slug_key" DETAIL:  Key (slug)=(lata-mangeshkar) already exists.

Szymon Lipiński Over a year ago

That's because you already have duplicated data. Delete the duplicates and try again.

no_freedom · Accepted Answer · 2011-11-23 07:36:20Z

0

You have to use if not artist_rows: That's all

    artist_slug = self.makeSlug(l)

cursor.execute("SELECT count(id) FROM song_artist WHERE name = %s and slug = %s ;" ,(l,artist_slug) ) # Id exist?
artist_rows = cursor.fetchone()
#artist_rows = artist_rows[0]
db.commit()
if not artist_rows:
cursor.execute( 'INSERT INTO song_artist (name,slug) VALUES (%s,%s) RETURNING id;',(l,artist_slug))
#k=( 'INSERT INTO song_artist (name,slug) SELECT %s,%s WHERE NOT EXISTS ( SELECT 42 FROM song_artist WHERE name = %s) RETURNING id;'%(l,artist_slug,l))
artist_id = cursor.fetchone()[0] # get mysql_insert_id using RETURNING
db.commit()

answered Nov 23, 2011 at 7:36

no_freedom

1,97110 gold badges30 silver badges48 bronze badges

Comments

Shawn Chin · Accepted Answer · 2011-11-22 19:53:23Z

0

You can add the unique constraint as Szymon Guz suggested,

ALTER TABLE song_artist ADD UNIQUE (name);

Then use a try / except to handle the duplicate error:

try:
  cursor.execute( 'INSERT INTO song_artist (name,slug) VALUES (%s,%s) RETURNING id;',(artist[0],artist_slug))

except psycopg2.IntegrityError:
  debug.print 'Duplicate values found.  Insert was not successful'

edited Nov 22, 2011 at 19:53

Shawn Chin

87.4k20 gold badges168 silver badges193 bronze badges

answered Nov 22, 2011 at 19:21

vasek1

14.2k12 gold badges35 silver badges36 bronze badges

Collectives™ on Stack Overflow

Avoid duplicate data PostgreSQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related