[SQL] MySQL을 쓰다 PostgreSQL을 쓰면서 느낀 차이 (희망편 ①)

 

이 글의 개요

• 소개 : PostgreSQL을 겪어보면서 느낀 PostgreSQL의 소소한 장점을 소개한 글입니다 (주관적일 수 있음 주의)
• 예상 독자 : MySQL(혹은 표준 SQL) 에 익숙해졌다가 PostgreSQL을 쓰게 된 상황을 겪으신 분들이 공감하실 것 같아요. DB를 너무 잘 아시는 분들에게는 심심할 수 있습니다 :)
• 글의 목표 : PostgreSQL도 좋은 점이 많습니다. 낯설겠지만 얼른 친해집시다!  (그리고 더 좋은 게 있다면 자랑 부탁드립니다 🙏🏻)

---

 

목차
--- 포스팅 ① 이번 글

0. 들어가며 : 처음 PostgreSQL을 만났을 때의 낯섦 

1. PostgreSQL이 직관적으로 느껴졌던 순간들 

(1) '::' : 형 변환을 쉽게

(2) '||' : 텍스트 결합을 단번에

(3) 날짜 계산 : +-로 자연스럽게 

-- 포스팅 ② 다음 글

2. 같은 것도 쉽게 되는 것 같던 PostgreSQL의 꿀 기능들

3. 나가며 : 가까이 보면 사랑스럽다. MySQL도 그럴 것이다

 

 

0. 들어가며 : 처음 PostgreSQL을 만났을 때의 낯섦

예전에 PostgreSQL과의 첫 만남이 기억납니다. 분명 SQL을 알고 있다고 생각했는데.. 회사에서 처음 본 데이터베이스 IDE와 PostgreSQL이라는 언어의 조합은 정말 생경했었습니다. 표준 또는 MySQL 위주로 쿼리를 배워왔던 저에게 회사의 찐 쿼리들은 참 멀어 보였어요. 한동안 참 어색한 사이였습니다😳.  
 
하지만 사람은 적응의 동물이 아니던가요. PostgreSQL을 쓰면서 이 방식에 점점 익숙해졌습니다. 점차 PostgreSQL에 대한 애정이 더 깊어진 결과 이전에는 보이지 않던 PostgreSQL 만의 편리한 점을 생각해볼 수 있었습니다.
 
 

1. PostgreSQL이 직관적으로 느껴졌던 순간들

(1) '::' : 형 변환을 쉽게

형 변환이란 자료형을 바꿔주는 것을 말합니다. 문자열에서 숫자로, 정수를 실수로, 때로는 날짜(시간)을 바꾸기도 해요. 데이터베이스 테이블에 지정된 데이터 형태에 따라, 혹은 쓰임에 따라 자료형을 바꿀 때가 있습니다. 예를 들어 '2024-01-01 0:00:00'이 문자열로 지정되어 있다면, 날짜 계산을 위해 자료형을 날짜(시간)으로 바꿔줍니다. 
 
이런 예시가 있다고 할게요. 날짜-시간이 가변되는 텍스트 자료형으로 등록되어 있습니다. 

-- 공통
CREATE TABLE date_prac (
    pre_date VARCHAR(30) NOT NULL
);

INSERT INTO date_prac
VALUES
    ('2024-01-01 0:00:00');

-- MySQL에서 자료 확인 
DESC date_prac;

-- PostgreSQL에서 자료 확인
SELECT pg_typeof(pre_date)
FROM date_prac;

-- 결과 
| character varying |

 
MySQL에서는 형을 변환할 때 'CAST'를 주로 활용했습니다.
PostgreSQL도 '::' 오퍼레이터 하나로도 형을 변환해줄 수 있습니다. 

* CAST함수는 PostgreSQL에서도 쓸 수 있지만, '::' 오퍼레이터는 PostgreSQL에만 있습니다. 
* MySQL의 DATE() 함수를 쓰면 날짜만 뽑을 수 있습니다.

-- MySQL(STANDARD SQL) CAST 함수
SELECT pre_date AS text
     , CAST(pre_date AS DATETIME) AS date_with_time
     , CAST(pre_date AS DATE) AS date
     , DATE(pre_date) AS date2
FROM date_prac;

-- PostgreSQL :: 오퍼레이터
SELECT pre_date AS text
     , pre_date::TIMESTAMP AS date_with_time
     , pre_date::DATE AS date
FROM date_prac;


-- 결과 (MySQL에서는 0:00:00아래 자릿수는 안 나옴)
| text | date_with_time | date | date2 |
| :--- | :--- | :--- | :--- |
| 2024-01-01 0:00:00 | 2024-01-01 00:00:00.000000 | 2024-01-01 | 2024-01-01 |

