public inbox for [email protected]  

help / color / mirror / Atom feed

How to UPSERT with optional updates?
2+ messages / 2 participants
[nested] [flat]

* How to UPSERT with optional updates?
@ 2021-09-21 13:43 Utku <[email protected]>
  2021-09-21 17:02 ` Re: How to UPSERT with optional updates? Peter Geoghegan <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Utku @ 2021-09-21 13:43 UTC (permalink / raw)
  To: [email protected]

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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: How to UPSERT with optional updates?
  2021-09-21 13:43 How to UPSERT with optional updates? Utku <[email protected]>
@ 2021-09-21 17:02 ` Peter Geoghegan <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Geoghegan @ 2021-09-21 17:02 UTC (permalink / raw)
  To: Utku <[email protected]>; +Cc: [email protected]

On Tue, Sep 21, 2021 at 9:58 AM Utku <[email protected]> wrote:
> 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.

The DO UPDATE portion of an upsert statement will accept a WHERE
clause that works in much the same way as a WHERE clause from a
regular UPDATE. You can decide whether you really want to update the
row, based on the row's actual contents, as well as the contents of
your EXCLUDED.* pseudo-row.

-- 
Peter Geoghegan





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2021-09-21 17:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-21 13:43 How to UPSERT with optional updates? Utku <[email protected]>
2021-09-21 17:02 ` Peter Geoghegan <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox