Domain

上のメモ書き書いている時に見つけた事。どうも、PostgreSQLではデータ型を指定する事が出来るみたい。

domainと言われている機能で、その機能を使えば保存するデータを厳密に仕切ることができるみたい。以下、実験結果。

domainTest=# create domain jp_postal_code as text check (value ~ '^\\d{3}$' or value ~ '^\\d{3}-\\d{4}$');
WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...eate domain jp_postal_code as text check (value ~ '^\\d{3}$'...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...code as text check (value ~ '^\\d{3}$' or value ~ '^\\d{3}-\...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
CREATE DOMAIN
domainTest=# create table domain_test (
id serial primary key,
postal jp_postal_code);
NOTICE:  CREATE TABLE will create implicit sequence "domain_test_id_seq" for serial column "domain_test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "domain_test_pkey" for table "domain_test"
CREATE TABLE
domainTest=# insert into domain_test (postal)values('561');
INSERT 0 1
domainTest=# insert into domain_test (postal)values('56a');
ERROR:  value for domain jp_postal_code violates check constraint "jp_postal_code_check"
domainTest=# insert into domain_test (postal)values('563-1');
ERROR:  value for domain jp_postal_code violates check constraint "jp_postal_code_check"
domainTest=# insert into domain_test (postal)values('563-1333');
INSERT 0 1
domainTest=# select * from domain_test;
 id |  postal  
----+----------
  1 | 561
  4 | 563-1333
(2 rows)

domainTest=# 

参考：http://www.postgresql.jp/document/pg824doc/html/sql-createdomain.html