PostgreSQL 함수 팁 🐘

PostgreSQL 함수 팁 🐘

1. NULL과 ''(빈 문자열)은 다르다 ⚠️

PostgreSQL에서는 NULL과 ''(빈 문자열)은 다르지만,
Oracle에서는 동일하게 취급된다.

SELECT '', NULL;

2. NULL 값 처리하기 (COALESCE) 🔄

NULL 값을 특정 값으로 대체할 때 COALESCE를 사용한다.

SELECT COALESCE('', '123'), COALESCE(NULL, '123');
-- 결과: '', '123'

3. 문자 → 숫자 변환 및 예외 처리 🔢

쿼리에서 numeric 타입으로 변환할 때 ::numeric을 사용한다.

SELECT (concat('0', '')::numeric);
SELECT (concat('0', null)::numeric);

MyBatis 예시

SELECT (concat('0', #{ my_num })::numeric);

4. 숫자가 아닌 문자 제거하기 🚫

• 전화번호에서 특수 기호 제거

SELECT regexp_replace('010-5555-7777', '\D+', '', 'g');
-- 결과: '01055557777'

• 날짜 문자열에서 특수 문자(-, 공백, :) 제거

SELECT regexp_replace('2023-08-01 13:24:56', '(-| |:)', '', 'g');
-- 결과: '20230801132456'

5. 날짜 문자열 포맷 변경 📅

PostgreSQL에서 to_date()는 시, 분, 초가 무시되므로 to_timestamp()를 사용해야 한다.

SELECT
  '20230801133051' AS "변환할 문자",
  to_timestamp('20230801133051', 'YYYYMMDDHH24MISS') AS "1. 날짜로 변환",
  to_char(to_timestamp('20230801133051', 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') AS "2. 문자로 변환";

6. 형 변환 (INTEGER로) 🔄

SELECT '123'::INTEGER;

7. UPSERT 문 (ON CONFLICT) 🔄

• ON CONFLICT는 INSERT 시 중복이 발생할 경우 특정 컬럼을 업데이트하는 데 사용된다.

• seq, yea는 Primary Key(PK)로 설정되어 있어야 한다.

• 중복될 경우 DO UPDATE를 실행하여 원하는 컬럼 값을 수정할 수 있다. (기존 값 수정할 때 사용)

INSERT INTO 테이블명 (seq, yea, value)
VALUES (1, 2024, '업데이트 테스트')
ON CONFLICT (seq, yea)
DO UPDATE SET value = '업데이트 완료';

위의 쿼리를 간략히 설명해보면 Insert를 하는데 만약 ON CONFLIC의 속성값에 해당하는 내용이 그대로 존재하면 DO UPDATE SET하여 기존 값은 유지한채로 원하는 부분만 수정할 수 있다 !!!