PostgreSQL学习&实战笔记_postgresql实战专题

---

前言

记录PostgreSQL的常用函数及其在实际工作中的应用，持续更新中。

---

一、列转行

STRING_AGG(xxx, ‘间隔符’)

SELECT
    t1.mdm_code                                             AS new_material_code
   ,COALESCE(STRING_AGG(t2.old_product_code, ','), '无')    AS old_material_code
   ,t1.name                                                 AS material_name
   ,t1.active                                               AS active_flag
FROM
(
    SELECT
        id
       ,mdm_code
       ,name
       ,active
    FROM ods_xxx
    WHERE mdm_code != 'A0010000-ZZZZZZ'
    AND mdm_code IS NOT NULL
) t1
LEFT OUTER JOIN
(
    SELECT
        old_product_code
       ,new_product_code
    FROM ods_yyy
) t2
ON t1.mdm_code = t2.new_product_code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

二、时间格式转字符串

TO_CHAR(xxx, ‘yyyy-MM-dd’)

TO_CHAR("FDate", 'yyyy-MM-dd')    AS arrange_date
1

三、截取字符串

（一）SPLIT_PART(xxx, ‘根据某部分截取’, 取第几部分)

SPLIT_PART("FSalerNumber", '_', 1)    AS saler_code
1

（二）SUBSTRING(xxx, 第几位, 到第几位)

SELECT substring('123',1,2) 
1

四、授权

GRANT SELECT ON TABLE 表名 TO 用户名
1

五、常用字段类型

字符串类型：VARCHAR
小数类型：NUMERIC(xx,x)
整数类型：INTEGER
日期类型：DATE

六、求两个日期间的间隔天数

DATE_PART(‘day’,‘2022-02-05’::timestamp-‘2022-02-01’::timestamp)

SELECT DATE_PART('day','2022-02-05'::timestamp - '2022-02-01'::timestamp)
1

七、拼接字符串

CONCAT()

SELECT CONCAT('jojo','bobo')
1

八、修改字段名

alter table 表名 rename column 原字段名 to 修改后字段名
1

九、日期加一天、一个月、一年

select '2022-01-01'::timestamp + '1 day'
select '2022-01-01'::timestamp + '1 month'
select '2022-01-01'::timestamp + '1 year'
1
2
3

十、建表语句

DROP TABLE IF EXISTS xxx.test
(
    id INT NOT NULL
   ,name VARCHAR(50)
   ,amt NUMERIC(10,2)
   ,PRTMARY KEY (id)
);
COMMENT ON TABLE xxx.test IS '测试表';
COMMENT ON COLUMN xxx.test.id IS 'ID';
COMMENT ON COLUMN xxx.test.name IS '名称';
COMMENT ON COLUMN xxx.test.amt IS '金额';
1
2
3
4
5
6
7
8
9
10
11

十一、修改字段类型

-- ALTER TABLE 表名 ALTER COLUMN 字段名 TYPE 修改后字段类型;
ALTER TABLE test.ads_xxx ALTER COLUMN pay_time TYPE TIMESTAMP;
1
2