Re: Understanding "unknown" data type

Steve Crawford <scrawford(at)pinpointresearch(dot)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