pg_restore warning column X has type unknown

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?

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.

StackExchange Database Administrators Q#106372, answer score: 9