public inbox for [email protected]
help / color / mirror / Atom feedPgsql error in coalesce
10+ messages / 6 participants
[nested] [flat]
* Pgsql error in coalesce
@ 2020-07-27 16:51 Chamath Sajeewa <[email protected]>
0 siblings, 4 replies; 10+ messages in thread
From: Chamath Sajeewa @ 2020-07-27 16:51 UTC (permalink / raw)
To: [email protected]
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!!
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Pgsql error in coalesce
@ 2020-07-27 17:05 Stephen Froehlich <[email protected]>
parent: Chamath Sajeewa <[email protected]>
3 siblings, 1 reply; 10+ messages in thread
From: Stephen Froehlich @ 2020-07-27 17:05 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; [email protected] <[email protected]>
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 protected]>
Sent: Monday, July 27, 2020 10:51
To: [email protected]
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!!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Pgsql error in coalesce
@ 2020-07-27 17:15 Chamath Sajeewa <[email protected]>
parent: Stephen Froehlich <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Chamath Sajeewa @ 2020-07-27 17:15 UTC (permalink / raw)
To: Stephen Froehlich <[email protected]>; +Cc: [email protected]
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 protected]>
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 <[email protected]>
> *Sent:* Monday, July 27, 2020 10:51
> *To:* [email protected]
> *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!!
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Pgsql error in coalesce
@ 2020-07-27 17:18 Stephen Froehlich <[email protected]>
parent: Chamath Sajeewa <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Stephen Froehlich @ 2020-07-27 17:18 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; +Cc: [email protected] <[email protected]>
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 protected]>
Sent: Monday, July 27, 2020 11:15
To: Stephen Froehlich <[email protected]>
Cc: [email protected]
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 protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>>
Sent: Monday, July 27, 2020 10:51
To: [email protected]<mailto:[email protected]>
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!!
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: [EXTERNAL] Pgsql error in coalesce
@ 2020-07-27 17:27 Vianello, Dan A <[email protected]>
parent: Chamath Sajeewa <[email protected]>
3 siblings, 1 reply; 10+ messages in thread
From: Vianello, Dan A @ 2020-07-27 17:27 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [EXTERNAL] Pgsql error in coalesce
@ 2020-07-27 17:32 Chamath Sajeewa <[email protected]>
parent: Vianello, Dan A <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Chamath Sajeewa @ 2020-07-27 17:32 UTC (permalink / raw)
To: Vianello, Dan A <[email protected]>; +Cc: [email protected]
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 protected]>
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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Pgsql error in coalesce
@ 2020-07-27 17:33 David Raymond <[email protected]>
parent: Chamath Sajeewa <[email protected]>
3 siblings, 0 replies; 10+ messages in thread
From: David Raymond @ 2020-07-27 17:33 UTC (permalink / raw)
To: [email protected] <[email protected]>
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 protected]>
Sent: Monday, July 27, 2020 12:51 PM
To: [email protected]
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!!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Pgsql error in coalesce
@ 2020-07-27 17:42 Alvaro Herrera <[email protected]>
parent: Chamath Sajeewa <[email protected]>
3 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2020-07-27 17:42 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Pgsql error in coalesce
@ 2020-07-27 17:43 Chamath Sajeewa <[email protected]>
parent: Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Chamath Sajeewa @ 2020-07-27 17:43 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: [email protected]
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, <[email protected]> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Pgsql error in coalesce
@ 2020-07-27 19:39 David G. Johnston <[email protected]>
parent: Chamath Sajeewa <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2020-07-27 19:39 UTC (permalink / raw)
To: Chamath Sajeewa <[email protected]>; +Cc: Vianello, Dan A <[email protected]>; [email protected] <[email protected]>
On Monday, July 27, 2020, Chamath Sajeewa <[email protected]> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2020-07-27 19:39 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-27 16:51 Pgsql error in coalesce Chamath Sajeewa <[email protected]>
2020-07-27 17:05 ` Stephen Froehlich <[email protected]>
2020-07-27 17:15 ` Chamath Sajeewa <[email protected]>
2020-07-27 17:18 ` Stephen Froehlich <[email protected]>
2020-07-27 17:27 ` Vianello, Dan A <[email protected]>
2020-07-27 17:32 ` Chamath Sajeewa <[email protected]>
2020-07-27 19:39 ` David G. Johnston <[email protected]>
2020-07-27 17:33 ` David Raymond <[email protected]>
2020-07-27 17:42 ` Alvaro Herrera <[email protected]>
2020-07-27 17:43 ` Chamath Sajeewa <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox