Lists: | pgsql-general |
---|
From: | Kevin Hunter <hunteke(at)earlham(dot)edu> |
---|---|
To: | PostgresQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | COALESCE function |
Date: | 2006-12-31 05:44:22 |
Message-ID: | 45974E36.1030108@earlham.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello All,
Attempting to select two different column types with COALESCE returns
this error:
ERROR: COALESCE types smallint and character varying cannot be matched
Attempting the same thing with a CASE statement returns a similar error:
ERROR: CASE types smallint and character varying cannot be matched
I also checked Oracle's NVL command, and it throws a similar error.
Clearly, I'm not supposed to intermix two different column types into a
SELECT statement. This is because the engine needs to return a set
given the criteria, and it's difficult to do that with criteria that
/depends on the data/, yes?
Could someone explain a/the more formal reason why I can't do what I'm
trying to do?
The relevant part of my SELECT statement:
SELECT
...,
COALESCE(f.number, f.name),
...
FROM
...,
field AS f,
...
WHERE
...
;
f.number ∈ SMALLINT
f.name ∈ CHARACTER VARYING
Thank you in advance!
Kevin
P.S. If something gets lost in bit/encoding translation, ∈ = "Element Of"
From: | mike <mike(at)thegodshalls(dot)com> |
---|---|
To: | Kevin Hunter <hunteke(at)earlham(dot)edu> |
Cc: | PostgresQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: COALESCE function |
Date: | 2006-12-31 06:10:29 |
Message-ID: | 1167545429.25375.4.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Based on the below each row could end up returning a different data type
compared to a previous row for that column.
SELECT COALESCE( CAST(f.number as varchar(100)) , f.name) FROM....
Whatever f.name is set to in terms of the max length of varchar, if any,
is what f.number should be cast to.
mike
On Sun, 2006-12-31 at 00:44 -0500, Kevin Hunter wrote:
> Hello All,
>
> Attempting to select two different column types with COALESCE returns
> this error:
>
> ERROR: COALESCE types smallint and character varying cannot be matched
>
> Attempting the same thing with a CASE statement returns a similar error:
>
> ERROR: CASE types smallint and character varying cannot be matched
>
> I also checked Oracle's NVL command, and it throws a similar error.
> Clearly, I'm not supposed to intermix two different column types into a
> SELECT statement. This is because the engine needs to return a set
> given the criteria, and it's difficult to do that with criteria that
> /depends on the data/, yes?
>
> Could someone explain a/the more formal reason why I can't do what I'm
> trying to do?
>
> The relevant part of my SELECT statement:
>
> SELECT
> ...,
> COALESCE(f.number, f.name),
> ...
> FROM
> ...,
> field AS f,
> ...
> WHERE
> ...
> ;
>
> f.number ∈ SMALLINT
> f.name ∈ CHARACTER VARYING
>
> Thank you in advance!
>
> Kevin
>
> P.S. If something gets lost in bit/encoding translation, ∈ = "Element Of"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/