Re: Problem with Check Constraint in pg_restore

Поиск

Список

Период

Сортировка

От	Alan Millington
Тема	Re: Problem with Check Constraint in pg_restore
Дата	13 июля 2009 г. 12:10:45
Msg-id	79025.60403.qm@web25405.mail.ukl.yahoo.com обсуждение
Ответ на	Re: Problem with Check Constraint in pg_restore (Tom Lane <tgl@sss.pgh.pa.us>)
Список	pgsql-general

Дерево обсуждения

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:

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [GENERAL] Problem with Check Constraint in pg_restore
To: "Alan Millington" <admillington@yahoo.co.uk>
Cc: pgsql-general@postgresql.org
Date: Monday, 13 July, 2009, 3:06 PM

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'::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))

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

В списке pgsql-general по дате отправления:

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Re: Problem with Check Constraint in pg_restore