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!

Solution 1

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.

Solution 2

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)