当前位置：首页 > 编程笔记 > 正文

已解决

Postgresql 常用整理

来自网友在路上 158858提问提问时间：2023-11-11 19:32:19阅读次数： 58

最佳答案问答题库588位专家为你答疑解惑

文章目录

1. 查询
- 1.1数据库表
- - 1.1.1 获取指定数据库表
  - 1.1.2 获取指定数据库表所有列名
- 1.2 别名
- - 1.2.1 子表指定别名
  - 1.2.2 查询结果指定别名
- 1.3 临时表
- - 1.3.1 定义临时表
  - 1.3.2 使用临时表
- 1.4 子表
- 1.5 分组
- - 1.5.1 group by
  - 1.5.2 partition by
- 1.6 分组后合并指定列字段：
- 1.7 查询条件判断：case when
- 1.8 大小判断
- - 1.8.1 日期
  - 1.8.2 其他
- 1.9 空值判断
- - 1.9.1 判断null
  - 1.9.2 判断空串 ‘’
  - 1.9.3 判断null和空串
- 1.10 时间函数
- - 1.10.1 获取当前日期
  - 1.10.2 获取昨天、上周、上年日期
  - 1.10.3 获取今天、今月、今年的开始日期
  - 1.10.4 从时间戳提取年、月、日、时分秒，周
  - 1.10.5 取年、月、日、时分秒、星期数
  - 1.10.6 获取时间差（获取年、月、日差）
  - 1.10.7 获取日期差
- 1.11 判断字符串是否包含
- 1.12 取json字符串指定字段
- - 1.12.1 json和jsonb操作符
  - 1.12.2 操作实例
  - 1.12.3 处理json数组
- 1.14 拼接字符串
2. 创建函数（以获取日期间隔为例）
- 2.1 创建方式
- 2.2 函数使用

1. 查询

1.1数据库表

1.1.1 获取指定数据库表

SELECT table_name FROM
information_schema.TABLES
WHERETABLE_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 结果1when 条件2 then 结果2else 结果3end  可对结果添加别名

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在包含主题的 $BO D Y$ 处会提示，以及不能使用 $符号，可以在其他工具执行（ N a v i c a t e 、 p g A d min ）： u n t er mina t e dd o ll a r - q u o t e d s t r in g a t or n e a r "$ 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 numericLANGUAGE 'plpgsql'COST 100VOLATILE PARALLEL UNSAFE
AS $BODY$--声明函数使用变量名称 类型，函数都需要使用分号结尾
DECLAREv_return  varchar;--返回间隔时间 xx日xx时xx分
--函数主体    
BEGIN--省略方法--返回值     RETURN v_return;--异常处理EXCEPTION WHEN OTHERS THENRETURN 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);

查看全文

99%的人还看了

相似问题

猜你感兴趣

版权申明

本文"Postgresql 常用整理"：http://eshow365.cn/6-37886-0.html 内容来自互联网，请自行判断内容的正确性。如有侵权请联系我们，立即删除！

上一篇: 微信小程序多端应用 Donut 多端编译
下一篇: 《QT从基础到进阶·十九》QThread多线程使用

晴海小常识分享

晴海小常识分享

Postgresql 常用整理

最佳答案 问答题库588位专家为你答疑解惑

文章目录

1. 查询

1.1数据库表

1.1.1 获取指定数据库表

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

1.2 别名

1.2.1 子表指定别名

1.2.2 查询结果指定别名

1.3 临时表

1.3.1 定义临时表

1.3.2 使用临时表

1.4 子表

1.5 分组

1.5.1 group by

1.5.2 partition by

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

1.7 查询条件判断：case when

1.8 大小判断

1.8.1 日期

1.8.2 其他

1.9 空值判断

1.9.1 判断null

1.9.2 判断空串 ‘’

1.9.3 判断null和空串

1.10 时间函数

1.10.1 获取当前日期

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

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

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

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

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

1.10.7 获取日期差

1.11 判断字符串是否包含

1.12 取json字符串指定字段

1.12.1 json和jsonb操作符

1.12.2 操作实例

1.12.3 处理json数组

1.14 拼接字符串

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

2.1 创建方式

2.2 函数使用

99%的人还看了

相似问题

猜你感兴趣

版权申明

推荐回答

最佳答案问答题库588位专家为你答疑解惑