Re: String manipulation

Pascal Polleunus wrote:
> You should be able to do that with a regular expression.
>
> CHECK (text ~ '^([0-9A-F]{2})+$')
>
> Remark: As the column is NOT NULL, I suppose that an empty string is not
> valid. If an empty string must be valid, replace the + with * ;-)

I just noticed an unusual affect. GUC regex_flavor affects CHECK
constraints even after the check constraint has been created:

test=> SET regex_flavor = 'advanced'; -- default
SET
?column?
----------
t
(1 row)

test=> SET regex_flavor = 'basic';
SET
test=> SELECT 'abc' ~ '(a|x).*';
?column?
----------
f
(1 row)

test=> SET regex_flavor = 'advanced';
SET
test=> CREATE TABLE test (x TEXT CHECK (x ~ '(a|x).*'));
CREATE TABLE
test=> INSERT INTO test VALUES ('a');
INSERT 17149 1
test=> SET regex_flavor = 'basic';
SET
test=> INSERT INTO test VALUES ('a');
ERROR: new row for relation "test" violates check constraint "test_x"

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073