I am running Postgres 8.1.4 on Windows XP Professional Service Pack 3.
I recently tried to make a copy of an existing database. I made a dump using
pg_dump -Fc, I created a new database from template0, and attempted to restore
into it using pg_restore. The tables were created and about half were
populated, but the restore was halted by the following error (messages from the
log):
2009-07-10 17:47:52 ERROR: new row for relation "RP_DATE" violates check
constraint "RP_DATE_check"
2009-07-10 17:47:52 CONTEXT: COPY RP_DATE, line 565:
"CB11 OLD_CI 1901-01-01 ? [OLD]: Date appointed by HM Proclamation 17 Sep 1900"
2009-07-10 17:47:52 STATEMENT: COPY "RP_DATE" (rp_id, date_type, date,
accuracy, notes) FROM stdin;
That is puzzling, as both data and constraint came from the existing database,
where they had happily coexisted. I find that I am able to insert the
complained-of row into the new database using either INSERT or COPY, so the
statement that the constraint is violated cannot be correct.
The constraint as displayed by pgAdmin III is as follows (it is the same in the
original and the restored database):
CONSTRAINT "RP_DATE_check" CHECK (date IS NULL AND accuracy = '?'::bpchar OR
date IS NOT NULL AND date > '1099-12-31'::date AND (accuracy = 'D'::bpchar OR
accuracy = 'M'::bpchar AND date::text ~~ '%-01'::text OR (accuracy =
'Y'::bpchar OR accuracy = '?'::bpchar) AND date::text ~~ '%-01-01'::text))
The solution may be to upgrade to a more recent Postgres version, but as that
is likely to involve making a database dump and restoring it, I thought I
should check whether the problem is caused by a bug which has now been fixed.
