Thread: Understanding "unknown" data type

I was working on a test restore and got:
WARNING:  column "astring" has type "unknown"

The restore worked correctly - it was a warning not an error - and we
located the cause and corrected it. But I did some playing leading to
questions for which I have been unable to locate answers:

steve=# create table foobar as select 'a string' as astring, null as anull;
WARNING:  column "astring" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "anull" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
SELECT 1

steve=# \d foobar
      Table "public.foobar"
  Column  |  Type   | Modifiers
---------+---------+-----------
  astring | unknown |
  anull   | unknown |

steve=# select * from foobar;
astring  | anull
----------+-------
  a string |

steve=# insert into foobar values ('1','2');
INSERT 0 1

steve=# select * from foobar;
  astring  | anull
----------+-------
  a string |
  1        | 2

steve=# select text (astring), text (anull) from foobar;
    text   | text
----------+------
  a string |
  1        | 2

So for starters...

1. Where can I read up on the purpose and properties of a data-type of
unknown? It apparently can be cast to some other types though "unknown"
is not shown in the output of \dC.

2. Is there any setting/option that would allow me to force an error
instead of allowing creation of tables with type unknown? (And why would
such a type be allowed in a table at all - it seems like a foot-gun
waiting to happen?)

Cheers,
Steve

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
constants.  The parser normally converts these to constants of some
other type, as soon as it can figure out what type they ought to be.
In your example case, there is no context whatever that leads to
resolving a type for them, so they're still "unknown" when it comes
time to create the table.

> It apparently can be cast to some other types though "unknown"
> is not shown in the output of \dC.

It can be cast to anything, but that's a hardwired behavior not
something listed in pg_cast.

> 2. Is there any setting/option that would allow me to force an error
> instead of allowing creation of tables with type unknown? (And why would
> such a type be allowed in a table at all - it seems like a foot-gun
> waiting to happen?)

Throwing an error has been discussed, but it seems likely to break
things for some people.  If it were really a serious issue, we might
force the point anyway, 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.

There's also been some discussion of forcing the declared column type to
text in such cases.  Don't remember at the moment what the downsides of
that might be.

            regards, tom lane

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

Steve Crawford <scrawford@pinpointresearch.com> writes:
> 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.

Probably not --- it's normally just an implementation detail.

>> ...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:

Unadorned string literals and NULL constants can be cast to anything.
Once it's not a simple constant anymore, you can't do anything at all
with it.

            regards, tom lane