Thread: type unknown?
Greetings one and all, I am porting all my databases from a 32 bit Postgres engine to 64 bit 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. 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; There are a number of views in this particular database where "collection" is used in this way and they all get this warning. Text is a valid type, albeit nonstandard. What is triggering this warning? Thanks, Carol
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
Well, it''s a guess. A "CREATE VIEW" follows the line that specifies the warning. (An "ALTER TABLE" immediately precedes it.) This made me think that is was on a view; then I went through the views till I found one with a column called "collection". Is there another way I can isolate what's happening? Carol On Sep 18, 2008, at 5:01 PM, Tom Lane wrote: > 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
Carol Walter <walterc@indiana.edu> writes: > Well, it''s a guess. A "CREATE VIEW" follows the line that specifies > the warning. (An "ALTER TABLE" immediately precedes it.) This made > me think that is was on a view; then I went through the views till I > found one with a column called "collection". Is there another way I > can isolate what's happening? Try doing the restore with log_min_error_statement set to WARNING; that would at least confirm whether you're looking at the right view definition. BTW, what PG version is this? regards, tom lane