| pg_typeof(text) | pg_typeof(date_with_time) | pg_typeof(date) |
| :--- | :--- | :--- |
| character varying | timestamp without time zone | date |

 
:: 오퍼레이터가 직관적이라고 생각한 이유는 사고의 흐름 때문입니다. 사람은 주로 왼쪽에서 오른쪽으로, 위에서 아래쪽으로 시선이 이동하는데요. 이 시선 이동과 사고 흐름 방향이 맞았습니다. 성격이 급한 저는 컬럼부터 생각하고 그 다음에 형 변환을 고려하는데요. 쿼리를 써놓고 컬럼 앞으로 가서 함수를 쓰는 것보다, 필요한 것들을 입력해놓고 끝에 가서 형 변환을 하는 것이 더 직관적으로 다가왔습니다. 
 
 

(2) '||' : 텍스트 결합을 단번에

회사에서 문자 결합을 자주 썼는습니다. 유저 이름으로 상세 페이지 주소를 뽑을 때처럼요! 티스토리 홈페이지를 예로 들어보겠습니다. 

- username : eunjipark123
- domain : tistory.com
- tistory_page : 'https://', username, '/' , 'tistory.com' 결합 -> 'https://eunjipark123/tistory.com

 
이때 MySQL보다 PostgreSQL에서 보다 직관적인 결합 방식을 보여줍니다. 

-- MySQL(STANDARD SQL) CONCAT 함수
SELECT userid
     , username
     , CONCAT('https://', username, '/' , 'tistory.com' ) AS tistory_url
FROM user_info;

-- PostgreSQL || 오퍼레이터
SELECT userid
     , username
     , 'https://' || username || '/' || 'tistory.com' AS tistory_url
FROM user_info;

-- 결과 
| userid | username | tistory_url |
| :--- | :--- | :--- |
| 1 | eunjipark123 | https://eunjipark123/tistory.com |

 
MySQL에서는 문자 결합을 위해 CONCAT() 함수를 썼습니다. 
PostgreSQL에서는 '||' 오퍼레이터로 텍스트를 연결해줍니다

* 마찬가지로 PostgreSQL 9.1이상 버전에는 CONCAT 함수가 있지만, '||' 오퍼레이터는 PostgreSQL, Oracle, SQLlite에서 쓸 수 있습니다. (MySQL애서는 OR의 의미로 ||가 쓰이기 때문에 CONCAT으로는 쓸 수 없다고 합니다.) 

 
이 표현은 세 가지 측면에서 장점이 있다고 생각했는데요.

• 첫째, 쿼리가 간결하다.
• 둘째, 생각의 흐름에 따라 자연스럽게 배치된다.
• 셋째, 확장이 쉽다였습니다.

괄호 안에 들어갈 필요 없이 '||'을 넣고 필요한 컬럼이나 문자를 써주기만 하면 되니 연결이 훨씬 편하고 간결해졌어요. 
 
 

(3) 날짜 계산 : +-로 자연스럽게 

날짜 차이를 계산하거나 날짜를 더하는 것처럼 시간 계산에서도 PostgreSQL이 직관적이라고 느낄 때가 있었어요. 
 

1️⃣ 날짜 차이 계산을  숫자처럼

고객의 방문 주기 등을 확인할 때 날짜 차이를 계산하는 경우가 있습니다. 예를 들어 RFM 세그멘테이션을 할 때는 '방문한 지 얼마나 됐는지(Recency)' 확인하기도 하는데요. 여기서 MySQL과 PostgreSQL의 차이를 볼 수 있습니다. 

-- MySQL
SELECT CAST(pre_date AS DATE) AS designated_date
     , CURRENT_DATE AS today
     , DATEDIFF(CURRENT_DATE, CAST(pre_date AS DATE)) AS datediff
     , TIMESTAMPDIFF(SECOND, pre_date, NOW()) AS timestampdiff_second
FROM date_prac;

-- 결과 
| designated\_date | today | datediff | timestampdiff\_second |
| :--- | :--- | :--- | :--- |
| 2024-01-01 | 2024-02-01 | 31 | 2678400 |

-- PostgreSQL
SELECT pre_date::DATE AS designated_date
     , CURRENT_DATE AS today
     , CURRENT_DATE - pre_date::DATE AS datediff
     , CURRENT_TIMESTAMP - pre_date::TIMESTAMP AS timestampdiff
FROM date_prac;


-- 결과
| designated\_date | today | datediff | timestampdiff |
| :--- | :--- | :--- | :--- |
| 2024-01-01 | 2024-02-01 | 31 | 0 years 0 mons 31 days 23 hours 38 mins 15.827527 secs |

 
MySQL은 DATEDIFF()나, TIMESTAMPDIFF()를 사용해서 날짜 차이를 계산해줬습니다. 
결과 차이가 있지만, 간단한 계산에선 - 를 이용해 날짜 차이를 빠르게 도출할 수 있었습니다. 

* PostgreSQL에서도 정교한 계산을 할 때는 TIMESTAMPDIFF()를 쓰는 것이 좋겠습니다. 

 
PostgreSQL의 날짜 차이 계산은 웬만하면 잊어버리기 쉽지 않다는 장점이 있었어요. 
흔히 아는 +- 라는 기호로 직관적으로 계산을 해주어 머릿속에 잘 각인됐습니다 (DATEDIFF 함수나 TIMESTAMP 함수는 인수 순서를 자꾸 까먹습니다..).

 

