PostgreSQL › PostgreSQL - novice

Pgsql error in coalesce

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

10 messages Options

Chamath Sajeewa

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

Stephen Froehlich

RE: 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 <[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!!

Chamath Sajeewa

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

Stephen Froehlich

RE: Pgsql error in coalesce

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

Vianello, Dan A

RE: [EXTERNAL] Pgsql error in coalesce

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.

Chamath Sajeewa

Re: [EXTERNAL] Pgsql error in coalesce

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.

David Raymond

RE: Pgsql error in coalesce

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

Alvaro Herrera-9

Re: Pgsql error in coalesce

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

Chamath Sajeewa

Re: Pgsql error in coalesce

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

David G Johnston

Re: Pgsql error in coalesce

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.