Can the 'returning' clause return source columns that are not inserted?
Here's a minimal example of my real-world problem:
create table t(id serial primary key, rnd double precision);
of course you can return the inserted columns with a returning clause:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID | RND |
|----|----------------|
| 9 | 0.203221440315 |
*/
you can also return a literal:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID | RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 | 1 |
*/
but you can't return the source columns:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/
Is there any way I can get w.rnd out of the final returning clause?
SQLFiddle
postgresql postgresql-9.3
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
add a comment |
Here's a minimal example of my real-world problem:
create table t(id serial primary key, rnd double precision);
of course you can return the inserted columns with a returning clause:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID | RND |
|----|----------------|
| 9 | 0.203221440315 |
*/
you can also return a literal:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID | RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 | 1 |
*/
but you can't return the source columns:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/
Is there any way I can get w.rnd out of the final returning clause?
SQLFiddle
postgresql postgresql-9.3
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too.
– Sebastian Meine
Sep 28 '13 at 15:01
I solved a similar problem forUPDATEin this related answer on SO, but this won't work forINSERT.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
Here's a minimal example of my real-world problem:
create table t(id serial primary key, rnd double precision);
of course you can return the inserted columns with a returning clause:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID | RND |
|----|----------------|
| 9 | 0.203221440315 |
*/
you can also return a literal:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID | RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 | 1 |
*/
but you can't return the source columns:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/
Is there any way I can get w.rnd out of the final returning clause?
SQLFiddle
postgresql postgresql-9.3
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
Here's a minimal example of my real-world problem:
create table t(id serial primary key, rnd double precision);
of course you can return the inserted columns with a returning clause:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID | RND |
|----|----------------|
| 9 | 0.203221440315 |
*/
you can also return a literal:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID | RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 | 1 |
*/
but you can't return the source columns:
with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/
Is there any way I can get w.rnd out of the final returning clause?
SQLFiddle
postgresql postgresql-9.3
postgresql postgresql-9.3
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
edited Sep 28 '13 at 13:24
Jack Douglas
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
asked Sep 28 '13 at 12:49
Jack Douglas♦Jack Douglas
27.7k1075149
27.7k1075149
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too.
– Sebastian Meine
Sep 28 '13 at 15:01
I solved a similar problem forUPDATEin this related answer on SO, but this won't work forINSERT.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too.
– Sebastian Meine
Sep 28 '13 at 15:01
I solved a similar problem forUPDATEin this related answer on SO, but this won't work forINSERT.
– Erwin Brandstetter
Oct 2 '13 at 0:48
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too.
– Sebastian Meine
Sep 28 '13 at 15:01
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too.
– Sebastian Meine
Sep 28 '13 at 15:01
I solved a similar problem for
UPDATE in this related answer on SO, but this won't work for INSERT.– Erwin Brandstetter
Oct 2 '13 at 0:48
I solved a similar problem for
UPDATE in this related answer on SO, but this won't work for INSERT.– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
1 Answer
1
active
oldest
votes
The documentation on the RETURNING clause says:
An expression to be computed and returned by the INSERT command after
each row is inserted. The expression can use any column names of the
table named by table_name. Write * to return all columns of the
inserted row(s).
This clearly does not apply for columns from an other table.
Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:
WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
FROM w, x;
That is, you can put more than one writable CTE to the beginning of a query. Please see this in action on dbfiddle.
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
2
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f50693%2fcan-the-returning-clause-return-source-columns-that-are-not-inserted%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
The documentation on the RETURNING clause says:
An expression to be computed and returned by the INSERT command after
each row is inserted. The expression can use any column names of the
table named by table_name. Write * to return all columns of the
inserted row(s).
This clearly does not apply for columns from an other table.
Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:
WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
FROM w, x;
That is, you can put more than one writable CTE to the beginning of a query. Please see this in action on dbfiddle.
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
2
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
The documentation on the RETURNING clause says:
An expression to be computed and returned by the INSERT command after
each row is inserted. The expression can use any column names of the
table named by table_name. Write * to return all columns of the
inserted row(s).
This clearly does not apply for columns from an other table.
Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:
WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
FROM w, x;
That is, you can put more than one writable CTE to the beginning of a query. Please see this in action on dbfiddle.
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
2
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
The documentation on the RETURNING clause says:
An expression to be computed and returned by the INSERT command after
each row is inserted. The expression can use any column names of the
table named by table_name. Write * to return all columns of the
inserted row(s).
This clearly does not apply for columns from an other table.
Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:
WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
FROM w, x;
That is, you can put more than one writable CTE to the beginning of a query. Please see this in action on dbfiddle.
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
The documentation on the RETURNING clause says:
An expression to be computed and returned by the INSERT command after
each row is inserted. The expression can use any column names of the
table named by table_name. Write * to return all columns of the
inserted row(s).
This clearly does not apply for columns from an other table.
Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:
WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
FROM w, x;
That is, you can put more than one writable CTE to the beginning of a query. Please see this in action on dbfiddle.
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
edited 2 mins ago
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
answered Sep 29 '13 at 6:35
dezsodezso
22k116096
22k116096
2
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
2
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
2
2
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle.
– Erwin Brandstetter
Oct 2 '13 at 0:48
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
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%2fdba.stackexchange.com%2fquestions%2f50693%2fcan-the-returning-clause-return-source-columns-that-are-not-inserted%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
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too.
– Sebastian Meine
Sep 28 '13 at 15:01
I solved a similar problem for
UPDATEin this related answer on SO, but this won't work forINSERT.– Erwin Brandstetter
Oct 2 '13 at 0:48