Postgresql不常用函数语法汇总

以下是在国产化适配国产中总结的一些mysql转pg的语法函数，分享出来供大家参考。

1. 在mysql中，我们习惯使用 DATE_FORMAT 函数对时间日期进行格式化，在pg中我们使用 to_char 代替

--- DATE_FORMAT用法
SELECT DATE_FORMAT(my_datetime, '%Y-%m-%d') AS formatted_date FROM my_table;

--- to_char用法
SELECT to_char(my_datetime, 'YYYY-MM-DD') AS my_table;

2.mysql里的 GROUP_CONCAT 用于将多行的值连接成一个字符串，pg中使用 string_agg代替

--- GROUP_CONCAT的用法
GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

SELECT class, GROUP_CONCAT(name) AS student_names
FROM students
GROUP BY class;

--- string_agg的用法
string_agg(expression, delimiter)

SELECT class, string_agg(name, ',') AS student_names
FROM students
GROUP BY class;

3.mysql的 DATE_SUB 函数用于从一个日期中减去指定的时间间隔，pg中使用间隔（interval）来进行日期的减法

---使用DATE_SUB从当前日期减去 5 天
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY);
SELECT DATE_SUB('2023-10-23', INTERVAL 3 MONTH);
SELECT DATE_SUB(your_date_column, INTERVAL 1 YEAR);

---使用interval从当前日期减去 5 天
SELECT current_date - INTERVAL '5 days' AS new_date;
SELECT current_date - INTERVAL '3 months' AS new_date;
SELECT '2023-10-23'::date - INTERVAL '1 years' AS new_date;

4.mysql的 find_in_set 函数用于查找一个字符串在逗号分隔的字符串列表中的位置，pg中使用ANY

--- FIND_IN_SET用法
FIND_IN_SET(search_string, string_list)

SELECT name
FROM students
WHERE FIND_IN_SET('swimming', hobbies) > 0;

--- 使用ANY代替

SELECT 
    CASE 
        WHEN 'your_search_string' = ANY (string_to_array(your_column, ',')) THEN TRUE
        ELSE FALSE
    END AS is_present
FROM your_table;

5.mysql的 on duplicate update 在pg中使用 ON CONFLICT DO UPDATE 代替

--- mysql用法
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2, ...;

--- pg用法
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...;