[SQL] POSIX-style regular expressions

[Goran Buzic]

Hi

Can anybody help me with POSIX-style regular expression used to check rules
that new data must satisfy for an insert or update operetion.

Table was created as follows.

CREATE TABLE table_name1 (
    id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
    id2    INT NOT NULL,
    CONSTRAINT primary_key
        PRIMARY KEY (id1, id2),
    CONSTRAINT id2_exists
        FOREIGN KEY (id2) REFERENCES table_name2
        ON DELETE CASCADE
        ON INSERT CASCADE
);

Any id1 that looks like
    1.2.
    1.12.
  12.1.
  12.12.
should be inserted into table, but the insert query was rejected with an
error message

ERROR:    ExecAppend: rejected due to CHECK constraint table_name1_id1

I tested preceding regular expression with Perl and JavaScript and it worked
fine. Can I use regular expressions with CHECK parametar, and if so, how can
I make it work.

The platform PostgreSQL is installed is Solaris 8 - Intel.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org