요즘은 ChatGPT가 알려준다고 하지만,
그러나 말을 바로 들어보면 허무함을 알 수 있다.
최근 DB 작업 중에 아래와 같은 경우가 있었는데 코드 수준이 아닌 DB 수준에서 해결하고 싶었습니다.
사용자는 항목 유형이 0인 중복 제품을 가질 수 있습니다.
단, 아이템 종류가 1인 경우 1개의 상품만 소유할 수 있으며, 이미 1인 아이템이 존재하는 경우에는 새로운 아이템으로만 교환이 가능합니다.
따라서 item_type이 0이면 삽입이 허용되고 item_type이 1이면 갱신이 필요하다.
부분 인덱스 사용
고유성 검사는 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.’ 제외된`는 `이다충돌 시` 섹션에서 충돌이 발생했을 때 삽입할 새 데이터와 충돌이 발생한 이전 데이터를 비교하고, 삽입하려는 새 데이터의 값을 참조하십시오.특수 예약어안돼. 이를 통해 기존 데이터의 값을 참조하고 충돌이 발생할 경우 업데이트할 수 있습니다.
예를 들어 쿼리에서 ON CONFLICT (id) DO UPDATE SET name = exclude.name exclude.name은 새로 삽입된 데이터의 이름 값입니다.
나타냅니다.
충돌이 있는 경우 이름 값을 기존 데이터의 id 값과 비교하여 업데이트할 수 있습니다.