[sql] update set 순서 설정

update set 순서에 대하여

Postgresql 기준

여러 행을 update 시, 입력 데이터 순서대로 데이터들이 update가 진행된다.
내가 하고자 했던건 모든 datetime 컬럼의 hour 값을 1씩 더해주는 것이었다.

Ex)
before
-> 2021-12-07 16:00:00.000000
after
-> 2021-12-07 17:00:00.000000

하지만 .....

update [table] set dt = dt + interval '1 hour'

위와 같은 간단한 쿼리로 모든 dt 값의 시간을 1 더해줄 수 있었지만
나의 테이블은 id, dt에 따라 unique가 설정되어 있었기 때문에
동일 id의 데이터가 같은 dt값을 가진 데이터를 가질 수 없었다.

맞닥뜨린 ERROR

ERROR: duplicate key value violates unique constraint

update는 가장 먼저 들어온 데이터부터 시작되는데,
가장 오래된 데이터 + 1시간을 하면 그 datetime이 이미 테이블안에 존재하기 때문에
위와 같은 에러가 발생하였다.

---

따라서,
먼저 기존 데이터들의 datetime을 중복제거하여 내림차순으로 정렬하였다.

select distinct dt from [table] order by dt desc;

그 다음, for 문을 통해 내림차순 정렬한 dt를 하나씩 i에 대입하여 그 datetime에 맞는(최근 datetime)의 데이터를 update set 하였다. 아래 function 참고

해결 방법

do
$$
declare
     i timestamp;
begin
for i IN (select distinct dt from [table] order by dt desc)
loop
    update [table] set dt = dt + interval '1 hour' where dt = i;
end loop;
end;
$$;

역시 SQL은 어렵다