[DB] 데이터베이스 시퀀스 번호 조회 및 변경 방법 : CURRVAL, NEXTVAL, SETVAL

해당 글에서는 데이터베이스 시퀀스에 대해서 조회 및 변경 방법에 대해 CURRVAL, NEXTVAL, SETVAL을 이용한 방법에 대해 알아봅니다.

1) 테이블 예시

---

💡 테이블 예시

- 예시 테이블로 tb_user라는 테이블 내에 ‘tb_user_user_sq_seq’라는 이름으로 시퀀스가 존재합니다.
- 현재 시퀀스의 값은 2까지 구성이 되어 있습니다.

 
 

2) 현재 시퀀스 반환(조회)

---

💡 현재 시퀀스 반환(조회)

- 현재 시퀀스 번호를 반환하는 방법들에 대해 알아봅니다.

1. SELECT last_value FROM [시퀀스 명];

- 시퀀스 명을 기반으로 last_value 값을 조회하여 시퀀스의 마지막 값을 반환받습니다.

2. SELECT MAX(user_sq) FROM [테이블 명];

- 테이블 내에 시퀀스가 되는 컬럼의 최대값을 조회하여 시퀀스의 마지막 값을 반환받습니다.

3. SELECT CURRVAL('[시퀀스 명]');

- 시퀀스 명을 기반으로 현재 시퀀스의 값을 조회합니다.
- 단, 해당 함수의 경우는 NEXTVAL()을 수행한 이후에 해당 조회가 된다는 점이 있습니다.

-- 1. format: 시퀀스 마지막 값(현재 시퀀스 값) 조회
SELECT last_value FROM [시퀀스 명];

-- 1. 예시 : 시퀀스 마지막 값(현재 시퀀스 값) 조회
SELECT last_value FROM tb_user_user_sq_seq;

-- 2. format : 시퀀스 마지막 값(현재 시퀀스 값) 조회 
SELECT MAX([시퀀스의 테이블 컬럼 명]) FROM [테이블 명];

-- 2. 예시: 시퀀스 마지막 값(현재 시퀀스 값) 조회 
SELECT MAX(user_sq) FROM tb_user;

-- 3. format: 현재 시퀀스 값 조회(* 해당 값은 NEXTVAL() 함수를 수행 한 이후에 수행이 됩니다)
SELECT CURRVAL('[시퀀스 명]');

-- 3. 예시: 현재 시퀀스 값 조회(* 해당 값은 NEXTVAL() 함수를 수행 한 이후에 수행이 됩니다)
SELECT CURRVAL('tb_user_user_sq_seq');

 

💡 현재 시퀀스 반환(조회) 예시

- 위에서 사전에 시퀀스가 2로 지정이 되어 있기에 현재 시퀀스 역시 2의 값으로 반환해 줍니다.

 
 
 
 

3) 시퀀스 초기화

---

💡 시퀀스 초기화

- 시퀀스를 초기화하고 시퀀스를 조회하여서 초기화가 되었는지 확인해 봅니다.

1. ALTER SEQUENCE [시퀀스 명] RESTART WITH 1;

- 시퀀스 명을 기반으로 시퀀스의 시작 값을 1로 조정합니다..

2. SELECT last_value FROM [시퀀스 명];

- 시퀀스 명을 기반으로 last_value 값을 조회하여  변화된 시퀀스의 값을 반환받습니다.

-- format: 시퀀스 초기화 
ALTER SEQUENCE [시퀀스 명] RESTART WITH 1;

-- 예시: 시퀀스 초기화
ALTER SEQUENCE tb_user_user_sq_seq RESTART WITH 1;

-- format: 시퀀스 마지막 값(현재 시퀀스 값) 조회
SELECT last_value FROM [시퀀스 명];

-- 예시 : 시퀀스 마지막 값(현재 시퀀스 값) 조회
SELECT last_value FROM tb_user_user_sq_seq;

 
 

💡 시퀀스 초기화 예제

- 시퀀스를 초기화한 뒤에 다시 조회를 하였을 때, 초기화로 지정한 1의 값으로 변화된 것을 확인하였습니다.

 
 

💡 [참고] 값을 1로 초기화한 경우 INSERT를 수행하는 경우 아래와 같은 오류가 발생하였습니다.

- 이미 키가 중복된 상태로 ‘DuplicateKeyException’이 발생하였습니다. 당연히 키이자 시퀀스 인 상태에서 시퀀스를 변경하는 경우는 마지막 시퀀스로 변경하여야 합니다.

- "org.springframework.dao.DuplicateKeyException: \n### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint \"tb_user_pkey\"\n Detail: Key (user_sq)=(1) already exists.

 
 
 
 
 

4) 시퀀스 마지막 번호로 변경

---

💡 시퀀스 마지막 번호로 변경

- 위와 같이 DuplicateKeyException과 같은 오류가 발생하였을 때, 마지막 시퀀스로 이어지기 위해 이를 변경합니다.

1. ALTER SEQUENCE tb_user_user_sq_seq RESTART WITH 3;

- 사전에 마지막 시퀀스 번호를 조회하고 마지막 번호에 + 1을 하여서 다음 번호로 지정하여 변경합니다.


2. SELECT SETVAL('[시퀀스 명]', [마지막 시퀀스 번호])

- 사전에 마지막 시퀀스 번호를 조회하고 마지막 번호에 + 1을 하여서 다음 번호로 지정하여 변경합니다.

-- [CASE1] 
-- 1. 변경 이전 마지막 시퀀스 조회 
SELECT last_value FROM tb_user_user_sq_seq;

-- 1.2. 조회값을 기반으로 마지막 시퀀스로 변경
ALTER SEQUENCE tb_user_user_sq_seq RESTART WITH 3;


-- [CASE2] 
-- 1. 변경 이전 마지막 시퀀스 조회 
SELECT last_value FROM tb_user_user_sq_seq;

-- 2. format : 시퀀스 마지막 번호 변경
SELECT SETVAL('[시퀀스 명]', [마지막 시퀀스 번호])

-- 2. 예시 : 시퀀스 마지막 번호 변경
SELECT SETVAL('tb_user_user_sq_seq', 3)

 
 

💡 시퀀스 마지막 번호로 변경 예시

- 마지막 값을 찾아서 시퀀스를 변경합니다.

 
 

5) 종합

---

1. 시퀀스가 1로 잡혀 있는 경우, 시퀀스를 이어주는 방법

---

💡 시퀀스가 1로 잡혀 있는 경우, 시퀀스를 이어주는 방법

- 테이블 데이터를 이관하는 경우, 기존의 시퀀스가 이어지지 않는 경우 이를 이용하여서 시퀀스를 이어줍니다.

 

-- 1. format: 시퀀스 마지막 값(현재 시퀀스 값) 조회
SELECT last_value FROM [시퀀스 명];

-- 1. 예시 : 시퀀스 마지막 값(현재 시퀀스 값) 조회
SELECT last_value FROM tb_user_user_sq_seq;

-- 2. format : 시퀀스 마지막 번호 변경
SELECT SETVAL('[시퀀스 명]', [마지막 시퀀스 번호])

-- 2. 예시 : 시퀀스 마지막 번호 변경
SELECT SETVAL('tb_user_user_sq_seq', 3)

 
 
 

2. 시퀀스 관련 함수

---

함수	설명
CURRVAL()	현재 시퀀스의 값을 반환합니다.
NEXTVAL()	시퀀스의 다음 값을 생성하고 반환합니다.
SETVAL()	시퀀스의 값을 설정합니다.

 
 
 
 
오늘도 감사합니다. 😀