schema error upgrading from 7.1 to 7.2 - Mailing list pgsql-general

From Vivek Khera
Subject schema error upgrading from 7.1 to 7.2
Date
Msg-id 15476.3686.626557.397236@onceler.kciLink.com
Whole thread Raw
Responses Re: schema error upgrading from 7.1 to 7.2
List pgsql-general
During the upgrade from 7.1.3 to 7.2, I encountered an incompatibility
with the schema defs.

Prior to the upgrade, I used the 7.1.3 pg_dump program to create a
compressed dump:

pg_dump -Fc vk > vk.dump

then, using the 7.2 pg_restore, I exctracted the table schema
definitions:

pg_restore -l vk.dump >vk.1
edit vk.1 to just extract TABLE defs and ACLs (everything prior to
 DATA parts)
pg_restore -L vk.1 vk.dump > vk.schema
psql vk < vk.schema

results in the complaint about 'CURRENT_DATE' as shown in the boiled
down example below.  The line it complains about from the schema is

 "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL,

The fix seems to be to remove the single quotes around CURRENT_DATE.
pg_restore should be taught this, I think.

pg_dumpall from 7.1.3 creates the same (now invalid) schema too.

This is not documented in the list of changes to the Schema
Manipulation.



A boiled down example:

khera=> create table foo ("owner_id" integer not null, "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL);
ERROR:  Bad date external representation 'CURRENT_DATE'
khera=> create table foo ("owner_id" integer not null, "owner_lastbilled" date DEFAULT CURRENT_DATE NOT NULL);
CREATE
khera=> \d foo
                           Table "foo"
      Column      |  Type   |             Modifiers
------------------+---------+------------------------------------
 owner_id         | integer | not null
 owner_lastbilled | date    | not null default date('now'::text)

khera=> insert into foo (owner_id) values (1);
INSERT 16966 1
khera=> select * from foo;
 owner_id | owner_lastbilled
----------+------------------
        1 | 2002-02-20
(1 row)

khera=> select version();
                              version
-------------------------------------------------------------------
 PostgreSQL 7.2 on i386-unknown-freebsd4.4, compiled by GCC 2.95.3
(1 row)

pgsql-general by date:

Previous
From: Jean-Michel POURE
Date:
Subject: Re: upgrade to 7.2 & pgdumpall
Next
From: "Glen Parker"
Date:
Subject: Re: Alternate database locations