1

create an upsert query in a schema with primary key and unique column, the data is loaded from a rest api, so i tried to create an upsert with both columns in the on conflict statement but i think that the upsert query only acepts one column in the on conflict restriction.

table schema:

create table public.INV_TXN_SOURCE_TYPES_TL (
    TRANSACTION_SOURCE_TYPE_ID numeric  ,
    LANGUAGE  varchar(40)   ,
    SOURCE_LANG varchar(40),
    LAST_UPDATE_DATE TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        unique ( LANGUAGE),
     primary key (TRANSACTION_SOURCE_TYPE_ID)
);

the update restriction happens when the LAST_UPDATE_DATE field is mayor than the current date in the db.

the query, it does not work:

insert into public.INV_TXN_SOURCE_TYPES_TL
    ( TRANSACTION_SOURCE_TYPE_ID , LANGUAGE , SOURCE_LANG , LAST_UPDATE_DATE )
    select TRANSACTION_SOURCE_TYPE_ID , LANGUAGE , SOURCE_LANG , LAST_UPDATE_DATE
from extractions.INV_TXN_SOURCE_TYPES_TL
ON CONFLICT (TRANSACTION_SOURCE_TYPE_ID,LANGUAGE)
    DO UPDATE SET SOURCE_LANG = excluded.SOURCE_LANG , LAST_UPDATE_DATE = excluded.LAST_UPDATE_DATE ; 

any other way to do it?

javier_orta
  • 457
  • 4
  • 15

0 Answers0