Update multiple columns on conflict postgres

Question

I have to write a query to update a record if it exists else insert it. I am doing this update/insert into a postgres DB. I have looked into the upsert examples and most of them use maximum of two fields to update. However, I want to update multiple columns. Example:

query="""INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT(col2) DO UPDATE SET (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""

In the query above assume that col2 is a unique key, I am inserting and updating the same number of columns. I have to execute this query using pymysql(python library). In a simple insert statement, I know how to pass the tuple containing the parameters dynamically.

cursor.execute(insert_query, data_tuple)

But in this case, I have both places(insert and update) input to be dynamic. Considering the above upsert query, the way I pass the parameters to the cursor

cursor.execute(upsert_query,data_tuple,data_tuple)

However, this one throws up an error with the number of arguments being in the execute function. So how do I pass? Moreover, I am trying to use this way to pass parameters because using the assignment(=) would be a laborious thing to do for 20 columns.

Is there any other alternative way to do this? Like a simple "replace into" statement in mysql.

score 3 · Accepted Answer · edited May 03 '19 at 08:15

The direct answer to your question is, you do a tuple + tuple to double the tuple.

cursor.execute(upsert_query, data_tuple + data_tuple)

Other Options:

If you have individual values and you are constructing the tuple, you can directly construct a tuple with twice the number of values.

query="""INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT(col2) DO UPDATE SET col1=%s, col3=%s, col4=%s, ..."""

cur.execute(query, (c1, c2, c3, ... c20, c1, c3, c4, ... c20))

You will have to specify the values (except col2) twice.

If you already have the tuple, which is what you originally asked, then you will use a + to merge the same tuple twice.

If you have the individual values and not the tuple, you could also use named parameters like a dictionary.

query="""INSERT INTO table (col1,col2,col3,col4...) VALUES(%(c1)s, %(c2)s, %(c3)s, %(c4)s...) ON CONFLICT(col2) DO UPDATE SET col1=%(c1)s, col3=%(c3)s, col4=%(c4)s, ..."""
cur.execute(query, {'c1': c1val, 'c2': c2val, 'c3': c3val, 'c4': c4val, ...})

This form is good for readability, passes parameters only once, and is easy to maintain (increase in columns, etc) if the number of columns change in the future.

I did think of this. How do I pass the tuple to the cursor using psycopg2 lib? — Eswar, Apr 30 '19 at 18:39
You can simply pass the parameters twice. I will edit the answer to reflect that. — Raze, May 02 '19 at 20:57
@eswar , Updated the answer, including for the case where you already have the tuple. — Raze, May 02 '19 at 21:16

Jaisus · Answer 2 · 2019-05-02T10:39:51.900

EDIT 2

So, after few exchanges : your problem seems to be how to use the cursor.execute function in pymysql. This is the link for the appropriate documentation : https://pymysql.readthedocs.io/en/latest/modules/cursors.html

I never code in python but the documentation seems quite precise on the execute method usage :

execute(query, args=None)

Execute a query
Parameters: 

    query (str) – Query to execute.
    args (tuple, list or dict) – parameters used with query. (optional)

Returns:    

Number of affected rows
Return type:    

int

If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.

So maybe with a 'dict' type it is possible, but I do not think it is the philosophy of it.

Original post

I am quite not sure of what exactly you want to say by 'both places input to be dynamic', so I will put down some SQL here an do not hesitate if you have any question after :)

First a small initialization

CREATE TABLE test 
( 
    id int,
    value_1 varchar,
    value_2 bit
);

ALTER TABLE test
ADD CONSTRAINT ck_test UNIQUE(id, value_1, value_2);


INSERT INTO test
VALUES
    (1, 'test', cast(1 as bit))
    , (2, 'test_2', cast(0 as bit));

Second the error

INSERT INTO test
VALUES
    (1, 'test', cast(1 as bit));

Third the UPSERT

INSERT INTO test
VALUES 
    (1, 'test', cast(1 as bit))
ON CONFLICT ON CONSTRAINT ck_test
DO
    UPDATE 
        SETid = 3, value_1 = 'error';

Is that answering your question? Or is it more a string building problem?

EDIT So, I am not fond of alternative languages so I will put this in plpgsql:

do language plpgsql $$
declare 
    query varchar;
    id_insert int;
    value_1_insert varchar;
    value_2_insert bit;
    id_update int;
    value_1_update varchar;
    value_2_update bit;
begin
    id_insert := 4;
    value_1_insert := 'test';
    value_2_insert := cast(1 as bit);

    id_update := id_insert;
    value_1_update := 'error';
    value_2_update := cast(0 as bit);

    query := 'INSERT INTO test
                VALUES 
                    (
                        cast('||id_insert||' as int)
                        , '''||value_1_insert||'''
                        , cast('||value_2_insert||' as bit)
                    )
                ON CONFLICT ON CONSTRAINT ck_test
                DO
                    UPDATE 
                        SET 
                            id = cast('||id_update||' as int)
                            , value_1 = '''||value_1_update||'''
                            , value_2 =  cast('||value_2_update||' as bit);';
    execute query;
end;
$$;

Hope this helps ;)

As asked in the question I want to insert if a row doesn't exist and update if it exists. The number of parameters/fields to insert and update are the same. I could've done the way told by @Raze. But the question is how do I pass the parameters. — Eswar, Apr 30 '19 at 18:42
I edited my post but I am still not sure of where is your problem. If it is just to pass some parameters to a string, you have multiple way to do it. This one is one of them. — Jaisus, May 02 '19 at 08:14
I have edited the question for more clarity. please have a look — Eswar, May 02 '19 at 10:01
Okay : I think I understand your problem. Your problem is absolutely not an upsert problem but a pymysql problem entirely and how to pass two list of parameters to the cursor.execute function. Is that it ? If this is your problem, please rename your question. And come to think of, I think this not possible : https://pymysql.readthedocs.io/en/latest/modules/cursors.html — Jaisus, May 02 '19 at 10:32
Yeah. you got my question. But I know this way doesn't work, what's the alternative? Btw, it's psycopg2 not pymysql. Because in pymysql, it's easy to go with "replace into" statement. But postgres doesn't support this. That's why I had to use "on conflict" statement. — Eswar, May 02 '19 at 13:25

Update multiple columns on conflict postgres

2 Answers2

Other Options:

Linked