Oracle query to PostgreSQL conversion
0
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 '18 at 19:26
marc_s
575k12811101257
asked Nov 11 '18 at 14:08
lioturliotur
619
add a comment |
0
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 '18 at 19:26
marc_s
575k12811101257
asked Nov 11 '18 at 14:08
lioturliotur
619
add a comment |
0
0
0
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 '18 at 19:26
marc_s
575k12811101257
asked Nov 11 '18 at 14:08
lioturliotur
619
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 '18 at 19:26
marc_s
575k12811101257
asked Nov 11 '18 at 14:08
lioturliotur
619
edited Nov 11 '18 at 19:26
marc_s
575k12811101257
asked Nov 11 '18 at 14:08
lioturliotur
619
edited Nov 11 '18 at 19:26
marc_s
575k12811101257
edited Nov 11 '18 at 19:26
marc_s
575k12811101257
edited Nov 11 '18 at 19:26
marc_s
575k12811101257
575k12811101257
asked Nov 11 '18 at 14:08
lioturliotur
619
asked Nov 11 '18 at 14:08
lioturliotur
619
asked Nov 11 '18 at 14:08
lioturliotur
619
619
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 '18 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 '18 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 '18 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 '18 at 15:09
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 '18 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 '18 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 '18 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 '18 at 15:09
|
show 1 more comment
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.
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 '18 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 '18 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 '18 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 '18 at 15:09
|
show 1 more comment
1
1
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.
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
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 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
edited Nov 11 '18 at 14:46
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
answered Nov 11 '18 at 14:14
Gordon LinoffGordon Linoff
769k35302403
769k35302403
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 '18 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 '18 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 '18 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 '18 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 '18 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 '18 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 '18 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 '18 at 15:09
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 '18 at 14:23
I am receiving now ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
– liotur
Nov 11 '18 at 14:23
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
@liotur - so do you have a unique constraint defined on your target table?
– APC
Nov 11 '18 at 14:33
Yes I have, lets call it OID.
– liotur
Nov 11 '18 at 14:38
Yes I have, lets call it OID.
– liotur
Nov 11 '18 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 '18 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 '18 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 '18 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 '18 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.
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
