postgresql数据库sql常用函数

常用sql写法

postgresql基本数据类型

类型	描述
INTEGER	整数类型，-2,147,483,648 到 2,147,483,647
BIGINT	大整数类型，-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
SMALLINT	小整数类型，-32,768 到 32,767
DECIMAL	精确数字类型，可定义精度和标度
NUMERIC	高精度数字类型，与 DECIMAL 相同
SERIAL	自增整数类型，等效于 INTEGER + 自增序列
BIGSERIAL	自增大整数类型，等效于 BIGINT + 自增序列
CHAR(n)	固定长度字符类型
VARCHAR(n)	可变长度字符类型，最大长度为 n
TEXT	可变长度字符类型，没有长度限制
DATE	日期类型，存储日期
TIME	时间类型，不包含日期
TIMESTAMP	时间戳类型，包含日期和时间
TIMESTAMPTZ	带时区的时间戳类型
INTERVAL	时间间隔类型
BOOLEAN	布尔类型，TRUE、FALSE 或 NULL
UUID	通用唯一标识符类型
BINARY	二进制数据类型
BYTEA	二进制数据类型，与 BINARY 功能类似
JSON	JSON 数据类型
JSONB	二进制格式的 JSON 数据类型

sql常用函数

时间常用函数

1. 获取当前时间

   SELECT CURRENT_DATE;  -- 当前日期
   SELECT CURRENT_TIME;  -- 当前时间
   SELECT CURRENT_TIMESTAMP;  -- 当前时间戳
   SELECT NOW();  -- 当前时间戳
   
   

2. 提取日期和时间(EXTRACT ,DATE_TRUNC)

   SELECT EXTRACT(YEAR FROM current_date);    -- 提取当前年份
   SELECT EXTRACT(MONTH FROM current_date);   -- 提取当前月份
   SELECT EXTRACT(DAY FROM current_date);     -- 提取当前日期
   SELECT EXTRACT(HOUR FROM current_time);    -- 提取当前小时
   SELECT EXTRACT(MINUTE FROM current_time);  -- 提取当前分钟
   SELECT EXTRACT(SECOND FROM current_time);  -- 提取当前秒
   
   SELECT DATE_TRUNC('year', current_date);    -- 截取到今年年初
   SELECT DATE_TRUNC('month', current_date);   -- 截取到本月月初
   SELECT DATE_TRUNC('day', current_date);     -- 截取到今天
   SELECT DATE_TRUNC('hour', current_time);    -- 截取到当前小时
   
   

3. 时间间隔函数（INTERVAL ）

   SELECT CURRENT_DATE + INTERVAL '1 day';    -- 明天
   SELECT CURRENT_DATE - INTERVAL '1 day';    -- 昨天
   SELECT CURRENT_DATE + INTERVAL '1 month';  -- 下个月
   SELECT CURRENT_DATE - INTERVAL '1 month';  -- 上个月
   

4. 时间格式化字符串（TO_CHAR）

   SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');  -- 格式化当前时间戳
   

5. 字符串转时间（TO_DATE）

   SELECT TO_DATE('2024-07-07 00:00:00','YYYY-MM-DD');  -- 解析字符串为日期
   

时间常见用法

• 获取当月起始日期

SELECT date_trunc('month', current_date) AS start_of_month;

• 获取当月月份数字

SELECT extract(month FROM current_date) AS current_month;

• 获取年：

SELECT extract(year FROM current_date) AS current_year;

• 获取月份：

SELECT extract(month FROM current_date) AS current_month;

• 获取天：

SELECT extract(day FROM current_date) AS current_day;

• 获取小时：

SELECT extract(hour FROM current_time) AS current_hour;

• 获取分钟：

SELECT extract(minute FROM current_time) AS current_minute;

• 获取秒：

SELECT extract(second FROM current_time) AS current_second;

• 获取本年到现在的所有月份

SELECT 
    to_char(month_start, 'YYYY-MM') AS month_name
FROM  (
    SELECT
        generate_series(
            date_trunc('year', current_date),  -- 从今年年初开始
            date_trunc('month', current_date), -- 到当前月份
            '1 month'::interval                -- 以每个月为间隔
        ) AS month_start
) as months
ORDER BY 
    month_start;

• 查询近12个月（包括本月）)

     SELECT 
      to_char(month_start, 'YYYY-MM') AS month_name
  FROM (
      SELECT
          generate_series(
              date_trunc('month', CURRENT_DATE) - INTERVAL '11 months',  -- 从 12 个月前的月初开始
              date_trunc('month', CURRENT_DATE),                          -- 到本月的月初
              '1 month'::interval                                       -- 每个月递增
          ) AS month_start
  ) AS months
  ORDER BY 
      month_start;
  

判空常用函数

1. COALESCE

   -- 多个字符串，返回一个非空字符串
   COALESCE(expression1, expression2, ..., expressionN)
   
   SELECT COALESCE(description, '这是一个空字符串') AS middle_name
   FROM common_server.smx_common_sql;
   
   

2. IS NULL和IS NOT NULL

   SELECT * FROM my_table WHERE column1 IS NULL;
   SELECT * FROM my_table WHERE column1 IS NOT NULL;
   

