728x90
요즘은 웬만하면 ChatGPT가 알려주는 시대라고는 하지만,
그렇다고 곧이 곧대로 말을 들었다가는 낭패를 볼 수 있다.
최근 DB 작업 중에 아래와 같은 케이스가 있었는데, 이걸 코드단이 아니라 DB단에서 해결하고 싶어졌었다.
유저는 item type이 0인 상품은 중복으로 가질 수 있다.
단 item type이 1인 경우에는 단 한 개의 상품만 소유할 수 있으며, 만약 type이 1인 item이 이미 있을 경우에는 새 것으로 교환만 가능하다.
그러니까 item_type이 0이면 insert 허용, 1이면 update를 해야하는 상황.
Partial Index 사용법
유일성 체크는 DB에서 하면 되지 않나? 싶어서 혼자 끙끙대다가
PostgreSQL에서 버전 11 이후부터는 ON CONFLICT 절에서도 Partial Index를 사용할 수 있다는 정보를 ChatGPT로부터 입수했다. (!)
그리고 결과적으로 아래와 같이 적용했고, 잘 동작하는 것까지 확인했다.
ChatGPT덕에 지름길이 생긴건 확실한 듯 하다.
-- UNIQUE INDEX(Partial index) 생성
CREATE UNIQUE INDEX idx_unique_user_type ON users (userid, type) WHERE type = 1;
-- ON CONFLICT절에서 Partial index를 사용하고 싶다면 INDEX명으로 제약을 걸지말고 Column명으로 걸도록 한다.
INSERT INTO users (userid, type, value)
VALUES (1, 1, 'value3') -- type이 1인 행이 이미 있다면 update될 것이다.
ON CONFLICT (userid, type) WHERE type = 1
DO UPDATE SET value = EXCLUDED.value;
참고정보
Q. 위 쿼리에서 EXCLUDED의 역할은 뭘까?
A.` excluded` 는 `ON CONFLICT` 절에서 충돌이 발생했을 때 새로 삽입하려는 데이터와 충돌이 발생한 기존 데이터를 비교하고, 새로 삽입하려는 데이터의 값을 참조할 수 있는 특별한 예약어입니다. 이를 통해 충돌이 발생한 경우 기존 데이터의 값을 참조하여 업데이트할 수 있습니다.
예를 들어, ON CONFLICT (id) DO UPDATE SET name = excluded.name 쿼리에서 excluded.name은 새로 삽입하려는 데이터의 name 값을 나타냅니다. 충돌이 발생한 경우 기존 데이터의 id 값과 비교하여 name 값을 업데이트할 수 있습니다.
728x90
728x90