Thread: use window as field name in 8.4

* Peter Lee (peter@flairpackaging.com) wrote:
> I am trying to upgrade our postgresql from 8.3 to 8.4.
>
> I found the "window" as field name makes many errors during pg_restore.
>
> - like "item.window".
>
> Is there any way I can restore the dump file from 8.3 without errors.

The best solution would probably be to rename those fields in the 8.3
database, redo the dump, and then load it into 8.4.  You could also use
the 8.4 pg_dump to connect to and dump out the 8.3 database, but if you
don't change the identifiers, anything trying to use that database will
fall over if it doesn't quote the identifiers.

"window" in 8.4 is a reserved word, which it wasn't in 8.3.

    Thanks,

        Stephen

On 06/15/2010 07:58 AM, Peter Lee wrote:

I am trying to upgrade our postgresql from 8.3 to 8.4.

I found the “window” as field name makes many errors during pg_restore.

- like “item.window”.

Is there any way I can restore the dump file from 8.3 without errors.

 

Peter

Does this happen using the 8.3 version of dump or the 8.4 version of dump. 8.4 added windowing functions which is likely the cause of your difficulty (i.e. a reserved word). The recommended procedure is to use the dump program from the newer version of PostgreSQL.

I also periodically review the reserved words at http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html and (*ahem* try to) avoid using them as names for columns/tables/...

Cheers,
Steve

Well, I guess that's the best solution: change the field name.
I hope to find some alternative solution, but I know it won't be easy.

Thank you.

Peter


-----Original Message-----
* Stephen Frost (sfrost@snowman.net)
The best solution would probably be to rename those fields in the 8.3
database, redo the dump, and then load it into 8.4.  You could also use the
8.4 pg_dump to connect to and dump out the 8.3 database, but if you don't
change the identifiers, anything trying to use that database will fall over
if it doesn't quote the identifiers.

"window" in 8.4 is a reserved word, which it wasn't in 8.3.

    Thanks,

        Stephen

* Peter Lee (peter@flairpackaging.com) wrote:
> I am trying to upgrade our postgresql from 8.3 to 8.4.
>
> I found the "window" as field name makes many errors during pg_restore.
>
> - like "item.window".
>
> Is there any way I can restore the dump file from 8.3 without errors.

On Tue, Jun 15, 2010 at 08:58:52AM -0600, Peter Lee wrote:
> I am trying to upgrade our postgresql from 8.3 to 8.4.
>
> I found the "window" as field name makes many errors during
> pg_restore.
>
> - like "item.window".
>
> Is there any way I can restore the dump file from 8.3 without
> errors.

Use 8.4's pg_dump on the running 8.3 database, and your problem will
be fixed :)

Cheers,
David (oh, and pg_dump -Fc will let pg_restore, an enormously powerful
utility, do its magic).
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate