57강 SQL 초석 다지기 : PostgreSQL Sequences

PostgreSQL 기초강의, PostgreSQL 기초강좌, PostgreSQL 기본강의, PostgreSQL 기본강좌

57강 SQL 초석 다지기 : PostgreSQL Sequences   

 

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

 

이번 시간에는

▶ PostgreSQL Sequences

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

 

요약: 이번 강의에서는 PostgreSQL 시퀀스와 시퀀스 객체를 사용하여 일련의 숫자를 생성하는 방법에 대해 배울 것입니다.

 

• 시퀀스는 업무에서 사용되는 각종 번호를 매길 때 유용하게 사용할 수 있습니다.
• 정의에 따르면, 수열은 정수의 순서가 있는 목록입니다. 수열의 숫자 순서는 중요합니다. 예를 들어 {1,2,3,4,5}과(와) {5,4,3,2,1}은 완전히 다른 시퀀스입니다.
• PostgreSQL의 시퀀스는 지정된 사양을 기반으로 정수 시퀀스를 생성하는 사용자 정의 스키마 바인딩 개체입니다.
• PostgreSQL에서 시퀀스를 만들려면 CREATE SEQUENCE 문을 사용합니다.

---

Introduction to PostgreSQL CREATE SEQUENCE statement   

다음은 CREATE SEQUENCE 문의 구문을 보여줍니다.

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
    [ AS { SMALLINT | INT | BIGINT } ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] 
    [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] 
    [ CACHE cache ] 
    [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ] 

 

위의 구문에서 각각의 옵션을 살펴보겠습니다.

 

 sequence_name   

CREATE SEQUENCE 절 뒤에 시퀀스 이름을 지정합니다. IF NOT EXIST는 조건부로 새 시퀀스가 존재하지 않는 경우에만 새 시퀀스를 생성합니다.
시퀀스 이름은 동일한 스키마에 있는 다른 시퀀스, 테이블, 인덱스, 뷰 또는 외부 테이블과 중복되면 안 됩니다.

스키마 (schema) 데이터 베이스의 기본 개념으로서 데이터 베이스가 대상으로 하는 실세계를 논의 영역(universe of discourse),  논의 영역에서 데이터 베이스에 필요한 정보를 추상화하는 모델을 데이터 모델(data model),  추상한 결과를 기술한 것을 개념 스키마(conceptual schema)라고 한다.  개념 스키마를 컴퓨터의 세계로 매핑한 것을 내부 스키마(internal schema)라 하고,  각 이용자 자신의 데이터 베이스를 구축하는 데는 개념 스키마를 어떻게 구성하는가가 더욱 중요한 점이다.  이용자의 데이터 시점에서 공통 항목을 추출해서 개념 스키마에 반영시키는 것이 필요하다. [네이버 지식백과] 스키마 [schema] (컴퓨터인터넷IT용어대사전, 2011. 1. 20., 전산용어사전편찬위원회)

 

 [ AS { SMALLINT | INT | BIGINT } ]   

시퀀스의 데이터 유형을 지정합니다. 올바른 데이터 유형은 SMALLINT, INT 및 BIGINT입니다. 기본 데이터 유형은 건너뛸 경우 BIGINT입니다.
시퀀스의 최솟값과 최댓값을 결정하는 시퀀스의 데이터 형식입니다.

 

 [ INCREMENT [ BY ] increment ]   

증분은 새 값을 만들기 위해 현재 시퀀스 값에 추가할 값을 지정합니다.
양수는 오름차순 시퀀스를 만들고 음수는 내림차순 시퀀스를 만듭니다.
기본 증분 값은 1입니다.

 

 [ MINVALUE minvalue | NO MINVALUE ]   

 [ MAXVALUE maxvalue | NO MAXVALUE ]   

시퀀스의 최솟값과 최댓값을 정의합니다. NO MINVALUE와 NO MAXVALUE를 사용하는 경우 시퀀스는 기본값을 사용합니다.

오름차순 시퀀스의 경우 기본 최댓값은 시퀀스의 데이터 유형에 대한 최댓값이며 기본 최솟값은 1입니다.

내림차순 시퀀스의 경우 기본 최댓값은 -1이고 기본 최솟값은 시퀀스의 데이터 유형의 최솟값입니다.

 

 [ START [ WITH ] start ]   

START 절은 시퀀스의 시작 값을 지정합니다.

기본 시작 값은 오름차순 시퀀스의 경우 최솟값이고 내림차순 시퀀스의 경우 최댓값입니다.

 

 [ CACHE cache ]   

캐시는 더 빠른 액세스를 위해 미리 할당되고 메모리에 저장되는 시퀀스 번호의 수를 결정합니다. 한 번에 하나의 값을 생성할 수 있습니다.

기본적으로 시퀀스는 한 번에 하나의 값을 생성합니다. 즉, 캐시가 없습니다.

 

 [ [ NO ] CYCLE ]    :    CYCLE | NO CYCLE   

CYCLE를 사용하면 한계에 도달한 경우 값을 다시 시작할 수 있습니다. 다음 숫자는 오름차순 시퀀스의 최솟값과 내림차순 시퀀스의 최댓값이 됩니다.