2️⃣ 특정 날짜를 더하거나 빼는 방법도 직관적 

"오늘로부터 한 달 후는 언제일까?'
'첫 구입 후 7일 이전에 방문한 고객은 누구일까? '
 
이런 문제를 해결할 때에도 날짜 계산이 필요합니다. 
예를 들어 한 고객이 기준 날짜(pre_date)로부터 7일 - 1달 이내에 방문했는지 플래그로 확인해본다고 하겠습니다. 

-- MySQL : DATEADD, TIMESTAMPADD

SELECT '2024-01-31' AS visit_date
     , DATE(pre_date) AS pre_date
     , DATE(DATE_ADD(pre_date, INTERVAL 7 DAY)) AS date_after_7
     , DATE(TIMESTAMPADD(MONTH, 1, pre_date)) AS date_after_1_month
     , '2024-01-31' BETWEEN DATE(DATE_ADD(pre_date, INTERVAL 7 DAY)) AND DATE(TIMESTAMPADD(MONTH, 1, pre_date)) AS is_7to1month
FROM date_prac;


-- PostgreSQL : +-, interval
SELECT '2024-01-31' AS visit_date
     , pre_date::DATE AS pre_date
     , (pre_date::DATE + 7)::DATE AS date_after_7
     , (pre_date::DATE + INTERVAL '1 MONTH')::DATE AS date_after_1_month
     , '2024-01-31'::DATE BETWEEN (pre_date::DATE + 7) AND (pre_date::DATE + INTERVAL '1 Month') AS is_7to1month
FROM date_prac;


-- 결과 
| visit_date | pre_date | date_after_7 | date_after_1_month | is_7to1month |
| :--- | :--- | :--- | :--- | :--- |
| 2024-01-31 | 2024-01-01 | 2024-01-08 | 2024-02-01 | true |

 
MySQL은 DATE_ADD(), DATE_SUB()나 TIMESTAMPADD()를 사용합니다. 
PostgreSQL 특정 날짜 + interval '숫자와 시간 단위' 를 사용합니다. 

* 단순히 DAY를 더하는 경우 interval과 단위는 생략 가능

 
DATE_ADD나 DATE_SUB, TIMESTAMPADD 함수를 썼을 때 헷갈리는 점은 어느 위치에 어느 인수를 넣어야하는지였는데요. 
반면 PostgreSQL은 "기준 날짜에 더한다 1달 간격을" 와 같이 직관적으로 해석하게 돼요(마치 영어처럼 말이쥬..?).
나중에 기억을 떠올리기에는 이 방식이 더 편리했습니다.
 
 

이번 포스팅 요약 

지금까지 PostgreSQL이 '직관적이라' 유용했던 점들을 알아보았습니다. 
요약해보면  1) 형 변환을 '::'로 쉽게 한다는 점. 2) 텍스트 결합을 '||'로 빠르고 쉽게 해결할 수 있었습니다. 3) 마지막으로 날짜 시간 계산을 할 때 +, - 처럼 인지하기 쉽게 연산할 수 있어 편리했습니다. 
 
PostgeSQL에서도 MySQL과 마찬가지로 표준 SQL 양식을 쓸 수 있습니다. 그럼에도 별도로 직관적인 연산을 지원하는 점이 인상 깊었습니다. 간단하고 빠른 결과를 도출할 때는 PostgreSQL의 보물같은 연산을 사용해보면 좋겠습니다. 
 
 
다음 포스팅에서는 PosrgreSQL이 의외로 쉽게 해결해주는 네 가지 꿀 기능을 확인해 보겠습니다. 
 
감사합니다. 
 
 

참고

(1) 형 변환

• PostgreSQL | CAST: Convert a value of One Type to Another 
• MySQL | 12.10 Cast Functions and Operators
• MySQL | MySQL DATETIME Data Type

(2) 텍스트 결합 

• PostgreSQL | CONCAT() Function
• MySQL |  12.8 String Functions and Operators

 
(3) 날짜 계산

• PostgreSQL | 9.9. Date/Time Functions and Operators(Table 9.32. Date/Time Operators) 
• MySQL | DATEDIFF() Function
• MySQL | TIMESTAMPDIFF() Function
• MySQL | TIMESTAMPADD() Function
• MySQL | DATE_ADD() Function

* 항상 노고 해주시는 ChatGPT님 감사합니다. 
 
 
+ (글또) 글에 대한 피드백도 저에게 많은 도움이 되니 언제든 피드백 부탁드려요! 

좋은 글을 써보고 싶어서 이런 저런 시도를 해보고 있습니다.
쉽게 읽히는 글을 쓰는 것이 지금 단계의 목표인데요. 
이번 글에서는 1) 글의 개요를 작성하고 2) 이해를 돕기 위한 예시를 함께 작성했어요.