Обсуждение: Problem with Check Constraint in pg_restore
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. |
Alan Millington <admillington@yahoo.co.uk> writes:
> CONSTRAINT "RP_DATE_check" CHECK (date IS NULL AND accuracy = '?'::bpchar OR date IS NOT NULL AND date >
'1099-12-31'::dateAND (accuracy = 'D'::bpchar OR accuracy = 'M'::bpchar AND date::text ~~ '%-01'::text OR (accuracy =
'Y'::bpcharOR accuracy = '?'::bpchar) AND date::text ~~ '%-01-01'::text))
These ~~ (LIKE) tests on date columns seem horribly unsafe. I suspect
your problem is that you're trying to load the data with DateStyle
set to some setting other than what this constraint is hard-wired
to assume. Personally I'd suggest replacing the LIKEs with something
using EXTRACTs, or some other way that doesn't depend on the textual
formatting of dates.
regards, tom lane
Thank you for that. Having done some further investigation I had concluded that the problem was probably with the LIKE (~~) comparison. I created a text dump file with the -d (use INSERT rather than COPY) option so that I could see which rows failed. All of the rows which exercised the LIKE test failed, and all those that did not succeeded. I remain a bit puzzled as to why the comparison should work one way when INSERT or COPY is invoked from pgAdmin III, and another when COPY is invoked from pg_restore or INSERT is contained in a file fed to psql. Be that as it may; I shall take your advice, and use EXTRACT instead, if that is going to be more robust. --- On Mon, 13/7/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
|