Check if data has a default length and it's numeric in sql

Question

In my table I have a column that is a code of 6 digits.

myCode CHAR(6) PRIMARY KEY CHECK ( SUBSTRING ( myCode ,1 ,1) >='0' AND
SUBSTRING ( myCode ,1 ,1) <= '9' AND ...#for all positions

Is there another method faster then this one ?

Answer 1

Using regex:

CREATE TABLE tabq(myCode CHAR(6) PRIMARY KEY CHECK (myCode ~'^([0-9]{6})$') );

If you just want to identify records which do not match this pattern, you could try:

SELECT *
FROM tabq
WHERE myCode !~ '^\\d{6}$';

Rextester Demo

Answer 2

Simpler:

mycode ~ '\d{6}'

\d is the class shorthand for digits.
Do not escape \ with another \ unless you are running with the long outdated standard_conforming_strings = off. See:

• Insert text with single quotes in PostgreSQL

No parentheses needed.
And you don't even need ^ and $ to anchor the expression to begin and end (even if that doesn't hurt) while using char(6). A rare use case where the data type is not complete nonsense. See:

• Best way to check for "empty or null value"
• Any downsides of using data type "text" for storing strings?

Shorter strings are blank-padded and don't pass the CHECK constraint.
Longer string don't pass the length specification of the type char(6).
(But careful! An explicit cast like '1234567'::char(6) silently truncates.)

CREATE TABLE tbl (mycode char(6) PRIMARY KEY CHECK (mycode ~ '\d{6}'));

I would still advise not to use the outdated type char(6). Odd behavior in corner cases. Use text instead. Then you actually need ^ and $ in the regexp expression:

CREATE TABLE tbl (mycode text PRIMARY KEY CHECK (mycode ~ '^\d{6}$'));

dbfiddle here

---

You commented (really a new question):

and what about that digits inside has to be distinct?

Enforcing unique digits in the string is not as simple. If you have the additional module intarray installed, there is an elegant solution with sort() and uniq():

CREATE TEMP TABLE tbl3 (
   mycode varchar(6) PRIMARY KEY
 , CONSTRAINT chk_6_distinct_digits
      CHECK (cardinality(uniq(sort(string_to_array(mycode, NULL)::int[]))) = 6)
);