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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073