Postgresql - Domain for Canadian Postal Code

Question

I am trying to implement a domain to check for proper postal codes for Canada. I wrote the following but i get a constraint error. Can you please tell me where i may be wrong? i would really appreciate it.

CREATE DOMAIN can_postal_code varchar(6) 
CONSTRAINT valid_postal_code 
CHECK (VALUE ~   '^[ABCEGHJKLMNPRSTVXY]\d[ABCEGHJKLMNPRSTVWXYZ]\d[ABCEGHJKLMNPRSTVWXYZ]\d$');

What is the postgresql version? – Clodoaldo Neto Feb 17 '13 at 12:25 — Clodoaldo Neto, Feb 17 '13 at 12:25
this is my postgresql version – ueg1990 Feb 17 '13 at 19:08 — ueg1990, Feb 17 '13 at 19:08

score 1 · Answer 1 · answered Feb 17 '13 at 12:37

1

For postgresql up to 9.0 use dollar quoting:

CHECK (VALUE ~ 
    $regex$^[ABCEGHJKLMNPRSTVXY]\d[ABCEGHJKLMNPRSTVWXYZ]\d[ABCEGHJKLMNPRSTVWXYZ]\d$$regex$
);

answered Feb 17 '13 at 12:37

Clodoaldo Neto

118,695
26
233
260

what does $regex$ mean in this expression? that i want to use a regular expression?? – ueg1990 Feb 17 '13 at 19:09
You can use any string inside the double dollar including an empty one. It is just to differentiate from any other pair inside your string. That would happen at the end of your string where you have the end of string matching dollar. – Clodoaldo Neto Feb 17 '13 at 19:30
@ueg1990, check [this](http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING) article in the docs. – vyegorov Feb 17 '13 at 22:23

Postgresql - Domain for Canadian Postal Code

1 Answers1