Problem with Check Constraint in pg_restore

From: Alan Millington <admillington(at)yahoo(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with Check Constraint in pg_restore
Date: 2009-07-13 11:06:03
Message-ID: 130191.87444.qm@web25408.mail.ukl.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
 

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Leif B. Kristensen 2009-07-13 11:16:12 Re: ubuntu packages for 8.4
Previous Message Jasen Betts 2009-07-13 10:35:13 Re: ubuntu packages for 8.4