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?