6

I'm doing a dump from PostgreSQL 9.1 and restoring on PostgreSQL 9.4 and getting this warning

pg_restore: WARNING:  column "foobar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

But after the restore I can verify the column "foobar" type is defined and correct.

What could be causing this warning?

  • Could you give us a \d table_name? – Vérace Jul 8 '15 at 19:55
  • the field name I changed to foobar but why would you need the table name? – Phill Pafford Jul 8 '15 at 20:32
  • Because it'll tell me if your SQL column names correspond to the actual table names. – Vérace Jul 8 '15 at 20:34
  • 2
    Vérace wasn't not asking for the table name but for the table definition, which you get with \d table_name in psql. Add the corresponding script from your dump file to make the question complete. – Erwin Brandstetter Jul 9 '15 at 3:44
7

CREATE VIEW or CREATE TABLE... AS SELECT have to infer the column types for the relation to create. Sometimes the context is not sufficient to guess a datatype, for example when it's just a string literal. In this case it's created as unknown.

Example :

test=> CREATE VIEW testview AS SELECT  'bla' AS foobar;
WARNING:  column "foobar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

Result:

test=> \d testview
    View "public.testview"
 Column |  Type   | Modifiers 
--------+---------+-----------
 foobar | unknown | 

If this view was pg_dumped and pg_restored, the column would be created as unknown with again the warning mentioned:

pg_restore: WARNING: column "foobar" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

The table/view should be created to begin with with explicit casts to columns when needed, as in

CREATE VIEW testview AS SELECT  'bla'::text AS foobar;

which we can assume is the point of that warning in the first place.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.