Re: BUG #2455: psql failing to restore a table because of a constaint violation.

"Jeff Ross" <jross(at)wykids(dot)org> writes:
> After the upgrade to 8.1.4, this script I use to sync a development database
> with our live database began failing.

> Here's the failure:

> psql:/home/_postgresql/wykids20060524.sql:84507: ERROR: new row for
> relation "Clearinghouse" violates check
> constraint "refnumber_ck"

> Here's the record it barfs on:

> RefNumber | V207.030

> "refnumber_ck" CHECK ("RefNumber"::text ~
> similar_escape('[A-Z]|[0-9]|.'::text, NULL::text))

> The value in the record cited doesn't violate the constraint,

Actually, yes it does. SIMILAR TO (specifically similar_escape()) was
broken for patterns involving | ... but now it's fixed. The previous
code failed to enforce that the pattern be a match to the entire data
string, but that is what is required by my reading of the SQL99 spec.
So your pattern really says that the data value has to be a *single*
letter, digit, or dot. See
http://archives.postgresql.org/pgsql-bugs/2006-04/msg00139.php

regards, tom lane