Re: Understanding "unknown" data type

Thanks Tom.

On 04/07/2011 11:29 AM, Tom Lane wrote:
> Steve Crawford<scrawford@pinpointresearch.com>  writes:
>> 1. Where can I read up on the purpose and properties of a data-type of
>> unknown?
> It's the type initially imputed to unadorned string literals and NULL...
Is there a place in the documentation that explains this? I haven't
found it so far.

> ...It can be cast to anything, but that's a hardwired behavior not
> something listed in pg_cast.
Hmmm. Not *anything* - at least not directly:

steve=# create table foobar as select '1' as somedata;
WARNING:  column "somedata" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
SELECT 1
                    ^
steve=# select somedata::int from foobar;
ERROR:  failed to find conversion function from unknown to integer

...
steve=# create table foobar as select '2011-04-07' as somedata;
WARNING:  column "somedata" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
SELECT 1
steve=# select somedata::date from foobar;
ERROR:  failed to find conversion function from unknown to date


> Throwing an error has been discussed, but it seems likely to break
> things for some people.

A GUC? (Like we need more of those :))
> ...but there aren't terribly bad side-effects
> (other than not being able to operate on that table column).  The
> cases that are of practical interest tend to involve label columns in
> views.
Not sure how the variety of front-ends might behave when fed an
"unknown". It's pretty easy to accidentally create such a column and
have it lurking waiting to cause downstream trouble.

For now, I have a daily alert script that warns me if a dev has
accidentally done this.

Cheers,
Steve