조건식
조건식 (CASE·COALESCE·NULLIF·GREATEST/LEAST)
쿼리를 짜다 보면 "이 값이 NULL이면 기본값을, 아니면 원래 값을" 같은 분기 로직이 필요할 때가 있어요. 다른 언어의 if/else처럼 쓸 수 있는 게 바로 조건식(conditional expression)이에요. PostgreSQL은 SQL 표준 조건식인 CASE, COALESCE, NULLIF, 그리고 GREATEST/LEAST를 제공해요. 이 페이지에서 각각의 쓰임을 예시와 함께 볼게요.
조건식의 쓰임이 부족할 때는?
이 조건식들로 해결할 수 없는 요구라면, 더 표현력 있는 언어로 서버 측 함수(server-side function)를 작성하는 것도 고려해 볼 만해요.
또 하나 명심할 점이 있어요. COALESCE, GREATEST, LEAST는 문법상 함수처럼 보이지만 일반 함수가 아니에요. 그래서 명시적인 VARIADIC 배열 인자와 함께 쓸 수 없어요.
CASE — 가장 일반적인 조건식
SQL의 CASE 표현식은 다른 언어의 if/else와 비슷한 범용 조건식이에요.
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
CASE 절은 표현식이 유효한 곳이라면 어디서든 쓸 수 있어요. 각 condition은 boolean 결과를 반환하는 표현식이에요. 어떤 조건이 참이면 그 뒤의 result가 CASE 표현식의 값이 되고, 나머지 부분은 처리되지 않아요. 참인 조건이 없고 ELSE가 있으면 그 결과가, ELSE도 생략됐다면 결과는 NULL이에요.
예시로 확인해 볼게요.
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
result 표현식들의 데이터 타입은 모두 하나의 출력 타입으로 변환 가능해야 해요.
CASE에는 "단순(simple)" 형태도 있어요.
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
첫 expression을 한 번 계산해 각 WHEN의 value와 비교하다가 같은 걸 찾아요. 이건 C의 switch 문과 비슷해요. 위 예시를 단순 CASE로 다시 쓰면 이렇게 되죠.
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;
CASE 표현식은 결과를 정하는 데 필요하지 않은 하위 표현식은 평가하지 않아요. 예를 들어 0으로 나누는 실패를 이렇게 피할 수 있어요.
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
다만 "CASE는 필요한 하위 표현식만 평가한다"는 원칙이 항상 절대적인 것은 아니라는 게 문서의 설명이에요. 예를 들어 상수 1/0 하위 표현식은 계획(planning) 단계에서 0으로 나누기 오류를 일으키기 쉬워요. 런타임에 절대 진입하지 않을 CASE 분기에 있더라도 말이죠.
COALESCE — NULL을 첫 번째로 채우는 방법
COALESCE(value [, ...])
COALESCE는 인자 중 NULL이 아닌 첫 번째 값을 반환해요. 모든 인자가 NULL일 때만 NULL을 반환하지요. 데이터를 조회해 화면에 보여줄 때 NULL에 기본값을 대신 넣는 용도로 자주 쓰여요.
SELECT COALESCE(description, short_description, '(none)') ...
이 예시는 description이 NULL이 아니면 그것을, NULL이면 short_description을, 그것마저 NULL이면 (none)을 반환해요.
인자들은 공통 데이터 타입으로 변환 가능해야 하며, 그 타입이 결과 타입이 돼요. COALESCE도 CASE처럼 결과 결정에 필요한 인자만 평가하고, 첫 NULL이 아닌 인자 오른쪽의 인자는 평가하지 않아요. 다른 DB에서 쓰는 NVL·IFNULL과 비슷한 기능을 제공하는 SQL 표준 함수예요.
NULLIF — COALESCE의 역연산
NULLIF(value1, value2)
NULLIF는 value1이 value2와 같으면 NULL을, 다르면 value1을 반환해요. 앞의 COALESCE 예시와 정반대 동작을 만들 때 쓰죠.
SELECT NULLIF(value, '(none)') ...
value가 (none)이면 NULL을, 아니면 value를 반환해요.
두 인자는 서로 비교 가능한 타입이어야 해요. 정확히는 value1 = value2라고 쓴 것처럼 비교되므로 적절한 = 연산자가 있어야 합니다. 결과 타입은 첫 인자 타입과 같지만 미묘한 점이 있어요. 실제로 반환되는 것은 암시된 = 연산자의 첫 번째 인자라서, 어떤 경우엔 두 번째 인자 타입에 맞춰 승격(promote)될 수 있어요. 예를 들어 NULLIF(1, 2.2)는 numeric을 결과로 내요. integer = numeric 연산자가 없고 numeric = numeric만 있기 때문이에요.
GREATEST와 LEAST — 최댓값·최솟값
GREATEST(value [, ...])
LEAST(value [, ...])
GREATEST는 인자 목록에서 가장 큰 값을, LEAST는 가장 작은 값을 골라요. 몇 개든 표현식을 받을 수 있어요. 모든 표현식은 공통 데이터 타입으로 변환 가능해야 하고 그 타입이 결과 타입이 돼요.
인자 목록의 NULL 값은 무시돼요. 결과가 NULL이 되는 건 모든 표현식이 NULL로 평가될 때뿐입니다. (이건 SQL 표준과 다른 점이에요. 표준에 따르면 어떤 인자든 NULL이면 반환값이 NULL이고, 일부 DB는 그렇게 동작해요.)