Postgresql 常用整理

1. 查询

1.1数据库表

1.1.1 获取指定数据库表

SELECT table_name FROM
information_schema.TABLES
WHERE
 TABLE_SCHEMA = 'act' -- 数据库名称
AND table_name LIKE '%act_re%';

1.1.2 获取指定数据库表所有列名

select column_name from information_schema.columns where table_name='ppom_object' ORDER BY ORDINAL_POSITION

1.2 别名

1.2.1 子表指定别名

• 父表根据子表别名获取列，使用as直接加英文方式

select  display_name as alias_display_name

1.2.2 查询结果指定别名

• 使用as 加英文双引号括住名称

select  display_name as "显示名称"

1.3 临时表

1.3.1 定义临时表

• 多个临时表，使用一个with，其他逗号分隔，最后一个不需要逗号

with  临时表名1  as (sql查询), 临时表名2 as(sql查询)

1.3.2 使用临时表

select * from 临时表名1 t1, 临时表名2 t2

1.4 子表

• 外部条件需要取子表列情况下，子表需要有别名（例如temp），通过别名获取

select * from (sql查询) temp where 条件

1.5 分组

1.5.1 group by

• 只可获取到分组的列（可以有多个）和按分组汇总数量或求和结果

select uid,name, count(*) from table group by uid,name

1.5.2 partition by

• 支持获取除分组的列以外其他的列，一般可以跟row_number()组合获取分组序号，按序号获取指定排序后的序号列

select * from (
    select row_number() over(partition by object_uid ORDER BY created_time ASC) AS new_index
)temp 
where temp.new_index = 1

1.6 分组后合并指定列字段：

• 使用array_to_string和array_agg组合
  • array_to_string（数组列，分隔符）
  • array_agg（ expression），加distinct去重，把表达式变成一个数组

SELECT uid, array_to_string(array_agg(distinct display_name),';') AS name FROM tmp GROUP BY uid

1.7 查询条件判断：case when

case when 条件1 then 结果1
     when 条件2 then 结果2
     else 结果3
     end  可对结果添加别名

1.8 大小判断

1.8.1 日期

select date_trunc('day',now()) = date_trunc('day',date('20230908'))

- 日期为date或timestamp，和字符串比较，字符串需要先转date或timestamp
select time1::timestamp > time2::timestamp
select time1::timestmap > to_timestamp('2023-11-15 18:33:41','yyyy-MM-dd hh24:mi:ss');
select to_date('2023-11-15 18:33:41','yyyy-MM-dd hh24:mi:ss');

1.8.2 其他

select a > b
# 大于：> ，小于：< ，等于：= ，不等于  !=  或 <> 

1.9 空值判断

1.9.1 判断null

select * from table where uid is null
select * from table where uid is not null

1.9.2 判断空串 ‘’

select * from table where uid = ''
select * from table where uid != '' 或 select * from table where uid <> ''

1.9.3 判断null和空串

select * from table where length(uid) > 0
select * from table where length(uid)  <= 0

1.10 时间函数

• （整合资料：https://blog.csdn.net/qq_40323256/article/details/123185022）

1.10.1 获取当前日期

select now();
select current_timestamp;
select to_char( now(),'yyyy-mm-dd hh:mi:ss');
select to_char(时间戳字段,'yyyy-mm-dd hh24:mi:ss');
#hh默认是12，可指定：hh12，hh24
select current_date;

1.10.2 获取昨天、上周、上年日期

select to_char( now() - interval '1 day','yyyy-mm-dd');
select to_char( now() - interval '1 week','yyyy-mm-dd hh:mi:ss');
select to_char( now() - interval '1 month','yyyy-mm-dd');
select to_char( now() - interval '1 year','yyyy-mm-dd');

1.10.3 获取今天、今月、今年的开始日期

select date_trunc('year', now())
select date_trunc('month', now())
select date_trunc('day', now())
select date_trunc('hour', now())
select date_trunc('minute', now())
select date_trunc('second', now())

1.10.4 从时间戳提取年、月、日、时分秒，周

select date_part('year', timestamp '2001-02-16 20:38:40')
#或者
select date_part('year', '2001-02-16 20:38:40'::timestamp)
select date_part('month', timestamp '2001-02-16 20:38:40')
select date_part('day', timestamp '2001-02-16 20:38:40')
select date_part('hour', timestamp '2001-02-16 20:38:40')
select date_part('minute', timestamp '2001-02-16 20:38:40')
select date_part('second', timestamp '2001-02-16 20:38:40')
select date_part('week', timestamp '2001-02-16 20:38:40')

