CODEWITHSUNDEEP
postgresql
Haydos
Haydos

Reputation: 422

Constrain digits postgresql

I want to constrain a number such that it has strictly 8 digits.... no more and no less. There are leading zeros if the number is less than 8 digits long. 

ALTER TABLE mytable ADD CONSTRAINT digit_chk CHECK (mynum ~ '[0-9]{8}');

However, using the above syntax, there is no error generated when I insert a number with 9 digits:

INSERT INTO mytable (mynum) VALUES ('123456789');

Why?

I should note that there is an error generated when I insert less than 8 digits.

Upvotes: 2

Views: 1061

Answers (1)

klin
klin

Reputation: 121604

Use:

ALTER TABLE mytable ADD CONSTRAINT digit_chk CHECK (mynum ~ '^[0-9]{8}$');

Per the documentation:

Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string.

Upvotes: 4

Related Questions