(Oracle PostgreSQL변환) Oracle의 nvl함수 PostgreSQL의 coalesce함수로 대체하기

본 글에서는 Oralce의 nvl함수를 대체하는 PostgresQL함수를 소개합니다.

Oracle nvl함수

구문 형식은 다음과 같습니다.

NVL(item1, item2)

item1값이 NULL이 아니면 item1값을, NULL이면 item2값을 취득합니다.

테스트 데이터 작성하기

다음과 같이 nvl함수를 확인하는 테스트 데이터를 작성합니다.

drop table testtable;
create table testtable (
  no numeric,
  name varchar(10)
);
insert into testtable values ( 1 , 'Tom');
insert into testtable values ( 2 , NULL);
insert into testtable values ( 3 , 'John');
insert into testtable values ( 4 , 'Smith');
insert into testtable values ( 5 , 'Jarry');

테스트 데이터 확인하기

저장한 데이터는 다음과 같습니다. NO항목 2의 NAME 항목 값이 NULL이기 때문에 아무것도 출력되지 않았음을 확인할 수 있습니다.

SQL> select no, name from testtable;
   NO NAME
----- ---------
    1 Tom
    2
    3 John
    4 Smith
    5 Jarry

테스트 데이터로 nvl함수실행 결과 확인하기

다음과 같이 nvl함수를 사용하여 항목의 값이 NULL인 경우 ‘NONE’ 문자열을 출력하도록 합니다.

SQL> select no, nvl(name, 'NONE') from testtable;

   NO NVL(NAME,'NONE')
----- --------------------
    1 Tom
    2 NONE
    3 John
    4 Smith
    5 Jarry

참고로 Oracle에서는 coalesce도 사용할 수 있습니다.

 select no, coalesce(name, 'NONE') from testtable;

nvl함수를 PostgreSQL용 대체 함수 coalesce

PostgreSQL에서는 다음과 같이 coalesce함수를 사용하여 Oracle nvl함수와 같은 결과를 얻을 수 있습니다.

# select no, coalesce(name, 'NONE') from testtable;
 no | coalesce
----+----------
  1 | Tom
  2 | NONE
  3 | John
  4 | Smith
  5 | Jarry
(5개 행)

Oracle에서는 ”(싱글 쿼테이션)을 NULL로 취급하지만 PostgreSQL에서는 ”(싱글 쿼테이션)과 NULL를 다른 값으로 취급합니다. Oracle과 동일한 결과를 얻고 싶다면 다음과 같이 nullif 함수를 사용하여 name항목의 값이 ”인 경우 NULL을 리턴하도록 합니다.

coalesce(nullif( name, '' ), 'NONE')

PostgresSQL에서 nvl함수를 사용하는 방법

다음과 같이 PostgreSQL에서 nvl라는 이름의 함수를 작성하여 내부에서 coalesce함수를 실행하면 Oracle nvl 함수와 같은 결과를 얻을 수 있습니다.

CREATE OR REPLACE FUNCTION nvl(text, text) RETURNS text
    AS 'select coalesce(nullif( $1, '''' ), $2);'
    LANGUAGE SQL
    IMMUTABLE;

# select no, nvl(name, 'NONE') from testtable;
 no |  nvl
----+-------
  1 | Tom
  2 | NONE
  3 | John
  4 | Smith
  5 | Jarry
(5개 행)