From: | Utku <ugultopu(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | How to UPSERT with optional updates? |
Date: | 2021-09-21 13:43:21 |
Message-ID: | D864459D-0107-4A95-8928-D78C758C8AF8@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2021-09-21 17:02:54 | Re: How to UPSERT with optional updates? |
Previous Message | Laurenz Albe | 2021-09-12 18:14:36 | Re: my server return OK with ALL, but QUERY.test from HOME http://postgis.net return ERROR |