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/
