Mixing
Why do I need to cast NULL to column type?
I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:
(Both the active and core fields are of type boolean)
UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;
However it fails with:
ERROR: column "core" is of type boolean but expression is of type text
I can get it to work by adding ::boolean to the nulls, but that just seems odd, why is NULL considered of type TEXT?
Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).
Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?
If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.
postgresql null cast
edited Jan 27 at 18:13
MDCCL
6,85331745
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
add a comment |
I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:
(Both the active and core fields are of type boolean)
UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;
However it fails with:
ERROR: column "core" is of type boolean but expression is of type text
I can get it to work by adding ::boolean to the nulls, but that just seems odd, why is NULL considered of type TEXT?
Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).
Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?
If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.
postgresql null cast
edited Jan 27 at 18:13
MDCCL
6,85331745
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
add a comment |
I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:
(Both the active and core fields are of type boolean)
UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;
However it fails with:
ERROR: column "core" is of type boolean but expression is of type text
I can get it to work by adding ::boolean to the nulls, but that just seems odd, why is NULL considered of type TEXT?
Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).
Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?
If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.
postgresql null cast
edited Jan 27 at 18:13
MDCCL
6,85331745
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:
(Both the active and core fields are of type boolean)
UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;
However it fails with:
ERROR: column "core" is of type boolean but expression is of type text
I can get it to work by adding ::boolean to the nulls, but that just seems odd, why is NULL considered of type TEXT?
Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).
Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?
If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.
postgresql null cast
postgresql null cast
edited Jan 27 at 18:13
MDCCL
6,85331745
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
edited Jan 27 at 18:13
MDCCL
6,85331745
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
edited Jan 27 at 18:13
MDCCL
6,85331745
edited Jan 27 at 18:13
MDCCL
6,85331745
edited Jan 27 at 18:13
MDCCL
6,85331745
6,85331745
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
asked Jan 25 at 3:39
ChristopherJChristopherJ
207110
207110
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here:
SELECT pg_typeof(NULL);
pg_typeof
───────────
unknown
This changes when a VALUES table comes into the picture:
SELECT pg_typeof(core) FROM (
VALUES (NULL)
) new_values (core);
pg_typeof
───────────
text
This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:
/*
* If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
* then resolve as type TEXT. This situation comes up with constructs
* like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
* UNION SELECT 'bar'; It might seem desirable to leave the construct's
* output type as UNKNOWN, but that really doesn't work, because we'd
* probably end up needing a runtime coercion from UNKNOWN to something
* else, and we usually won't have it. We need to coerce the unknown
* literals while they are still literals, so a decision has to be made
* now.
*/
(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)
The way out, however, might be the following. Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases). From your example, a small trick could possibly help:
SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);
active │ core │ id
────────┼──────┼──────
t │ │ 1234
Here you use row expressions casted to the table's type, and then extracting them back to a table.
Based on the above, your UPDATE could look like
UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
((true, NULL, 3419)::fields),
((false, NULL, 3420)::fields)
) AS new_values(x) WHERE (x).id = t.id;
Notes:
- I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
- if you need only a subset of the columns, you can create custom types for this purpose. Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).
Look at the whole thing working on dbfiddle.
answered Jan 25 at 7:43
dezsodezso
22.3k116097
Thanks, this is interesting, however, for me, the code above producesCannot cast type boolean to bigint in column 1(the error points at the :: between the first fields statement)
– ChristopherJ
Jan 25 at 8:29
1
@ChristopherJ the answer assumes that the table calledfieldshas 3 columns,(active, core, id)with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?
– ypercubeᵀᴹ
Jan 25 at 13:54
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
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%2f228046%2fwhy-do-i-need-to-cast-null-to-column-type%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
This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here:
SELECT pg_typeof(NULL);
pg_typeof
───────────
unknown
This changes when a VALUES table comes into the picture:
SELECT pg_typeof(core) FROM (
VALUES (NULL)
) new_values (core);
pg_typeof
───────────
text
This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:
/*
* If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
* then resolve as type TEXT. This situation comes up with constructs
* like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
* UNION SELECT 'bar'; It might seem desirable to leave the construct's
* output type as UNKNOWN, but that really doesn't work, because we'd
* probably end up needing a runtime coercion from UNKNOWN to something
* else, and we usually won't have it. We need to coerce the unknown
* literals while they are still literals, so a decision has to be made
* now.
*/
(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)
The way out, however, might be the following. Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases). From your example, a small trick could possibly help:
SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);
active │ core │ id
────────┼──────┼──────
t │ │ 1234
Here you use row expressions casted to the table's type, and then extracting them back to a table.
Based on the above, your UPDATE could look like
UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
((true, NULL, 3419)::fields),
((false, NULL, 3420)::fields)
) AS new_values(x) WHERE (x).id = t.id;
Notes:
- I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
- if you need only a subset of the columns, you can create custom types for this purpose. Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).
Look at the whole thing working on dbfiddle.
answered Jan 25 at 7:43
dezsodezso
22.3k116097
Thanks, this is interesting, however, for me, the code above producesCannot cast type boolean to bigint in column 1(the error points at the :: between the first fields statement)
– ChristopherJ
Jan 25 at 8:29
1
@ChristopherJ the answer assumes that the table calledfieldshas 3 columns,(active, core, id)with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?
– ypercubeᵀᴹ
Jan 25 at 13:54
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
add a comment |
This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here:
SELECT pg_typeof(NULL);
pg_typeof
───────────
unknown
This changes when a VALUES table comes into the picture:
SELECT pg_typeof(core) FROM (
VALUES (NULL)
) new_values (core);
pg_typeof
───────────
text
This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:
/*
* If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
* then resolve as type TEXT. This situation comes up with constructs
* like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
* UNION SELECT 'bar'; It might seem desirable to leave the construct's
* output type as UNKNOWN, but that really doesn't work, because we'd
* probably end up needing a runtime coercion from UNKNOWN to something
* else, and we usually won't have it. We need to coerce the unknown
* literals while they are still literals, so a decision has to be made
* now.
*/
(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)
The way out, however, might be the following. Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases). From your example, a small trick could possibly help:
SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);
active │ core │ id
────────┼──────┼──────
t │ │ 1234
Here you use row expressions casted to the table's type, and then extracting them back to a table.
Based on the above, your UPDATE could look like
UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
((true, NULL, 3419)::fields),
((false, NULL, 3420)::fields)
) AS new_values(x) WHERE (x).id = t.id;
Notes:
- I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
- if you need only a subset of the columns, you can create custom types for this purpose. Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).
Look at the whole thing working on dbfiddle.
answered Jan 25 at 7:43
dezsodezso
22.3k116097
Thanks, this is interesting, however, for me, the code above producesCannot cast type boolean to bigint in column 1(the error points at the :: between the first fields statement)
– ChristopherJ
Jan 25 at 8:29
1
@ChristopherJ the answer assumes that the table calledfieldshas 3 columns,(active, core, id)with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?
– ypercubeᵀᴹ
Jan 25 at 13:54
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
add a comment |
This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here:
SELECT pg_typeof(NULL);
pg_typeof
───────────
unknown
This changes when a VALUES table comes into the picture:
SELECT pg_typeof(core) FROM (
VALUES (NULL)
) new_values (core);
pg_typeof
───────────
text
This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:
/*
* If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
* then resolve as type TEXT. This situation comes up with constructs
* like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
* UNION SELECT 'bar'; It might seem desirable to leave the construct's
* output type as UNKNOWN, but that really doesn't work, because we'd
* probably end up needing a runtime coercion from UNKNOWN to something
* else, and we usually won't have it. We need to coerce the unknown
* literals while they are still literals, so a decision has to be made
* now.
*/
(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)
The way out, however, might be the following. Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases). From your example, a small trick could possibly help:
SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);
active │ core │ id
────────┼──────┼──────
t │ │ 1234
Here you use row expressions casted to the table's type, and then extracting them back to a table.
Based on the above, your UPDATE could look like
UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
((true, NULL, 3419)::fields),
((false, NULL, 3420)::fields)
) AS new_values(x) WHERE (x).id = t.id;
Notes:
- I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
- if you need only a subset of the columns, you can create custom types for this purpose. Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).
Look at the whole thing working on dbfiddle.
answered Jan 25 at 7:43
dezsodezso
22.3k116097
This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here:
SELECT pg_typeof(NULL);
pg_typeof
───────────
unknown
This changes when a VALUES table comes into the picture:
SELECT pg_typeof(core) FROM (
VALUES (NULL)
) new_values (core);
pg_typeof
───────────
text
This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:
/*
* If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
* then resolve as type TEXT. This situation comes up with constructs
* like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
* UNION SELECT 'bar'; It might seem desirable to leave the construct's
* output type as UNKNOWN, but that really doesn't work, because we'd
* probably end up needing a runtime coercion from UNKNOWN to something
* else, and we usually won't have it. We need to coerce the unknown
* literals while they are still literals, so a decision has to be made
* now.
*/
(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)
The way out, however, might be the following. Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases). From your example, a small trick could possibly help:
SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);
active │ core │ id
────────┼──────┼──────
t │ │ 1234
Here you use row expressions casted to the table's type, and then extracting them back to a table.
Based on the above, your UPDATE could look like
UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
((true, NULL, 3419)::fields),
((false, NULL, 3420)::fields)
) AS new_values(x) WHERE (x).id = t.id;
Notes:
- I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
- if you need only a subset of the columns, you can create custom types for this purpose. Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).
Look at the whole thing working on dbfiddle.
answered Jan 25 at 7:43
dezsodezso
22.3k116097
edited Jan 28 at 21:57
answered Jan 25 at 7:43
dezsodezso
22.3k116097
answered Jan 25 at 7:43
dezsodezso
22.3k116097
answered Jan 25 at 7:43
dezsodezso
22.3k116097
22.3k116097
Thanks, this is interesting, however, for me, the code above producesCannot cast type boolean to bigint in column 1(the error points at the :: between the first fields statement)
– ChristopherJ
Jan 25 at 8:29
1
@ChristopherJ the answer assumes that the table calledfieldshas 3 columns,(active, core, id)with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?
– ypercubeᵀᴹ
Jan 25 at 13:54
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
add a comment |
Thanks, this is interesting, however, for me, the code above producesCannot cast type boolean to bigint in column 1(the error points at the :: between the first fields statement)
– ChristopherJ
Jan 25 at 8:29
1
@ChristopherJ the answer assumes that the table calledfieldshas 3 columns,(active, core, id)with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?
– ypercubeᵀᴹ
Jan 25 at 13:54
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
Thanks, this is interesting, however, for me, the code above produces
Cannot cast type boolean to bigint in column 1 (the error points at the :: between the first fields statement)– ChristopherJ
Jan 25 at 8:29
Thanks, this is interesting, however, for me, the code above produces
Cannot cast type boolean to bigint in column 1 (the error points at the :: between the first fields statement)– ChristopherJ
Jan 25 at 8:29
1
1
@ChristopherJ the answer assumes that the table called
fields has 3 columns, (active, core, id) with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?– ypercubeᵀᴹ
Jan 25 at 13:54
@ChristopherJ the answer assumes that the table called
fields has 3 columns, (active, core, id) with boolean, boolean and int/bigint types. Does your table have more columns or different types or are the columns defined in different order?– ypercubeᵀᴹ
Jan 25 at 13:54
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
Ah i see, thanks, yep there are more columns and in different order. Got it thank you
– ChristopherJ
Jan 26 at 14:15
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%2f228046%2fwhy-do-i-need-to-cast-null-to-column-type%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
