PostgreSQL Upsert COALESCE-CodePudding

I am trying to create a script that updates a Postgres table on changes or new rows only for example:

1: Insert if the unique identifier does not exist 2: Update if the unique identifier exists and only if any of the fields have changed compared to the incoming temp table

The "incoming" temp table is updated by a CSV file, so currently attempting to "Upsert" from this temp table into the main table where there are deltas/changes only.

Currently, each time I run the following script it updates all the records that match the unique identifier rather than the ones with changes are updated.

Successfully run. Total query runtime: 48 secs 662 msec.
1187514 rows affected.

my vain/unsuccessful attempt:

INSERT INTO public.testdb1 AS a
(
    id,
    fielda,
    fieldb,
    fieldc,
    fieldd,
    fielde,
    fieldf
) 
SELECT 
    id,
    fielda,
    fieldb,
    fieldc,
    fieldd,
    fielde,
    fieldf
FROM 
    public.temp_table2
ON CONFLICT 
    (id, fielda) 
DO UPDATE 
  SET 
      id  = COALESCE(excluded.id, a.id),
        fielda  = COALESCE(excluded.fielda, a.fielda),
        fieldb  = COALESCE(excluded.fieldb, a.fieldb),
        fieldc  = COALESCE(excluded.fieldc, a.fieldc),
        fieldd  = COALESCE(excluded.fieldd, a.fieldd),
        fielde  = COALESCE(excluded.fielde, a.fielde),
        fieldf  = COALESCE(excluded.fieldf, a.fieldf)
RETURNING
    a.id

As an additional question is there a way of getting the RETURNING part to show the rows updated/inserted as a count?

CodePudding user response：

You can try to execute your command in cte then RETURNING and COUNT it.

WITH cte AS (
 UPDATE 
  SET 
      id  = COALESCE(excluded.id, a.id),
        fielda  = COALESCE(excluded.fielda, a.fielda),
        fieldb  = COALESCE(excluded.fieldb, a.fieldb),
        fieldc  = COALESCE(excluded.fieldc, a.fieldc),
        fieldd  = COALESCE(excluded.fieldd, a.fieldd),
        fielde  = COALESCE(excluded.fielde, a.fielde),
        fieldf  = COALESCE(excluded.fieldf, a.fieldf)
   RETURNING 1
)
SELECT count(*) FROM cte;

sqlfiddle

CodePudding user response：

Based on the answer from D-Shih this seems to work, but not sure its the most elegant way as i am specifying the fields in triplicate..

WITH cte AS(
    INSERT INTO public.testdb1 AS a
    (
        id,
        fielda,
        fieldb,
        fieldc,
        fieldd,
        fielde,
        fieldf
    ) 
    SELECT 
        *
    FROM 
        public.temp_table2
    ON CONFLICT 
        (id, fielda) 
    DO UPDATE 
      SET 
        id  = excluded.id,
        fielda  = excluded.fielda,
        fieldb  = excluded.fieldb,
        fieldc  = excluded.fieldc,
        fieldd  = excluded.fieldd,
        fielde  = excluded.fielde,
        fieldf  = excluded.fieldf,
      WHERE
        a.id IS DISTINCT FROM excluded.id OR
        a.fielda IS DISTINCT FROM excluded.fielda OR
        a.fieldb IS DISTINCT FROM excluded.field OR
        a.fieldc IS DISTINCT FROM excluded.fieldc OR
        a.fieldd IS DISTINCT FROM excluded.fieldd OR
        a.fielde IS DISTINCT FROM excluded.fielde OR
        a.fieldf IS DISTINCT FROM excluded.fieldf
    RETURNING
        1
)SELECT count(*) FROM cte;