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, Oct 13 '15 at 16:37
_I guess that..._ I guess that you are inserting an already existent key. — Clodoaldo Neto, Oct 13 '15 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, Oct 13 '15 at 17:54

score 5 · Accepted Answer · edited May 23 '17 at 12:26

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.

score 2 · Answer 2 · answered Aug 14 '17 at 23:46

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)

Inserting unique values in PostgreSQL

2 Answers2

Linked