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