Pgsql error in coalesce

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Pgsql error in coalesce

Chamath Sajeewa
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!!
Reply | Threaded
Open this post in threaded view
|

RE: Pgsql error in coalesce

Stephen Froehlich

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

Reply | Threaded
Open this post in threaded view
|

Re: Pgsql error in coalesce

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

Reply | Threaded
Open this post in threaded view
|

RE: Pgsql error in coalesce

Stephen Froehlich

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 <[hidden email]>
Sent: Monday, July 27, 2020 11:15
To: Stephen Froehlich <[hidden email]>
Cc: [hidden email]
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, <[hidden email]> 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 <[hidden email]>
Sent: Monday, July 27, 2020 10:51
To: [hidden email]
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!!

Reply | Threaded
Open this post in threaded view
|

RE: [EXTERNAL] Pgsql error in coalesce

Vianello, Dan A
In reply to this post by Chamath Sajeewa

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

 

 

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.
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Pgsql error in coalesce

Chamath Sajeewa
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, <[hidden email]> 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

 

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

 

 

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.
Reply | Threaded
Open this post in threaded view
|

RE: Pgsql error in coalesce

David Raymond
In reply to this post by Chamath Sajeewa

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

Reply | Threaded
Open this post in threaded view
|

Re: Pgsql error in coalesce

Alvaro Herrera-9
In reply to this post by Chamath Sajeewa
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


Reply | Threaded
Open this post in threaded view
|

Re: Pgsql error in coalesce

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

On Mon, 27 Jul 2020, 23:12 Alvaro Herrera, <[hidden email]> wrote:
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
Reply | Threaded
Open this post in threaded view
|

Re: Pgsql error in coalesce

David G Johnston
In reply to this post by Chamath Sajeewa
On Monday, July 27, 2020, Chamath Sajeewa <[hidden email]> 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.