Inserting unique values in PostgreSQL

Question

I am trying to insert the distinct values in my database: My query is like that:

    query2 = "INSERT INTO users (id, name, screenName, createdAt, defaultProfile, defaultProfileImage,\
    description) SELECT DISTINCT %s, %s, %s, %s, %s, %s, %s;"

    cur.execute(query2, (user_id, user_name, user_screenname, user_createdat, \
    default_profile, default_profile_image, description))

However, I still get the error: psycopg2.IntegrityError: duplicate key value violates unique constraint "users_key".

I guess that the query is inserting all data and then selects the distinct values? Another way I guess would be to store all data in a temporary database and then retrieve them for there. But is there an easier way?

Thank you!

@Matteo distinct does not require from although in this case it is superfluous — Clodoaldo Neto
– Clodoaldo Neto, Commented Oct 13, 2015 at 16:37
I guess that... I guess that you are inserting an already existent key. — Clodoaldo Neto
– Clodoaldo Neto, Commented Oct 13, 2015 at 16:39
Of course it does and that's my question actually. Can SQL first DISTINCT values and then INSERT THEM, without making a step in the middle (like making a table, storing all information and then making another table)? — Nasia Ntalla
– Nasia Ntalla, Commented Oct 13, 2015 at 17:54

Community · Accepted Answer · 2017-05-23 12:26:04Z

6

You appear to be trying to do an insert ... if not exists type operation. If you're on PostgreSQL 9.5 you should use INSERT ... ON CONFLICT NO ACTION. See How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? for relevant details.

SELECT DISTINCT, used the way you are using it, will not help you with this. It operates only on the rows input to the SELECT statement. It doesn't know anything about where the output rows go, in your case to a table.

You need to join against the table, or use WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE ...) to make the INSERT INTO ... SELECT ...'s SELECT part return zero rows if the row already exists in the target table.

Note, though, that that's still subject to race conditions. If you have multiple concurrent clients you also need to LOCK the table.

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Oct 14, 2015 at 0:16

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

Ben Wilson · Accepted Answer · 2017-08-14 23:46:13Z

For anyone who is looking for a way to insert unique values from one table into a table where there is no unique key (in other words, only insert rows wherein the full set of columns does not match a set of columns for an existing record in the target table)

e.g.

Table 1

    column1  column2
    -------  -------
 1.  dog     collar

Table 2

    column1  column2
    -------  -------
 1. dog      collar 
 2. dog      leash

Intended effect is to insert column 2 from Table 2, but not column 1 since it matches a row in Table 1.

This worked for me, albeit not very performant:

INSERT INTO table1 (
    column1,
    column2
    ) 

    SELECT 
    column1,
    column2

    FROM table2 

    WHERE ( 
    column1,
    column2
    ) 

    NOT IN 

    (SELECT     
    column1,
    column2
    FROM table1)

Collectives™ on Stack Overflow

Inserting unique values in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related