Обсуждение: pg_restore fails when restoring a database view

Hi,

I have created a database view by :

CREATE VIEW V_EDUCATION_LEVEL ( id, description ) AS
SELECT 1, 'College or above'
UNION ALL
SELECT 2, 'Secondary';

Then I pg_dump(ed) the database by specifying a tar format and tried to
pg_restore to a destination database. However, I encountered the following
error:

pg_restore: creating VIEW v_education_level
pg_restore: [archiver (db)] could not execute query: ERROR:  CREATE TABLE:
attribute "?column?" duplicated
pg_restore: *** aborted because of error

I am running Postgres 7.2.2.

Thanks,

Pius






_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

Your problem is that the dump file doesn't specify the column name for
the view. When you go to restore it, having no names to reference, it
gives both columns the name ?column?, which errors out. This is fixed in
7.3 if you use the tar format (though oddly you'll get a different error
with plain text dumps). If you can't upgrade, you can rewrite the view
to use alias's in the select:


CREATE VIEW "V_EDUCATION_LEVEL" AS
SELECT 1 AS id, 'College or above' AS description
UNION
SELECT 2 AS id, 'Secondary' AS description;

Robert Treat

On Fri, 2003-02-14 at 16:16, Pius Chan wrote:
> Hi,
>
> I have created a database view by :
>
> CREATE VIEW V_EDUCATION_LEVEL ( id, description ) AS
> SELECT 1, 'College or above'
> UNION ALL
> SELECT 2, 'Secondary';
>
> Then I pg_dump(ed) the database by specifying a tar format and tried to
> pg_restore to a destination database. However, I encountered the following
> error:
>
> pg_restore: creating VIEW v_education_level
> pg_restore: [archiver (db)] could not execute query: ERROR:  CREATE TABLE:
> attribute "?column?" duplicated
> pg_restore: *** aborted because of error
>
> I am running Postgres 7.2.2.
>
> Thanks,
>
> Pius