0

I am using the following two queries (in PostgreSQL/Python):

query_update = """UPDATE my_table
               SET {}='{}'
               WHERE index={}""".format(*[str(updated_columns), str(updated_values), str(updated_row_indices)])

query_insert = """INSERT INTO my_table ({})
                   VALUES ('{}')""".format(*[str(updated_columns), str(updated_values), str(updated_row_indices)])

Instead of these two queries, I want to have only one query that will automatically insert record if it doesn't exits. I have two questions:

  1. How to make these two queries into one query using PostgreSQL? (important)
  2. How to do the same using MySQL? (optional)
BoarGules
  • 13,647
  • 2
  • 23
  • 38
user8436761
  • 187
  • 9

2 Answers2

1

query_update is not needed.

Add ON CONFLICT clause to you query_insert variable.

query_insert = """INSERT INTO my_table ({})
                   VALUES ('{}')
ON CONFLICT ({}) DO
UPDATE
   SET {}""".format(*[str(columns_to_insert), str(column_values), str(key_columns)], str(column_values_update))

Here, columns_to_insert should be a comma separated list of columns you insert data to; column_values should be a comma separated list of values for columns_to_insert; key_columns should be a comma separated list of primary key columns; column_values_update should be a comma separated list of column_name=column_value pairs (similar to regular UPDATE command).

Petro K
  • 148
  • 8
  • Thank you! One question, since I have a Pandas DataFrame, do I put `df.columns` instead of ``? – user8436761 Jan 10 '19 at 15:18
  • @user8436761 Sorry, don't know what df.columns is... Instead of , you should type the comma separated list of my_table primary key columns. – Petro K Jan 10 '19 at 15:43
  • That would be one column called 'index', but when I put it in, I am getting an error: `SET {}='{}'""".format(*[str(updated_columns), str(updated_values), str(updated_row_indices)]) IndexError: tuple index out of range`. Any ideas? – user8436761 Jan 10 '19 at 19:07
  • @user8436761 Ok, I've edited the answer and added some explanations. Attention: I've changed the names of your variables and added some new ones. The number of {} pairs must be equal to number of items you pass in the array for format function. Hope this helps. – Petro K Jan 10 '19 at 19:44
  • I tried multiple versions, but I am still either getting the same tuple index out of range error or `ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression` error. Do you have an example with some column names and real values how would the query look like? – user8436761 Jan 11 '19 at 11:39
  • I have been trying somethign like this in a terminal but I am getting the above subselect or ROW() error: `INSERT INTO my_table (index, col_name_1, col_name_5) VALUES (4,'value for col 1', 'value for col 5') ON CONFLICT (index) DO UPDATE SET (index)=4, (col_name_1)='value for col 1', (col_name_2)=('value for col 5');` – user8436761 Jan 11 '19 at 11:52
  • @user8436761 In your code, everything looks ok except UPDATE clause. Why do you use brackets there? I recommend you to read about UPDATE syntax in SQL. This part should look like this: DO UPDATE SET index=4, col_name_1='value for col 1', col_name_2='value for col 5' – Petro K Jan 11 '19 at 13:55
0

There is something called a merge statement. This will do exactly what you are looking for

https://www.postgresql.org/message-id/attachment/23520/sql-merge.html

Breian Wells
  • 111
  • 3
  • Do you know how to turn the above code into a merge statement? – user8436761 Jan 10 '19 at 14:55
  • My understanding is that merge has issues with concurrency, which means you usually want to lock the table when using it, which is suboptimal for updating a single row. Also merge is primarily designed for updating many rows in a single statement. For OP's purpose, upsert (ON CONFLICT) is more appropriate. – Julian Jan 10 '19 at 15:16