3. case

   SELECT CASE WHEN column1 IS NULL THEN 'Default Value' ELSE column1 END FROM my_table;
   

类型转行函数

1. 使用cast

   cast (num_no as VARCHAR)
   

2. 使用 :: 操作符

   expression::VARCHAR
   

3. 使用 TO_CHAR 和 TO_DATE 函数

4. 使用 ARRAY 类型和 UNNEST 函数

   SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;
   
   SELECT UNNEST(ARRAY[1, 2, 3, 4, 5]) AS number;
   

5. 使用case

   CASE
       WHEN condition THEN result
       [WHEN ...]
       [ELSE result]
   END
   

6. 自定义函数

   CREATE OR REPLACE FUNCTION to_uppercase(input TEXT)
   RETURNS TEXT AS $$
   BEGIN
       RETURN UPPER(input);
   END;
   $$ LANGUAGE plpgsql;
   
   SELECT to_uppercase('hello world');
   

多行转行函数

1. STRING_AGG

   STRING_AGG(expression , separator ORDER BY employee_name ASC) -- expression 参数，separator 拼接符号
   

2. ARRAY_AGG

   SELECT ARRAY_AGG(employee_name ORDER BY employee_name ASC ) AS employee_names FROM employees;
   

3. ---

   将数组转换为字符串

   SELECT ARRAY_TO_STRING( ARRAY_AGG(num_no ORDER BY num_no desc  ),separator ) 
   FROM my_table; -- 将数组转为 指定分隔符的数组
   

4. UNNEST

   SELECT UNNEST(ARRAY[1, 2, 3, 4]);	
   

5. LISTAGG pg:13以上可用

   LISTAGG(expression, separator) WITHIN GROUP (ORDER BY clause)
   

操作字符串（时间）函数

1. **SUBSTRING** 函数

   SUBSTRING(string FROM start [FOR length])
   
   SELECT SUBSTRING('Hello, world!' FROM 1 FOR 5);  -- 结果: 'Hello' 
   SELECT SUBSTRING('Hello, world!' ,1 , 5);  -- 结果: 'Hello'
   -- 多种写法 从第一个开始截取5个字符串
   

2. **LEFT** 函数

   LEFT(string, n)
   
   SELECT LEFT('Hello, world!', 5);  -- 结果: 'Hello'
   -- 从左边第一个开始 ，截取指定个数字符
   

3. **RIGHT** 函数

RIGHT(string, n)

SELECT RIGHT('Hello, world!', 6);  -- 结果: 'world!'
-- 从右边第一个开始 ，截取指定个数字符

4. **POSITION** 函数

   POSITION(substring IN string)  -- substring 在 string中的位置信息
   
   SELECT POSITION(',' IN 'Hello, world!');  -- 结果: 6
   

5. **SPLIT_PART** 函数

   SPLIT_PART(string, delimiter, field)
   
   SELECT SPLIT_PART('Hello,world,here', ',', 2);  -- 结果: 'world'
   -- 把字符串按照delimiter 分割 按照field 取字符字串 从1开始
   

6. **OVERLAY** 函数

   OVERLAY(string PLACING substring FROM start [FOR length]) 
   -- 从start 开始 截取length个字符 替换为substring
   
   SELECT OVERLAY('Txxxxas' PLACING 'ex' FROM 2 FOR 4);  -- 结果: 'Texas'
   
   

7. **TRIM** 函数

   TRIM([BOTH | LEADING | TRAILING ] [characters FROM] string)
   -- BOTH 左右两边 LEADING 左边 TRAILING 右边 匹配到characters 转为空
   
   SELECT TRIM(BOTH ' ' FROM '   Hello, world!   ');  -- 结果: 'Hello, world!'
   SELECT TRIM(LEADING 'H' FROM 'Hello, world!');     -- 结果: 'ello, world!'
   SELECT TRIM(TRAILING '!' FROM 'Hello, world!!!');  -- 结果: 'Hello, world'
   

8. **LPAD** 和 **RPAD** 函数

   LPAD(str, length, fill)
   -- 指定str 填充fill 在左边到指定字符长度 
   RPAD(str, length, fill)
   -- 指定str 填充fill 在右边边到指定字符长度 
   
   SELECT LPAD('Hello', 10, '-');  -- 结果: '-----Hello'
   SELECT RPAD('Hello', 10, '-');  -- 结果: 'Hello-----'
   

9. **CONCAT**: 连接字符串

   SELECT CONCAT('Hello', ', ', 'world!');  -- 'Hello, world!'
   

10. **LENGTH**: 返回字符串长度

    SELECT LENGTH('Hello, world!');  -- 13
    

11. UPPER / LOWER: 转换为大写/小写

    SELECT UPPER('Hello, world!');  -- 'HELLO, WORLD!'
    SELECT LOWER('Hello, world!');  -- 'hello, world!'
    

12. REPLACE: 替换字符串中的子串

    SELECT REPLACE('Hello, world!', 'world', 'PostgreSQL');  -- 'Hello, PostgreSQL!'
    

13. REVERSE: 反转字符串

    SELECT REVERSE('Hello, world!');  -- '!dlrow ,olleH'
    

14. INITCAP: 每个单词的首字母大写

    SELECT INITCAP('hello, world!');  -- 'Hello, World!'