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)
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 |
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? |