Home » SQL » POSTGRES » postgresql coalesce 함수로 null 값을 다른 값으로 바꿔 봅시다.

postgresql coalesce 함수로 null 값을 다른 값으로 바꿔 봅시다.

postgresql coalesce 함수는 널 값을 다른 값으로 대체하는 역할을 하는 함수입니다. 잠깐. 널 값이 무엇인가? 이 을 보시면 됩니다. 추가 예제는 다소 복잡합니다. 그러니, left outer join과 with절에 대한 이해가 있어야 합니다. 모르신다면, 그냥 coalesce를 어떤 용도로 쓰는지 짚고 넘어가셔도 됩니다.

  • sql null 값과 널 체크 하는 방법을 알아봅시다. 링크

먼저 1번째 인자로는 target 값을 받습니다. 2번째 인자로는 만약 target이 null일 때, 어떤 값으로 바꿀 것인지를 받아요. 쿼리를 보겠습니다.

[그림 1] 쿼리 1

먼저 coalesce 함수의 1번째 인자에 null을, 2번째 인자에 2를 넣었어요. target이 null입니다. 이럴 때, 2번째 인자로 넘어간 2를 돌려줍니다. 실행 결과를 볼까요?

[그림 2] 쿼리 1의 실행 결과

2가 나옵니다. 왜? 1번째 인자로 넘어간 값이 null 값 이였기 때문입니다. 이제 다음 쿼리를 보겠습니다.

[그림 3] 쿼리 2

coalesce 함수의 1번째 인자로 3, 2번째 인자로 2가 넘어왔습니다. 이 경우에는 어떻게 될까요? 3은 null이 아닙니다. 따라서, 2가 나오지 않고 그대로 3이 나올 것입니다.

[그림 4] 쿼리 2의 실행 결과

실행 결과는 3이 나옵니다.

정리하면, coalesce(param1, param2) 형태로 들어왔다고 하겠습니다. param1 이 null이면 param2 를 돌려주게 됩니다. 그렇지 않으면 param1 을 돌려주게 됩니다. null 값일 때, 0을 돌려주게 하려면 어떻게 하면 될까요? param2를 0으로 하면 됩니다.

고로, coalesce(null, 0)을 하면, 널 값이기 때문에 0이 나오게 됩니다. 이 함수는 outer join을 할 때에는 심심찮게 써먹기도 합니다. outer join을 할 때 널 값이 많이 나오기 때문입니다.


예제를 하나 보겠습니다. 예제는 2023년 6월 28일부터 2023년 7월 2일까지 등록된 책의 개수를 뽑아내는 것입니다. 등록되지 않은 날에는 0을 뽑아와야 합니다. 등록된 책에 대한 정보는 테이블 a에 있어요.

a 테이블에 있는 내용들을 모두 조회해 보면 위와 같습니다. reg_at이 timezone이 있는 timestamp 형식임에 주의하세요.

이제 각 날짜별로 몇 권의 책이 등록되었는지 카운트 해 보겠습니다. date 함수는 날짜를 뽑아오는 것입니다. 따라서, group by(reg_at)을 해 주면 됩니다. 그리고 집계 함수는 문서의 수를 세는 count(*)로 처리하면 됩니다.

다음에 2023년 6월 28일부터 2023년 7월 2일까지 날짜를 뽑아옵니다. postgres에서는 generate_series를 제공해 줍니다. 이를 이용하면 쉽게 할 수 있어요. 이제, 이 두 테이블을 join 해야 되는데요. 6월 28일부터 7월 2일까지 날짜를 뽑은 것을 왼쪽에 둡니다. count로 집계한 것을 오른쪽에 두고 left outer join을 사용하면 됩니다.

with절로 임시 테이블을 정의한 뒤에 dat_table과 stat_data를 left outer join을 해 주었습니다. cc가 널 값인 게 드문드문 보입니다. 이것은 6월 29일과 7월 1일에 등록된 책이 없기 때문입니다.

coalesce(stat_data.cc, 0)을 봅시다. 아까와 다른 점은 2023년 6월 29일의 cc값이 null값 대신 0이 나왔다는 것입니다. 이는, 널 값이 들어올 때 0을 돌려주게 했기 때문입니다.

Leave a Comment

11 + 14 =