From: Utku <ugultopu@gmail.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\))
Subject: How to UPSERT with optional updates?
Message-Id: <D864459D-0107-4A95-8928-D78C758C8AF8@gmail.com>
Date: Tue, 21 Sep 2021 09:43:21 -0400
To: pgsql-novice@lists.postgresql.org
Archived-At: 
 <https://www.postgresql.org/message-id/D864459D-0107-4A95-8928-D78C758C8AF8%40gmail.com>
Precedence: bulk

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" =3D CASE WHEN EXCLUDED."shouldUpdateCol3" =3D TRUE THEN =
EXCLUDED."col3" ELSE table_name."col3" END,
  "col4" =3D CASE WHEN EXCLUDED."shouldUpdateCol4" =3D TRUE THEN =
EXCLUDED."col4" ELSE table_name."col4" END,
  "col5" =3D CASE WHEN EXCLUDED."shouldUpdateCol5" =3D TRUE THEN =
EXCLUDED."col5" ELSE table_name."col5" END,
  "col7" =3D 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.=