Question 253074 in PostgreSQL Question posted in
The official documentation can be found here.

ERROR: invalid regular expression: invalid character range in postgresql

Trying to insert right row but can’t because it violates check constraint

Table:

CREATE TABLE IF NOT EXISTS public.authors
(
    au_id character varying(11) COLLATE pg_catalog."default" NOT NULL,
    au_lname character varying(40) COLLATE pg_catalog."default" NOT NULL,
    au_fname character varying(20) COLLATE pg_catalog."default" NOT NULL,
    phone character(12) COLLATE pg_catalog."default" NOT NULL DEFAULT 'UNKNOWN'::bpchar,
    address character varying(40) COLLATE pg_catalog."default",
    city character varying(20) COLLATE pg_catalog."default",
    state character(2) COLLATE pg_catalog."default",
    zip character(5) COLLATE pg_catalog."default",
    contract bit(1) NOT NULL,
    CONSTRAINT authors_au_id_primary_key PRIMARY KEY (au_id),
    CONSTRAINT authors_au_zip_check CHECK (zip ~ similar_to_escape('[0-9][0-9][0-9][0-
9][0-9]'::text)),
    CONSTRAINT authors_au_id_check CHECK (au_id::text ~ similar_to_escape('[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'::text))
)

Insert:

INSERT into authors VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',  '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1::bit);

Error:
ERROR: invalid regular expression: invalid character range

SQL state: 2201B

Tags:

2

Answers


  1. 0

    To me, it looks as if one of constraints is wrongly set (because of CRLF in the middle of regular expression):

    CONSTRAINT authors_au_zip_check CHECK (zip ~ similar_to_escape('[0-9][0-9][0-9][0-
9][0-9]'::text)),

    If it were a single-line:

    CONSTRAINT authors_au_zip_check CHECK (zip ~ similar_to_escape('[0-9][0-9][0-9][0-9][0-9]'::text)),

    then code – (insert included) – works. Have a look at the fiddle.

    Login or Signup to reply.
  2. 0

    There is practically no reason to be messing with ranges. You can use the digit meta escape with a fixed repetition quantifier:

    CONSTRAINT authors_au_zip_check CHECK (zip ~ similar_to_escape('d{5}'::text)),
CONSTRAINT authors_au_id_check CHECK (au_id::text ~ similar_to_escape('d{3}-d{2}-d{4}'::text))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search