Thread: Pgsql error in coalesce
What do you get with the following two type castings:
SELECT coalesce(column_name::integer, 255); ?
SELECT coalesce(column_name, ‘255’::text)::integer; ?
“integer” might actually be “smallint” if you’re only going to 255.
--Stephen
From: Chamath Sajeewa <csgsajeewa@gmail.com>
Sent: Monday, July 27, 2020 10:51
To: pgsql-novice@lists.postgresql.org
Subject: Pgsql error in coalesce
Hi,
There is table with int4 column. When select query is executed as "select coalesce(column_name, 255), query is failing with below error.
"COALESCE types text and integer cannot be mached".
Any idea?
Thank You!!
What do you get with the following two type castings:
SELECT coalesce(column_name::integer, 255); ?
SELECT coalesce(column_name, ‘255’::text)::integer; ?
“integer” might actually be “smallint” if you’re only going to 255.
--Stephen
From: Chamath Sajeewa <csgsajeewa@gmail.com>
Sent: Monday, July 27, 2020 10:51
To: pgsql-novice@lists.postgresql.org
Subject: Pgsql error in coalesce
Hi,
There is table with int4 column. When select query is executed as "select coalesce(column_name, 255), query is failing with below error.
"COALESCE types text and integer cannot be mached".
Any idea?
Thank You!!
The documentation says nothing about it … so no I have no clue. Personally, I tend to use that function in R more than I do in PostgreSQL.
From: Chamath Sajeewa <csgsajeewa@gmail.com>
Sent: Monday, July 27, 2020 11:15
To: Stephen Froehlich <s.froehlich@cablelabs.com>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: Pgsql error in coalesce
Hi,
Query is working properly when column_name::integer is used. I wonder Why is it required when column type is already set to int4. Any idea?
On Mon, 27 Jul 2020, 22:35 Stephen Froehlich, <s.froehlich@cablelabs.com> wrote:
What do you get with the following two type castings:
SELECT coalesce(column_name::integer, 255); ?
SELECT coalesce(column_name, ‘255’::text)::integer; ?
“integer” might actually be “smallint” if you’re only going to 255.
--Stephen
From: Chamath Sajeewa <csgsajeewa@gmail.com>
Sent: Monday, July 27, 2020 10:51
To: pgsql-novice@lists.postgresql.org
Subject: Pgsql error in coalesce
Hi,
There is table with int4 column. When select query is executed as "select coalesce(column_name, 255), query is failing with below error.
"COALESCE types text and integer cannot be mached".
Any idea?
Thank You!!
Your column name “column_name” is of type text. The number 255 is an integer. Since those datatypes don’t match the coalesce function fails. If the data in “column_name” can always be cast to an integer then you can use
Select coalesce(column_name::integer, 255);
Or you can cast 255 to a text string of ‘255’ with this:
Select coalesce(column_name, '255'::text);
any attachments are intended solely for the
addressee(s) and may contain confidential
and/or legally privileged information. If you
are not the intended recipient of this message
or if this message has been addressed to you
in error, please immediately alert the sender
by reply e-mail and then delete this message
and any attachments. If you are not the
intended recipient, you are notified that
any use, dissemination, distribution, copying,
or storage of this message or any attachment
is strictly prohibited.
The contents of this e-mail message andYour column name “column_name” is of type text. The number 255 is an integer. Since those datatypes don’t match the coalesce function fails. If the data in “column_name” can always be cast to an integer then you can use
Select coalesce(column_name::integer, 255);
Or you can cast 255 to a text string of ‘255’ with this:
Select coalesce(column_name, '255'::text);
any attachments are intended solely for the
addressee(s) and may contain confidential
and/or legally privileged information. If you
are not the intended recipient of this message
or if this message has been addressed to you
in error, please immediately alert the sender
by reply e-mail and then delete this message
and any attachments. If you are not the
intended recipient, you are notified that
any use, dissemination, distribution, copying,
or storage of this message or any attachment
is strictly prohibited.
Something's definitely weird here.
You're sure it's an int4 column?
Are you using an unqualified table name in the FROM clause which your search_path might be re-directing to a different table with the same name in a different schema where column_name is text? Or to a temp table with the same name which would be first in the search path?
You didn't accidentally put single quotes instead of double quotes around column_name?
Trying to think of any other little oopsies that might be in play here.
From: Chamath Sajeewa <csgsajeewa@gmail.com>
Sent: Monday, July 27, 2020 12:51 PM
To: pgsql-novice@lists.postgresql.org
Subject: Pgsql error in coalesce
Hi,
There is table with int4 column. When select query is executed as "select coalesce(column_name, 255), query is failing with below error.
"COALESCE types text and integer cannot be mached".
Any idea?
Thank You!!
On 2020-Jul-27, Chamath Sajeewa wrote: > Hi, > There is table with int4 column. When select query is executed as "select > coalesce(column_name, 255), query is failing with below error. > "COALESCE types text and integer cannot be mached". I'd bet you're not querying the table you think you're querying ... What does "\d table" show? Also, please show the complete query, and SHOW search_path . -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2020-Jul-27, Chamath Sajeewa wrote:
> Hi,
> There is table with int4 column. When select query is executed as "select
> coalesce(column_name, 255), query is failing with below error.
> "COALESCE types text and integer cannot be mached".
I'd bet you're not querying the table you think you're querying ...
What does "\d table" show? Also, please show the complete query, and
SHOW search_path .
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi,I thought it check the data type of the specified column. Isn't it the case here?