Oracle query to PostgreSQL conversion
up vote
0
down vote
favorite
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)
sql database oracle postgresql database-migration
edited Nov 11 at 19:26
marc_s
567k12810981249
asked Nov 11 at 14:08
liotur
549
add a comment |
up vote
0
down vote
favorite
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)
sql database oracle postgresql database-migration
edited Nov 11 at 19:26
marc_s
567k12810981249
asked Nov 11 at 14:08
liotur
549
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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)
sql database oracle postgresql database-migration
edited Nov 11 at 19:26
marc_s
567k12810981249
asked Nov 11 at 14:08
liotur
549
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)
sql database oracle postgresql database-migration
sql database oracle postgresql database-migration
edited Nov 11 at 19:26
marc_s
567k12810981249
asked Nov 11 at 14:08
liotur
549
edited Nov 11 at 19:26
marc_s
567k12810981249
asked Nov 11 at 14:08
liotur
549
edited Nov 11 at 19:26
marc_s
567k12810981249
edited Nov 11 at 19:26
marc_s
567k12810981249
edited Nov 11 at 19:26
marc_s
567k12810981249
567k12810981249
asked Nov 11 at 14:08
liotur
549
asked Nov 11 at 14:08
liotur
549
asked Nov 11 at 14:08
liotur
549
549
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
@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?
– liotur
Nov 11 at 14:57
@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 separateupdateandinsertstatements within a single transaction.
– Gordon Linoff
Nov 11 at 15:09
|
show 1 more comment
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
@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?
– liotur
Nov 11 at 14:57
@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 separateupdateandinsertstatements within a single transaction.
– Gordon Linoff
Nov 11 at 15:09
|
show 1 more comment
up vote
1
down vote
accepted
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.
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
@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?
– liotur
Nov 11 at 14:57
@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 separateupdateandinsertstatements within a single transaction.
– Gordon Linoff
Nov 11 at 15:09
|
show 1 more comment
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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.
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
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.
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
edited Nov 11 at 14:46
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
answered Nov 11 at 14:14
Gordon Linoff
749k34285391
749k34285391
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
@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?
– liotur
Nov 11 at 14:57
@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 separateupdateandinsertstatements within a single transaction.
– Gordon Linoff
Nov 11 at 15:09
|
show 1 more comment
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
@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?
– liotur
Nov 11 at 14:57
@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 separateupdateandinsertstatements within a single transaction.
– Gordon Linoff
Nov 11 at 15:09
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
Yes I have, lets call it OID.
– liotur
Nov 11 at 14:38
@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?
– liotur
Nov 11 at 14:57
@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?
– liotur
Nov 11 at 14:57
@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.– Gordon Linoff
Nov 11 at 15:09
@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.– Gordon Linoff
Nov 11 at 15:09
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249548%2foracle-query-to-postgresql-conversion%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
