How to successfully UNION ALL for double precision in PostgreSQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am encountering a really strange problem.
This query does not work:
SELECT
t1.status :: TEXT,
t1.total_amount :: float AS amount
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
Error:
[42804] ERROR: UNION types text and double precision cannot be matched Position: 147
But this query works:
SELECT
t1.total_amount :: float AS amount,
t1.status :: TEXT
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
So the column order must be the same?
postgresql union
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
|
show 3 more comments
I am encountering a really strange problem.
This query does not work:
SELECT
t1.status :: TEXT,
t1.total_amount :: float AS amount
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
Error:
[42804] ERROR: UNION types text and double precision cannot be matched Position: 147
But this query works:
SELECT
t1.total_amount :: float AS amount,
t1.status :: TEXT
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
So the column order must be the same?
postgresql union
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
2
Is that your real query? I cannot see a position 2767 there...
– Laurenz Albe
Nov 16 '18 at 12:19
I apologize, I was trying to simplify the question. I posted the real query, hopefully its not too much
– Zanko
Nov 16 '18 at 12:26
Does it work with parentheses like this:(SELECT ...) UNION (SELECT ...)?
– Laurenz Albe
Nov 16 '18 at 12:37
No, still the same error
– Zanko
Nov 16 '18 at 12:38
Hmmm. I don't see the problem right away. Try to reduce the example by removing everything that you can so that the error remains. Maybe then we can see clearer.
– Laurenz Albe
Nov 16 '18 at 12:40
|
show 3 more comments
I am encountering a really strange problem.
This query does not work:
SELECT
t1.status :: TEXT,
t1.total_amount :: float AS amount
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
Error:
[42804] ERROR: UNION types text and double precision cannot be matched Position: 147
But this query works:
SELECT
t1.total_amount :: float AS amount,
t1.status :: TEXT
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
So the column order must be the same?
postgresql union
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
I am encountering a really strange problem.
This query does not work:
SELECT
t1.status :: TEXT,
t1.total_amount :: float AS amount
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
Error:
[42804] ERROR: UNION types text and double precision cannot be matched Position: 147
But this query works:
SELECT
t1.total_amount :: float AS amount,
t1.status :: TEXT
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
t1.fees :: float AS amount,
'Processed' :: TEXT AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;
So the column order must be the same?
postgresql union
postgresql union
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
edited Nov 16 '18 at 13:12
Laurenz Albe
52.5k103052
52.5k103052
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
asked Nov 16 '18 at 12:17
ZankoZanko
1,22821028
1,22821028
2
Is that your real query? I cannot see a position 2767 there...
– Laurenz Albe
Nov 16 '18 at 12:19
I apologize, I was trying to simplify the question. I posted the real query, hopefully its not too much
– Zanko
Nov 16 '18 at 12:26
Does it work with parentheses like this:(SELECT ...) UNION (SELECT ...)?
– Laurenz Albe
Nov 16 '18 at 12:37
No, still the same error
– Zanko
Nov 16 '18 at 12:38
Hmmm. I don't see the problem right away. Try to reduce the example by removing everything that you can so that the error remains. Maybe then we can see clearer.
– Laurenz Albe
Nov 16 '18 at 12:40
|
show 3 more comments
2
Is that your real query? I cannot see a position 2767 there...
– Laurenz Albe
Nov 16 '18 at 12:19
I apologize, I was trying to simplify the question. I posted the real query, hopefully its not too much
– Zanko
Nov 16 '18 at 12:26
Does it work with parentheses like this:(SELECT ...) UNION (SELECT ...)?
– Laurenz Albe
Nov 16 '18 at 12:37
No, still the same error
– Zanko
Nov 16 '18 at 12:38
Hmmm. I don't see the problem right away. Try to reduce the example by removing everything that you can so that the error remains. Maybe then we can see clearer.
– Laurenz Albe
Nov 16 '18 at 12:40
2
2
Is that your real query? I cannot see a position 2767 there...
– Laurenz Albe
Nov 16 '18 at 12:19
Is that your real query? I cannot see a position 2767 there...
– Laurenz Albe
Nov 16 '18 at 12:19
I apologize, I was trying to simplify the question. I posted the real query, hopefully its not too much
– Zanko
Nov 16 '18 at 12:26
I apologize, I was trying to simplify the question. I posted the real query, hopefully its not too much
– Zanko
Nov 16 '18 at 12:26
Does it work with parentheses like this:
(SELECT ...) UNION (SELECT ...)?– Laurenz Albe
Nov 16 '18 at 12:37
Does it work with parentheses like this:
(SELECT ...) UNION (SELECT ...)?– Laurenz Albe
Nov 16 '18 at 12:37
No, still the same error
– Zanko
Nov 16 '18 at 12:38
No, still the same error
– Zanko
Nov 16 '18 at 12:38
Hmmm. I don't see the problem right away. Try to reduce the example by removing everything that you can so that the error remains. Maybe then we can see clearer.
– Laurenz Albe
Nov 16 '18 at 12:40
Hmmm. I don't see the problem right away. Try to reduce the example by removing everything that you can so that the error remains. Maybe then we can see clearer.
– Laurenz Albe
Nov 16 '18 at 12:40
|
show 3 more comments
1 Answer
1
active
oldest
votes
Yes, the order must be the same. It is not the column alias, but the column position that is the deciding factor.
See the documentation for details about “UNION compatible data types”.
The documentation has
The resolution algorithm is applied separately to each output column of a union query.
It fails to make explicit that a column is identified by its position.
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
add a 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
});
}
});
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%2f53337755%2fhow-to-successfully-union-all-for-double-precision-in-postgresql%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
Yes, the order must be the same. It is not the column alias, but the column position that is the deciding factor.
See the documentation for details about “UNION compatible data types”.
The documentation has
The resolution algorithm is applied separately to each output column of a union query.
It fails to make explicit that a column is identified by its position.
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
add a comment |
Yes, the order must be the same. It is not the column alias, but the column position that is the deciding factor.
See the documentation for details about “UNION compatible data types”.
The documentation has
The resolution algorithm is applied separately to each output column of a union query.
It fails to make explicit that a column is identified by its position.
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
add a comment |
Yes, the order must be the same. It is not the column alias, but the column position that is the deciding factor.
See the documentation for details about “UNION compatible data types”.
The documentation has
The resolution algorithm is applied separately to each output column of a union query.
It fails to make explicit that a column is identified by its position.
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
Yes, the order must be the same. It is not the column alias, but the column position that is the deciding factor.
See the documentation for details about “UNION compatible data types”.
The documentation has
The resolution algorithm is applied separately to each output column of a union query.
It fails to make explicit that a column is identified by its position.
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
answered Nov 16 '18 at 13:09
Laurenz AlbeLaurenz Albe
52.5k103052
52.5k103052
add a comment |
add a comment |
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.
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%2f53337755%2fhow-to-successfully-union-all-for-double-precision-in-postgresql%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
2
Is that your real query? I cannot see a position 2767 there...
– Laurenz Albe
Nov 16 '18 at 12:19
I apologize, I was trying to simplify the question. I posted the real query, hopefully its not too much
– Zanko
Nov 16 '18 at 12:26
Does it work with parentheses like this:
(SELECT ...) UNION (SELECT ...)?– Laurenz Albe
Nov 16 '18 at 12:37
No, still the same error
– Zanko
Nov 16 '18 at 12:38
Hmmm. I don't see the problem right away. Try to reduce the example by removing everything that you can so that the error remains. Maybe then we can see clearer.
– Laurenz Albe
Nov 16 '18 at 12:40