Subject: Pgsql error in coalesce

From: Chamath Sajeewa <[email-masked]>
Date: Mon, 27 Jul 2020 22:21:17 +0530

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!!

---

From: Stephen Froehlich <[email-masked]>
Date: Mon, 27 Jul 2020 17:05:46 +0000

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 <[email-masked]>
Sent: Monday, July 27, 2020 10:51
To: [email-masked]
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!!

---

From: Chamath Sajeewa <[email-masked]>
Date: Mon, 27 Jul 2020 22:45:02 +0530

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, <[email-masked]>
wrote:

> *From:* Chamath Sajeewa <[email-masked]>
> *Sent:* Monday, July 27, 2020 10:51
> *To:* [email-masked]
> *Subject:* Pgsql error in coalesce
>
>
>
> Hi,
>

---

From: Stephen Froehlich <[email-masked]>
Date: Mon, 27 Jul 2020 17:18:22 +0000

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 <[email-masked]>
Sent: Monday, July 27, 2020 11:15
To: Stephen Froehlich <[email-masked]>
Cc: [email-masked]
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, <[email-masked]<mailto:[email-masked]>> wrote:
What do you get with the following two type castings:

--Stephen

From: Chamath Sajeewa <[email-masked]<mailto:[email-masked]>>
Sent: Monday, July 27, 2020 10:51
To: [email-masked]<mailto:[email-masked]>
Subject: Pgsql error in coalesce

Hi,

---

From: "Vianello, Dan A" <[email-masked]>
Date: Mon, 27 Jul 2020 17:27:00 +0000

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);

E-MAIL CONFIDENTIALITY NOTICE: 
The contents of this e-mail message and 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.

---

From: Chamath Sajeewa <[email-masked]>
Date: Mon, 27 Jul 2020 23:02:42 +0530

Hi,
I thought it check the data type of the specified column. Isn't it the case
here?

On Mon, 27 Jul 2020, 22:57 Vianello, Dan A, <[email-masked]>
wrote:

> 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
>
>
>
> The contents of this e-mail message and
> 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.
>

---

From: David Raymond <[email-masked]>
Date: Mon, 27 Jul 2020 17:33:09 +0000

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 <[email-masked]>
Sent: Monday, July 27, 2020 12:51 PM
To: [email-masked]
Subject: Pgsql error in coalesce

Hi,

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 27 Jul 2020 13:42:07 -0400

> "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

---

From: Chamath Sajeewa <[email-masked]>
Date: Mon, 27 Jul 2020 23:13:55 +0530

Hi all,
Yes i am checking the wrong table. Really sorry about the
inconvenience caused.
Thank you all for enlightening me on this.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 27 Jul 2020 12:39:49 -0700

On Monday, July 27, 2020, Chamath Sajeewa <[email-masked]> wrote:

> Hi,
> I thought it check the data type of the specified column. Isn't it the
> case here?
>

 What is this “it” and how, and to what effect, is it supposed to be
checking the data type?

David J.