COALESCE(a, b, c)는 인수 중 NULL이 아닌 첫 번째 값을 돌려줍니다. 그래서 COALESCE(nickname, name)은 닉네임이 있으면 닉네임을, 없으면 이름을 보여 줍니다. NULLIF(a, b)는 반대로 a가 b와 같으면 NULL을 돌려줍니다. CASE WHEN condition THEN value ... ELSE value END는 조건에 따라 값을 고릅니다.
Ben과 Chen은 닉네임이 없어서 전체 이름이 나옵니다. Ben은 전화번호도 no phone이 됩니다.
NULL을 기본값으로 바꾸기
COALESCE는 PostgreSQL의 IFNULL, ISNULL, NVL입니다. NULL이 들어간 산술 연산은 결과가 모두 NULL이므로, 계산 전에 NULL을 0으로 바꿀 때 씁니다. 행이 0개인 집계에 값을 줄 때도 씁니다.
주문 2는 COALESCE 없이는 NULL, 있으면 80이 나오고, 행이 없는 합계는 NULL 대신 0이 됩니다.
모든 인수는 하나의 타입으로 맞춰져야 합니다. 정수 컬럼에 COALESCE(quantity, 'none')을 쓰면 PostgreSQL이 'none'을 정수로 읽으려 하므로 "invalid input syntax for type integer" 오류가 납니다. 먼저 컬럼을 캐스트하세요. COALESCE(quantity::text, 'none')처럼 씁니다. 캐스트는 CAST 페이지에서 다룹니다.
NULLIF로 값을 NULL로 바꾸기
NULLIF(a, b)는 a = b이면 NULL을, 아니면 a를 돌려줍니다. 0으로 나누기를 막고, 빈 문자열을 NULL로 바꿔 COALESCE가 대체할 수 있게 합니다.
spring의 클릭률은 5입니다. summer는 "division by zero" 오류 대신 NULL을 받고, 빈 담당자는 unassigned가 됩니다.
CASE WHEN으로 값 고르기
이 형태는 조건을 순서대로 검사해 처음 맞는 값을 돌려줍니다. 아무것도 맞지 않는 행은 ELSE 값을, ELSE가 없으면 NULL을 받습니다.
ORDER BY의 CASE가 품절 상품을 맨 뒤로 보내므로 mug가 마지막에 옵니다. 식 하나를 값들과 비교하는 짧은 형태도 있습니다. CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END처럼 씁니다. 이 형태로는 NULL을 검사할 수 없습니다. NULL = NULL은 참이 아니기 때문입니다. 그럴 때는 전체 형태에서 WHEN x IS NULL을 쓰세요.
조건별로 세기
집계 함수 안의 CASE는 한 번에 범주별로 세거나 더합니다. PostgreSQL에는 같은 뜻을 더 직접적으로 쓰는 FILTER 절도 있습니다.
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
count(*) FILTER (WHERE status = 'paid') AS paid_too
FROM orders;
둘 다 결제된 주문만 셉니다. count()는 다른 행에서 CASE가 돌려주는 NULL을 건너뜁니다.
NULL을 무시하는 GREATEST와 LEAST
GREATEST(a, b, ...)와 LEAST(a, b, ...)는 가장 큰 인수와 가장 작은 인수를 돌려줍니다. PostgreSQL에서는 NULL 인수를 건너뛰고 모든 인수가 NULL일 때만 NULL을 돌려주므로, GREATEST(1, NULL, 3)은 3입니다. MySQL은 인수 중 하나라도 NULL이면 NULL을 돌려주므로 쿼리를 옮길 때 주의하세요. 전체 규칙은 조건식에 관한 PostgreSQL 문서를 보세요.
자주 묻는 질문
IFNULL, ISNULL, NVL에 해당하는 PostgreSQL 함수는 무엇인가요?
COALESCE(value, fallback)입니다. PostgreSQL에는 IFNULL(MySQL), ISNULL(SQL Server), NVL(Oracle) 함수가 없습니다. COALESCE는 표준 SQL이며 인수를 세 개 이상 받을 수도 있습니다.
PostgreSQL에서 NULL을 0으로 바꾸려면 어떻게 하나요?
컬럼을 감싸세요. COALESCE(discount, 0)처럼 씁니다. 행이 없을 때 NULL을 돌려주는 합계는 COALESCE(SUM(amount), 0)으로 씁니다.
COALESCE는 모든 인수를 계산하나요?
아니요. 인수를 순서대로 계산하다가 NULL이 아닌 첫 인수에서 멈추므로, 그 행에서는 뒤의 인수를 계산하지 않습니다.
WHERE 절에서 CASE를 쓸 수 있나요?
네. CASE 식은 WHERE, ORDER BY, 집계 함수 안처럼 값이 올 수 있는 곳이면 어디든 쓸 수 있습니다. 다만 WHERE에서는 보통 평범한 AND/OR 조건이 더 읽기 쉽습니다.