I'm facing a problem I have seen before but never got the time to
report until now that is biting me again. I have a table which has
some CHECK constrains using regular expressions and one check is
failing when I try to insert some valid data. The constraint
definition is:
«nombre propio válido» CHECK (nombre ~ '^[[:upper:]][[:lower:]]*([-\'. [:alpha:]]+)?$'::text)
And the data is ``José Luis''. Using a SELECT with the expression and
the data leads true as expected:
masm=# select 'José Luis' ~ '^[[:upper:]][[:lower:]]*([-\'. [:alpha:]]+)?$';
?column?
----------
t
(1 fila)
However when I try to insert one tuple using the same data it fails:
masm=# INSERT INTO persona.persona (nombre, fecha_nacimiento, sexo) VALUES ('José Luis', CAST('1979-06-15' AS date), 'm');
ERROR: el nuevo registro para la relación «persona» viola la restricción check «nombre propio válido»
Wired. A workaround that used to work was to put the select statement
and then the insert:
select 'José Luis' ~ '^[[:upper:]][[:lower:]]*([-\'. [:alpha:]]+)?$';
INSERT INTO persona.persona (nombre, fecha_nacimiento, sexo) VALUES ('José Luis', CAST('1979-06-15' AS date), 'm');
but it doesn't any more :-(. Btw, this is PostgreSQL 8.0.4, running on
Fedora Core 4.
Best Regards,
Manuel.
