Re: pg_restore fails when restoring a database view

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