NO CYCLE를 사용하는 경우 한계에 도달하면 다음 값을 가져오려고 하면 오류가 발생합니다.

CYCLE 또는 NO CYCLE을 명시적으로 지정하지 않은 경우 NO CYCLE이 기본값입니다.

 

 [ OWNED BY { table_name.column_name | NONE } ]    

OWNER BY 절을 사용하면 테이블 열을 시퀀스와 연결하여 열 또는 테이블을 삭제할 때 PostgreSQL은 연결된 시퀀스를 자동으로 삭제합니다.

테이블의 열에 SERIAL pseudo-type을 사용하는 경우, PostgreSQL은 내부적으로 열과 연관된 시퀀스를 자동으로 생성합니다.

SERIAL을 pseudo-type이라고 하는 이유가 여기에 있습니다. 

---

PostgreSQL CREATE SEQUENCE examples   

더 잘 이해하기 위해 시퀀스를 생성하는 몇 가지 예를 들어 보겠습니다.

 

1) Creating an ascending sequence example   

이 문은 CREATE SEQUENCE 문을 사용하여 100부터 시작하여 5의 증분으로 새 오름차순 시퀀스를 만듭니다.

CREATE SEQUENCE mysequence
INCREMENT 5
START 100;  

 

시퀀스에서 다음 값을 가져오려면 nextval() 함수를 사용합니다.

SELECT nextval('mysequence'); 

문을 다시 실행하면 시퀀스에서 다음 값을 얻을 수 있습니다.

SELECT nextval('mysequence');    

 

2) Creating a descending sequence example   

다음 문은 사이클 옵션을 사용하여 3부터 1까지 내림차순 시퀀스를 생성합니다.

CREATE SEQUENCE three
INCREMENT -1
MINVALUE 1 
MAXVALUE 3
START 3
CYCLE;   

 

다음 명령문을 여러 번 실행하면 3, 2, 1부터 다시 3, 2, 1까지 숫자가 표시됩니다.

SELECT nextval('three');   

직접 해보면서 확인해 보세요.

 

3) Creating a sequence associated with a table column   

 

먼저 order_details라는 새 테이블을 만듭니다.   

CREATE TABLE order_details(
    order_id SERIAL,
    item_id INT NOT NULL,
    item_text VARCHAR NOT NULL,
    price DEC(10,2) NOT NULL,
    PRIMARY KEY(order_id, item_id)
);   

 

둘째, order_details 테이블의 item_id 열과 연관된 새 시퀀스를 만듭니다.   

CREATE SEQUENCE order_item_id
START 10
INCREMENT 10
MINVALUE 10
OWNED BY order_details.item_id;   

 

셋째, order_details 테이블에 세 개의 주문 라인 항목을 삽입합니다.   

INSERT INTO 
    order_details(order_id, item_id, item_text, price)
VALUES
    (100, nextval('order_item_id'),'DVD Player',100),
    (100, nextval('order_item_id'),'Android TV',550),
    (100, nextval('order_item_id'),'Speaker',250);   

이 문에서, 우리는 nextval() 함수를 사용하여 order_item_id 시퀀스에서 항목 ID 값을 가져옵니다.

 

넷째, order_details 테이블에서 데이터를 쿼리합니다.   

SELECT
    order_id,
    item_id,
    item_text,
    price
FROM
    order_details;           

item_id에 정의한 시퀀스 규칙으로 데이터가 입력되었습니다.

 

 

Listing all sequences in a database   

현재 데이터베이스의 모든 시퀀스를 나열하려면 다음 쿼리를 사용합니다.

SELECT
    relname sequence_name
FROM 
    pg_class 
WHERE 
    relkind = 'S';

 

Deleting sequences   

시퀀스가 테이블 열과 연결된 경우 테이블 열이 제거되거나 테이블이 삭제되면 자동으로 삭제됩니다.

DROP SEQUENCE 문을 사용하여 수동으로 시퀀스를 제거할 수도 있습니다.

DROP SEQUENCE [ IF EXISTS ] sequence_name [, ...] 
[ CASCADE | RESTRICT ];   

 

이 구문에서:

 

• 먼저 삭제할 시퀀스의 이름을 지정합니다. IF EXISTES 옵션은 시퀀스가 있는 경우 조건부 삭제합니다. 여러 개의 시퀀스를 동시에 삭제하려는 경우 쉼표로 구분된 시퀀스 이름 목록을 사용할 수 있습니다.
• 그런 다음 시퀀스에 종속된 개체와 종속된 개체 등에 종속된 개체를 반복적으로 삭제하려면 CASCASD 옵션을 사용합니다.

Delete sequence when drop table   

이 문은 테이블 order_details를 삭제합니다. 시퀀스 order_item_id는 order_details의 item_id와 연결되므로 자동으로 삭제됩니다.

DROP TABLE order_details;   

---

이번 강의에서 PostgreSQL 시퀀스와 시퀀스 개체를 사용하여 시퀀스 목록을 생성하는 방법에 대해 배웠습니다.

 

다음 강의에서 만나요.

---

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

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