Could you please help with some converted Oracle queries to postgreSQL queries, it is exists?
I have an Oracle query which I want to adopt for postgreSQL, could you please help me with this?
merge into TABLE_NAME using dual on
(ID='CF9EB9FE6F6D4CC9B75EC0CD420421B91541944569' and ANOTHER_ID='E198D55909D94895AF747ED7E032AC58')
when matched then update set USER='USERNAME'
when not matched then insert values('EEA2620A4A0F31CE05E69B','CFB75EC0CD41B91541944569','E198D55909D94895AF747ED7E032AC58','USERNAME',null)
You do the upsert
in Postgres using insert . . . on conflict
:
insert into table_name (?, id, anotherid, ?, ?) -- put in the column names
values('EEA2620A4A0F31CE05E69B',
'CFB75EC0CD41B91541944569',
'E198D55909D94895AF747ED7E032AC58',
'USERNAME', null
)
on conflict (id, anotherid)
do update set USER = 'USERNAME';
You want a unique constraint on (id, anotherid)
so the conflict is recognized:
alter table table_name add constraint unq_tablename_id_anotherid unique (id, anotherid);
The unique constraint needs to be defined on the columns that would cause the conflict.