schema error upgrading from 7.1 to 7.2

From: Vivek Khera <khera(at)kcilink(dot)com>
To: pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: schema error upgrading from 7.1 to 7.2
Date: 2002-02-20 21:00:22
Message-ID: 15476.3686.626557.397236@onceler.kciLink.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs 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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2002-02-20 21:29:59 Bug #598: optimizer: convert 'IN' to join
Previous Message Andy Marden 2002-02-20 20:37:12 Re: Dates and year 2000

Browse pgsql-general by date

  From Date Subject
Next Message Jean-Michel POURE 2002-02-20 21:01:13 Re: upgrade to 7.2 & pgdumpall
Previous Message Cindy 2002-02-20 20:53:20 documention on psql?