Understanding "unknown" data type

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Understanding "unknown" data type
Date: 2011-04-07 18:01:04
Message-ID: 4D9DFBE0.9000906@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Curvey 2011-04-07 18:19:02 Re: postgres segfaulting on pg_restore
Previous Message Chris Curvey 2011-04-07 17:54:00 Re: postgres segfaulting on pg_restore