53강 SQL 초석 다지기 : PostgreSQL User-defined Data Types

PostgreSQL 강의, PostgreSQL 강좌, SQL 무료강의, SQL 무료강좌, SQL 기초강의 , SQL 기초강좌

53강 SQL 초석 다지기 : PostgreSQL User-defined Data Types   

SQL 초석 다지기 53강 시작합니다.

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

이번 시간에는

▶ PostgreSQL User-defined Data Types

에 대해서 알아보는 시간입니다.

 

요약: 이번 강의에서는 CREATE DOMAIN 및 CREATE TYPE 문을 사용하여 PostgreSQL 사용자 정의 데이터 유형을 생성하는 방법을 배우게 됩니다.

---

Introduction to PostgreSQL User-defined Data Types   

기본 제공 데이터 유형 외에도 PostgreSQL에서는 다음 명령문을 통해 사용자 정의 데이터 유형을 생성할 수 있습니다.

● CREATE DOMAIN은 NOT NULL, CHECK 등의 제약 조건을 가진 사용자 정의 데이터 유형을 생성합니다.
● CREATE TYPE은 저장 프로시저에 사용되는 복합 유형을 반환된 값의 데이터 유형으로 만듭니다.

 

PostgreSQL CREATE DOMAIN statement   

PostgreSQL의 도메인은 NULL 및 CHECK가 아닌 선택적 제약 조건이 있는 데이터 유형입니다. 도메인은 스키마 범위 내에서 고유한 이름을 가집니다.

도메인은 공통 제약 조건이 있는 필드 관리를 중앙 집중화하는 데 유용합니다. 예를 들어 일부 테이블에는 NULL과 공백을 사용할 수 없는 동일한 열이 있을 수 있습니다.

다음 문은 mailing_list라는 이름의 테이블을 만듭니다.

CREATE TABLE mailing_list (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    CHECK (
        first_name !~ '\s'
        AND last_name !~ '\s'
    )
); 

이 테이블에서 first_name 열과 last_name 열은 모두 null과 공백을 사용할 수 없습니다. CHECK 제약 조건을 정의하는 대신 contact_name 도메인을 생성하여 여러 열에 재사용할 수 있습니다.

다음 문은 CREATE DOMAIN을 사용하여 VARCHAR 데이터 형식을 사용하여 contact_name이라는 새 도메인을 생성하며 NULL과 공백을 사용할 수 없습니다.

CREATE DOMAIN contact_name AS 
   VARCHAR NOT NULL CHECK (value !~ '\s'); 

그리고 contact_name을 first_name 열과 last_name 열의 데이터 유형으로 일반적인 기본 제공 유형으로 사용합니다.

CREATE TABLE mailing_list (
    id serial PRIMARY KEY,
    first_name contact_name,
    last_name contact_name,
    email VARCHAR NOT NULL
);  

 

 

다음 문은 mailing_list 테이블에 새 행을 삽입합니다.

INSERT INTO mailing_list (first_name, last_name, email)
VALUES('Jame V','Doe','jame.doe@example.com');  

이름에 공백이 포함되어 있기 때문에 PostgreSQL에서 다음 오류가 발생했습니다.

다음 문은 contact_name 유형의 제약 조건을 위반하지 않기 때문에 작동합니다.

INSERT INTO mailing_list (first_name, last_name, email)
VALUES('Jane','Doe','jane.doe@example.com');  

 

도메인을 변경하거나 제거하려면 ALTER DOMAIN 또는 DROP DOMAIN을 각각 사용합니다.
현재 데이터베이스의 모든 도메인을 보려면 다음과 같이 \dD 명령을 사용합니다.

sql shell(psql)에서 \dD 명령을 사용하면 나온다.

dvdrental 샘플에서 생성했던 year도 같이 보인다.

 

Getting domain information   

특정 스키마의 모든 도메인을 가져오려면 다음 쿼리를 사용합니다.

SELECT typname 
FROM pg_catalog.pg_type 
  JOIN pg_catalog.pg_namespace 
   ON pg_namespace.oid = pg_type.typnamespace 
WHERE 
typtype = 'd' and nspname = '<schema_name>';  

 

다음 문은 현재 데이터베이스의 공용 스키마에 있는 도메인을 반환합니다.

SELECT typname 
FROM pg_catalog.pg_type 
  JOIN pg_catalog.pg_namespace 
   ON pg_namespace.oid = pg_type.typnamespace 
WHERE 
typtype = 'd' and nspname = 'public';   

---

PostgreSQL CREATE TYPE   

CREATE TYPE 문을 사용하면 함수의 반환 형식으로 사용할 수 있는 복합 형식을 만들 수 있습니다.

film_id, title 및 release_year와 같은 여러 값을 반환하는 함수를 사용하려고 합니다. 첫 번째 단계는 다음과 같은 유형(예: film_summary)을 만드는 것입니다.

CREATE TYPE film_summary AS (
    film_id INT,
    title VARCHAR,
    release_year SMALLINT
);   

이 작업은 시스템 카탈로그를 건드리는 작업이라 일부 툴에서는 지원되지 않을 수도 있습니다. 저는 datagrip을 사용했습니다.

 

둘째, film_summary 데이터 형식을 함수의 반환 형식으로 사용합니다.

CREATE OR REPLACE FUNCTION get_film_summary (f_id INT) 
    RETURNS film_summary AS 
$$ 
SELECT
    film_id,
    title,
    release_year
FROM
    film
WHERE
    film_id = f_id ; 
$$ 
LANGUAGE SQL;  

 

셋째, get_film_summary() 함수를 호출합니다.

SELECT * FROM get_film_summary (40);  

사용자 정의 유형을 변경하려면 ALTER TYPE 문을 사용합니다. 사용자 정의 유형을 제거하려면 DROP TYPE 문을 사용합니다.

psql 프로그램을 사용하는 경우 \dT 또는 \dT+ 명령을 사용하여 현재 데이터베이스의 모든 사용자 정의 유형을 나열할 수 있습니다.

윈도우 시작메뉴에서 postgresql 15 - > sql shell(psql) 실행

접속 후 명령을 실행해보면 된다.

---

이번 강의에서는 CREATE DOMAIN 및 CREATE TYPE 문을 사용하여 PostgreSQL 사용자 정의 유형을 만드는 방법에 대해 배웠습니다.

 

data type에 대한 강의는 마치고 다음 시간에는 테이블 관리에 대한 강의를 시작합니다.

---

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.