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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
