CODEWITHSUNDEEP
sqlpostgresql
Lilac Liu
Lilac Liu

Reputation: 49

define a type involving pattern matching

If I want to define a type in PostgreSQL where the type should look like :

[A-Z]{4}[0-9]{4}

which is doing a pattern matching. What should I do?

Upvotes: 0

Views: 29

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

CREATE DOMAIN might work for you. 

create domain NEWTYPE as char(8)
  constraint newtype_regex not null check (value ~ '[A-Z]{4}[0-9]{4}');

I guessed about not null. Use it like an intrinsic data type.

create table test (
  nt NEWTYPE
);

Smoke test . . .

insert into test values ('ABCD0123'); -- Succeeds
insert into test values ('ABCD012');  -- Fails
insert into test values (null);       -- Fails
insert into test values ('abcd0123'); -- Fails

Upvotes: 1

Related Questions