How to UPSERT with optional updates?
От | Utku |
---|---|
Тема | How to UPSERT with optional updates? |
Дата | |
Msg-id | D864459D-0107-4A95-8928-D78C758C8AF8@gmail.com обсуждение исходный текст |
Ответы |
Re: How to UPSERT with optional updates?
|
Список | pgsql-novice |
I'm trying to write a script for an optional upsert. That is, it is just like a regular insert or update, but in addition,the information of a given column should be updated, or be left as-is, is passed as well. That is: - Insert if does not exist. - If exists, check the parameters to understand if a particular column should be updated, or be left as-is. This is the script that I have so far: INSERT INTO table_name ( "col1", "col2", "col3", "col4", "col5", "col6", "col7" ) SELECT DISTINCT a."col1", a."col2", a."col3", a."col4", a."col5", a."col6", a."col7" FROM UNNEST ( $1::uuid[], $2::uuid[], $3::numeric[], $4::numeric[], $5::boolean[], $6::boolean[], $7::timestamptz[], $8::boolean[], $9::boolean[], $10::boolean[] ) WITH ORDINALITY AS a( "col1", "col2", "col3", "col4", "col5", "col6", "col7", "shouldUpdateCol3", "shouldUpdateCol4", "shouldUpdateCol5", "ordinality" ) ON CONFLICT ("col1", "col2") DO UPDATE SET "col3" = CASE WHEN EXCLUDED."shouldUpdateCol3" = TRUE THEN EXCLUDED."col3" ELSE table_name."col3" END, "col4" = CASE WHEN EXCLUDED."shouldUpdateCol4" = TRUE THEN EXCLUDED."col4" ELSE table_name."col4" END, "col5" = CASE WHEN EXCLUDED."shouldUpdateCol5" = TRUE THEN EXCLUDED."col5" ELSE table_name."col5" END, "col7" = EXCLUDED."col7"; It does not work, because the columns `shouldUpdateCol3`, `shouldUpdateCol4` and `shouldUpdateCol5` are not selected in the`SELECT FROM UNNEST` above. However, if I add them to the `SELECT FROM UNNEST`, then I get `INSERT has more expressions than target columns` error.
В списке pgsql-novice по дате отправления: