Postgresql Updates and inserts -- upsert to almost all columns except one column

Question

I'm trying to upsert the data by using conflict and below is the query and each every time it keeps on inserting the record, but not updating, any suggestion on the query?

    CREATE TABLE tbl (
      id    int PRIMARY KEY
    , text  text
    , extra text
    , vndr_code int4,
    item_code varchar(50)
    ,created_date timestamp
    ,created_by varchar(225)
    ,modified_date timestamp 
    ,modified_by varchar(225)
    );

INSERT INTO tbl VALUES
  (1, 'foo1', 'sample_data_test1',12345, 89890, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' ),
  (2, 'foo2', 'sample_data_test2',12345, 22000, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' ),
  (3, 'foo3', 'sample_data_test3',12345, 33030, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' ),
  (4, 'foo4', 'sample_data_test4',12345, 44404, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' ),
  (5, 'foo5', 'sample_data_test5',12345, 55555, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' )

;

Upsert:

WITH data(id) AS (              -- Only 1st column gets explicit name
   VALUES
  (5, 'foo5', 'sample_data_test5_view',12345, 55555, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' ),
  (6, 'foo6', 'sample_data_test6',12345, 55555, now()::timestamp(0), 'user_name',now()::timestamp(0), 'user_name' )

   )
, del AS (
   DELETE FROM tbl AS t
   USING  data d
   WHERE  t.id = d.id
   -- AND    t <> d              -- optional, to avoid empty updates
   )                             -- only works for complete rows
INSERT INTO tbl AS t
TABLE  data                      -- short for: SELECT * FROM data
ON     CONFLICT (id) DO NOTHING
RETURNING t.id;

how not to update created_by & created_date columns, just update the rest of those columns exclude id?

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/#:~:text=In%20relational%20databases%2C%20the%20term,combination%20of%20update%20or%20insert). I don't see where you actualyl issue an update statement. Better Onsite reference: https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql — xQbert, Jun 15 '22 at 17:55
Possible Duplicate of: https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql — xQbert, Jun 15 '22 at 17:57

score 0 · Answer 1 · answered Jun 15 '22 at 17:56

 WITH data(id) AS (

see that part that says (id)? That is the column list -- you only have one column because you only specify 1 column

 WITH data(id,text,extra,vndr_code,item_code,
           created_date,created_by,modified_data,modified_by) AS (

would give you what you want

Postgresql Updates and inserts -- upsert to almost all columns except one column

1 Answers1