1.10.5 取年、月、日、时分秒、星期数

// 从当前时间中提取年份
SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-05-20 16:54:53.644833');    

// 从当前时间中提取月份   
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-05-20 16:54:53.644833'); 

// 从当前时间中提取天     
SELECT EXTRACT(DAY FROM TIMESTAMP '2023-05-20 16:54:53.644833');    

// 从当前时间中提取小时    
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-05-20 16:54:53.644833');     

// 从当前时间中提取分钟  
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-05-20 16:54:53.644833');     

// 从当前时间中提取秒
SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-05-20 16:54:53.644833');     

// 从当前时间中提取世纪
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2023-05-20 16:54:53.644833');    

// 从当前时间中提取时间戳，单位：秒
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-05-20 16:54:53.644833');      
//取星期数
SELECT EXTRACT(DOW FROM TIMESTAMP '2023-05-20 16:54:53.644833');  

1.10.6 获取时间差（获取年、月、日差）

#只获取年、月、日数据，其他忽略
select date_part('year', endtime::TIMESTAMP-starttime::TIMESTAMP)
select date_part('month', endtime::TIMESTAMP-starttime::TIMESTAMP)
select date_part('day', endtime::TIMESTAMP-starttime::TIMESTAMP)
#获取整体时间差，默认秒
#时间差换算成日
select date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60/60/24
#可以外部套一层向下取整floor()、向上取整ceil()、四舍五入round()取整
select floor(date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60/60/24)
#时间差换算成小时
select date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60/60
#时间差换算成分钟
select date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60

1.10.7 获取日期差

#只取日期部分计算
select Date(endtime) - DATE(starttime)

1.11 判断字符串是否包含

• position(substring in string)，第一个时目标字符串，第二个是原字符串，如果包含，返回第一次出现位置，根据是否大于0判断

select postion('aa' in 'abcd')

• strpos(string, substring)，作用与position一致

1.12 取json字符串指定字段

• （参考资料：https://blog.csdn.net/c_zyer/article/details/130968257）

1.12.1 json和jsonb操作符

1.12.2 操作实例

select order_json::json->'orderBody' from order -- 对象域
select order_json::json->>'orderBody' from order -- 文本
select order_json::json#>'{orderBody}' from order -- 对象域
select order_json::json#>>'{orderBody}' from order -- 文本

# 列名：column，列值： {"key":"","name":"发起人修改","id":""}
select column::json ->> 'name'

1.12.3 处理json数组

#取数组长度
select json_array_length(name::json)

#取出json数组中的某个字段返回json数组
#列名：column，列值：[{"key":"","name":"xxx","id":""}]
select json_array_elements(column::json) -> 'name'

1.14 拼接字符串

// a11b
select concat('a', 11, NULL, 'b');
// aabb
select  'aa' || 'bb'

2. 创建函数（以获取日期间隔为例）

2.1 创建方式

• 可以通过sql文件执行函数定义，或者通过sql工具界面操作创建函数的方式，部分工具（以HeidiSQL为例）执行sql在包含主题的$BODY$处会提示，以及不能使用$符号，可以在其他工具执行（Navicate、pgAdmin）：unterminateddollar-quotedstringatornear"$BODY$
• 函数内容

-- 定义函数所处表，名称，public.f_daydelay，
-- 输入参数名称 类型 
-- 返回类型 RETURNS type
-- 创建函数语言选择 LANGUAGE，可以选择（plpgsql，sql，plpython，plperl，...）
-- 启动成本 COST 100（默认值）
-- 波动率 VOLATILE （波动率默认分类），可以执行任何操作

CREATE OR REPLACE FUNCTION public.f_daydelay(
    starttime timestamp with time zone,
    endtime timestamp with time zone)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

--声明函数使用变量名称 类型，函数都需要使用分号结尾
DECLARE
     v_return  varchar;--返回间隔时间 xx日xx时xx分
--函数主体    
BEGIN
  --省略方法
  --返回值     
  RETURN v_return;
    --异常处理
    EXCEPTION 
    WHEN OTHERS THEN
        RETURN SQLERRM;
--主体结束    
END;
$BODY$;

--定义函数授权用户
ALTER FUNCTION public.f_daydelay(timestamp with time zone, timestamp with time zone)
    OWNER TO postgres;

2.2 函数使用

select f_daydelay('2023-05-24 11:38:14.38'::timestmap,'2023-06-20 11:38:14.38'::timestamp);