Skip site navigation (1) Skip section navigation (2)

Re: COALESCE function

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 (view raw or flat)
Thread:
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/


In response to

pgsql-general by date

Next:From: Christopher BrowneDate: 2006-12-31 15:06:45
Subject: Re: Autovacuum Improvements
Previous:From: Kevin HunterDate: 2006-12-31 05:44:22
Subject: COALESCE function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group