Thread: max() unexpected type conversion

Hello.

I'm having some trouble with an unexpected conversion performed by the max() 
function.
If max() operates on a 'varchar' type column, the result appears with 'text' 
type.
Should I expect this behaviour in the current and later versions ?

I mention this because I'm using ODBC in VB, and this type conversion has a 
bad outcome, because 'text' columns require a specific treatment (a second 
reading of the column contents returns null). Therefore, if this conversion 
is beeing made as a result of a design option, I'll have to use a different 
approach (something like 'select ... order by ... desc limit 1').

A small sample follows, giving a 'text' type result:


create table test
( testcol varchar(4) not null
);

insert into test (testcol) values ('A');
insert into test (testcol) values ('A');
insert into test (testcol) values ('B');
insert into test (testcol) values ('B');
insert into test (testcol) values ('C');
insert into test (testcol) values ('C');

select max(testcol) from test;



Thank you

Hélder M. Vieira

"Hélder M. Vieira" <hmv@mail.telepac.pt> writes:
> If max() operates on a 'varchar' type column, the result appears with 'text' 
> type.
> Should I expect this behaviour in the current and later versions ?

This is true of just about everything, not only max() -- there are *no*
functions yielding varchar in recent PG releases.
        regards, tom lane

> This is true of just about everything, not only max() -- there are *no*
> functions yielding varchar in recent PG releases

Thanks for the information. I added an explicit cast to the max() output and
it works fine.

Anyway, for those using VB, this is potentially hazardous. Knowing that the
underlying data is of type 'varchar', one has to be always very cautious
about the 'text' conversion, otherwise all readings after the first will
return null instead of valid data.



Helder M. Vieira