[PostgreSQL] SQL Error [42883]: ERROR: operator does not exist: timestamp with time zone + text

** 시작 전 편의성을 위해 오라클에서 사용하는 NVL함수를 NVL라는 명칭의 function으로 다음과 같이 생성해 두었습니다. 해당 부분 참고 바랍니다. **

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

 

포스트그레에서는 NVL을 지원하지 않기 때문에 'COALESCE' 를 사용해야 합니다. 문법은 동일하고 명칭만 다릅니다.

- NVL 함수 :  NVL("값", "지정값")

       ex) NVL(TEST, 0) : TEST값이 NULL인 경우 0으로 치환

             NVL(TEST, 'NULL') : TEST값이 NULL인 경우 'NULL'이라는 문자열로 치환

 

- COALESCE 함수 :  COALESCE ("값", "지정값")

       ex) COALESCE(TEST, 0) : COALESCE값이 NULL인 경우 0으로 치환

              COALESCE (TEST, 'NULL') : TEST값이 NULL인 경우 'NULL'이라는 문자열로 치환

 

---

 

 

요즘 기존 솔루션 버전 업그레이드를 하며 DB를 Oracle에서 Postgre로 변경되어 프로시저를 수정중에 있다...

컨버전 작업을 하는데 나오는 에러가 어마무시하다!!! 후하...그 중 알아볼 에러는 요 놈!!!

 

 

이게 무슨 말인고....했더니 포스트그레에서 오라클과 다르게 trim쓰지 못한다는 에러이다.

포스트그레에서 TRIM함수를 지원하는 걸로 아는데... 싶어 공홈도 찾아봤는데

 

https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-trim-function/

PostgreSQL TRIM() Function

 

지원하잖아!!!!!!!!!!!!!!

우선 문제가 되는 프로시저의 일부 소스를 가져와 보았다.

TO_CHAR(clock_timestamp()+nvl(trim(both cur_1.TEST),'0'),'YYYYMMDD') --ERROR

 

뭐... 결론적으로는 정말 어이없게도 cur_1에서 조회해오는 TEST컬럼의 타입문제로 결론이 났는데

요 컬럼이 숫자로 정의되어있어서 trim(both ~ ) 문법은 필요가 없다는 것..... 그래서 TRIM(BOTH...) 삭제

문자열 일 때 다시만나~ 이미 1차 컨버전 된 소스라고 들었는데 엉망진창이네...ㅎㅎㅎㅎ...

 

무튼 TRIM(BOTH...)를 삭제 했더니 이번에는 다음과 같은 에러가 발생한다.

 

오라클 문법과 많이 다르구나 너...?

오라클과 다르게 포스트 그레는 Timestamp에서 숫자를 더하거나 빼면 오류가 발생한다.

그래서 연/월/일 등의 단위와 결합하여 날짜와 시간 연산이 가능한 INTERVAL타입으로 선언을 해준 후 계산을 하고자 한다.

INTERVAL타입은 컬럼 타입이 숫자인 경우 오류가 발생하여 숫자 타입의 cur_1.TEST를 문자타입으로 cast해 준 후 다시 실행해 보았다.

 

TO_CHAR(clock_timestamp()+ nvl(cast(cur_1.TEST as VARCHAR),'0'),'YYYYMMDD') --ERROR

 

 

똑같은 에러가 난다...!!!!! 마지막으로 타입선언을 추가 해 주었더니

TO_CHAR(clock_timestamp()+ nvl(cast(cur_1.TEST as VARCHAR),'0')::interval,'YYYYMMDD')

 

그랬더니 에러 없이 실행이 된다!!!! 으악 포스트그레 문법이 너무 예민해!!!!!