Regular expressions w localization problem

From: Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx>
To: pgsql-general(at)postgresql(dot)org
Subject: Regular expressions w localization problem
Date: 2005-11-25 17:18:55
Message-ID: m34q601w8w.fsf@conexa.fciencias.unam.mx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Együd Csaba 2005-11-25 17:19:06 Re: Syntax Error in COPY
Previous Message Tom Lane 2005-11-25 17:10:17 Re: regarding postmaster.pid file