Oracle query to PostgreSQL conversion

Question

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)

Gordon Linoff · Accepted Answer · 2018-11-11 14:46:04Z

1

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.

edited Nov 11, 2018 at 14:46

answered Nov 11, 2018 at 14:14

Gordon Linoff

1.3m62 gold badges704 silver badges856 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

liotur Over a year ago

I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

APC Over a year ago

@liotur - so do you have a unique constraint defined on your target table?

liotur Over a year ago

Yes I have, lets call it OID.

liotur Over a year ago

@Gordon Linoff, I don't want to change something in the tables and columns. I have something that works in Oracle, and I need to convert it to postgreSQL. Is there way to do it without changing the key?

Gordon Linoff Over a year ago

@liotur . . . Adding a unique constraint on a pair of columns that should be unique does not seem like "changing" the tables. In any case, that is how upsert works in Postgres. Your alternative is using separate update and insert statements within a single transaction.

|

Collectives™ on Stack Overflow

Oracle query to PostgreSQL conversion

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related