CREATE DOMAIN — 새 도메인 정의하기

CREATE DOMAIN 명령은 선택적인 제약 조건을 가진 데이터 타입인 도메인(domain)을 새로 정의하는 명령이에요. 여러 테이블에서 공통으로 쓰는 필드 제약을 한곳에 모아 관리할 수 있어요.

출처: PostgreSQL 문서

본문

Synopsis

CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ domain_constraint [ ... ] ]

where domain_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

Description

CREATE DOMAIN은 새 도메인을 생성해요. 도메인은 본질적으로 선택적인 제약 조건(허용되는 값 집합에 대한 제한)을 가진 데이터 타입이에요. 도메인을 정의한 사용자가 소유자가 돼요.

스키마 이름이 주어지면(예: CREATE DOMAIN myschema.mydomain ...) 도메인은 지정된 스키마에 생성돼요. 그렇지 않으면 현재 스키마에 생성돼요. 도메인 이름은 그 스키마에 존재하는 타입과 도메인들 사이에서 고유해야 해요.

도메인은 필드에 대한 공통 제약을 한곳에 추상화해 유지보수하기 좋게 만드는 데 유용해요. 예를 들어 여러 테이블이 이메일 주소 컬럼을 포함하고 있고, 모두 주소 문법을 검증하는 동일한 CHECK 제약을 요구할 수 있어요. 각 테이블의 제약을 개별적으로 설정하는 대신 도메인을 정의하세요.

도메인을 만들려면 기반 타입에 대한 USAGE 권한이 있어야 해요.

Parameters

name

생성할 도메인의 이름이에요 (선택적으로 스키마 한정).

data_type

도메인의 기반 데이터 타입이에요. 배열 지정자를 포함할 수 있어요.

collation

도메인에 대한 선택적인 콜레이션이에요. 콜레이션을 지정하지 않으면 도메인은 기반 데이터 타입과 동일한 콜레이션 동작을 가져요. COLLATE를 지정하면 기반 타입은 콜레이션 가능해야 해요.

DEFAULT expression

DEFAULT 절은 도메인 데이터 타입의 컬럼에 대한 기본값을 지정해요. 그 값은 변수가 없는 어떤 표현식이든 될 수 있어요 (하지만 서브쿼리는 허용되지 않아요). 기본 표현식의 데이터 타입은 도메인의 데이터 타입과 일치해야 해요. 기본값을 지정하지 않으면 기본값은 null 값이에요.

기본 표현식은 컬럼에 값을 지정하지 않는 어떤 삽입 연산에서도 사용돼요. 특정 컬럼에 기본값이 정의되면 도메인과 연결된 기본값을 재정의해요. 그리고 도메인 기본값은 기반 데이터 타입과 연결된 기본값을 재정의해요.

CONSTRAINT constraint_name

제약에 대한 선택적인 이름이에요. 지정하지 않으면 시스템이 이름을 생성해요.

NOT NULL

이 도메인의 값은 null이 될 수 없어요 (아래 주의 사항 참고).

NULL

이 도메인의 값은 null이 허용돼요. 이것이 기본값이에요.

이 절은 비표준 SQL 데이터베이스와의 호환성을 위해서만 의도된 것이에요. 새 애플리케이션에서는 그 사용이 권장되지 않아요.

CHECK (expression)

CHECK 절은 도메인의 값이 만족해야 하는 무결성 제약 또는 테스트를 지정해요. 각 제약은 Boolean 결과를 만드는 표현식이어야 해요. 테스트되는 값을 참조하려면 키워드 VALUE를 사용해야 해요. TRUE 또는 UNKNOWN으로 평가되는 표현식은 성공해요. 표현식이 FALSE 결과를 만들면 오류가 보고되고 그 값은 도메인 타입으로 변환될 수 없어요.

현재 CHECK 표현식은 서브쿼리를 포함할 수 없고 VALUE 외의 변수를 참조할 수 없어요.

도메인에 CHECK 제약이 여러 개 있으면 이름의 알파벳 순서로 테스트돼요. (PostgreSQL 9.5 이전 버전은 CHECK 제약에 대해 특정 발화 순서를 존중하지 않았어요.)

Notes

도메인 제약, 특히 NOT NULL은 값을 도메인 타입으로 변환할 때 확인돼요. 명목상 도메인 타입인 컬럼이 그런 제약이 있는데도 null로 읽힐 수 있어요. 예를 들어 외부 조인 쿼리에서 도메인 컬럼이 외부 조인의 null 가능 쪽에 있으면 이런 일이 생길 수 있어요. 더 미묘한 예시는:

INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));

빈 스칼라 서브-SELECT는 도메인 타입인 것으로 간주되는 null 값을 만들므로, 더 이상의 제약 검사가 적용되지 않고 삽입이 성공해요.

SQL이 null 값을 모든 데이터 타입의 유효한 값으로 일반적으로 가정하기 때문에 이런 문제를 피하기는 매우 어려워요. 따라서 모범 사례는 도메인의 제약을 null 값이 허용되도록 설계한 다음, 필요에 따라 도메인 타입 자체가 아니라 도메인 타입의 컬럼에 컬럼 NOT NULL 제약을 적용하는 거예요.

PostgreSQL은 CHECK 제약의 조건이 불변(immutable), 즉 같은 입력 값에 대해 항상 같은 결과를 줄 것이라고 가정해요. 이 가정이 CHECK 제약을 값이 처음 도메인 타입으로 변환될 때만 검사하고 다른 때는 검사하지 않는 것을 정당화해요. (이는 5.5.1 절에 설명된 테이블 CHECK 제약의 처리와 본질적으로 같아요.)

이 가정을 깨는 일반적인 방법의 예시는 CHECK 표현식에서 사용자 정의 함수를 참조한 다음 그 함수의 동작을 변경하는 거예요. PostgreSQL은 그런 것을 금지하지 않지만, 이제 CHECK 제약을 위반하는 도메인 타입의 저장된 값이 있어도 알아차리지 못해요. 이는 이후 데이터베이스 덤프 및 복원을 실패하게 만들 수 있어요. 그런 변경을 처리하는 권장 방법은 제약을 삭제하고(ALTER DOMAIN 사용), 함수 정의를 조정한 다음, 제약을 다시 추가해서 저장된 데이터에 대해 다시 검사하는 거예요.

또한 도메인 CHECK 표현식이 오류를 던지지 않도록 하는 것도 좋은 관행이에요.

Examples

이 예시는 us_postal_code 데이터 타입을 만들고 그 타입을 테이블 정의에서 사용해요. 정규식 테스트가 값이 유효한 미국 우편 번호처럼 보이는지 검증하는 데 사용돼요:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TABLE us_snail_addy (
  address_id SERIAL PRIMARY KEY,
  street1 TEXT NOT NULL,
  street2 TEXT,
  street3 TEXT,
  city TEXT NOT NULL,
  postal us_postal_code NOT NULL
);

Compatibility

CREATE DOMAIN 명령은 SQL 표준을 따르는 명령이에요.

이 명령에서 NOT NULL 문법은 PostgreSQL 확장이에요. (비복합 데이터 타입에 대해 같은 것을 표준 방식으로 작성하려면 CHECK (VALUE IS NOT NULL)이 되지만, "Notes" 절에 따르면 그런 제약은 실전에서 어쨌든 피하는 게 좋아요.) NULL "제약"은 PostgreSQL 확장이에요 (Compatibility 절도 참고).

CREATE DOMAIN — 새 도메인 정의하기