To: Carol Walter <walterc@indiana.edu>
cc: pgsql-admin@postgresql.org
Subject: Re: type unknown? 
In-reply-to: <ECE4FB45-9CD3-429F-9743-1BDA1D771942@indiana.edu> 
References: <ECE4FB45-9CD3-429F-9743-1BDA1D771942@indiana.edu>
Comments: In-reply-to Carol Walter <walterc@indiana.edu>
	message dated "Thu, 18 Sep 2008 16:29:03 -0400"
Date: Thu, 18 Sep 2008 17:01:33 -0400
Message-ID: <2235.1221771693@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Carol Walter <walterc@indiana.edu> writes:
> Postgres.  To do this, I did a pg_dumpall and I'm restoring.  I got  
> an error on the restoration as follows:

> psql:/dbsdisk/data_load/dbdev_all_080915.sql:3920581: WARNING:   
> column "collection" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.

AFAIK the only way to get that warning is with an undecorated literal
constant:

regression=# create view foo as select 'bar' as collection;
WARNING:  column "collection" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW
regression=# \d foo
        View "public.foo"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 collection | unknown | 
View definition:
 SELECT 'bar' AS collection;

... which is not what you're showing here:

> The view that was being processes when the error occurred is:

>      View "public.v_cos_master_year_count"
>     Column    |       Type        | Modifiers
> -------------+-------------------+-----------
>   collection  | text              |
>   year        | character varying |
>   entry_count | bigint            |
> View definition:
>   SELECT 'cos'::text AS collection, cos_master.year_published AS  
> "year", count(cos_master.id) AS entry_count
>     FROM cos_master
>    GROUP BY cos_master.year_published, cos_master.collection
>    ORDER BY cos_master.year_published;

Are you sure you correctly identified which view is drawing the warning?

			regards, tom lane