Re: Selecting a constant question: A summary

I see... PostgreSQL wants to guess the datatype, given no clear
syntactic information, and perhaps a varchar(n) wouldn't be a valid cast
to some of the possible datatypes.

So, where x = '(1,2)' might be legal for comparing to x, but a field of
type varchar(5) might not be, as in where x = y, where y is type
varchar(5) containing '(1,2)'.

(Time values don't have this problem in pure ANSI SQL, since the literal
is TIME '12:34', but I can see for user types it might be ambiguous).

I find PostgreSQL's handling of this strange, as I come from systems
where 'xxx' is either a varchar or char type, in all contexts, and
implicit casts handle any needed conversions.
But now I understand why it does things this way.

Thanks.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, June 12, 2007 9:50 PM
To: Chuck McDevitt
Cc: Andrew Hammond; Josh Berkus; pgsql-hackers(at)postgresql(dot)org; Dann
Corbit; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question: A summary

"Chuck McDevitt" <cmcdevitt(at)greenplum(dot)com> writes:
> Just a curiosity question: Why is the type of a literal '1' "unknown"
> instead of varchar(1)?

Because, for instance, it might be intended as an integer or float or
numeric value. Change the content a little, like '(1,2)' or '12:34',
and maybe it's a point or time value. There are plenty of contexts in
which the intended type of a literal is obviously not text/varchar.

We assign unknown initially as a way of flagging that the type
assignment is uncertain. Once we have a value that we think is varchar
(a table column for instance), the rules for deciding to cast it to a
different type get a lot more stringent.

regards, tom lane