Postgresql 常用整理

文章目录

  • 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
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在包含主题的 B O D Y BODY BODY处会提示，以及不能使用 符号，可以在其他工具执行（ N a v i c a t e 、 p g A d m i n ）： u n t e r m i n a t e d d o l l a r − q u o t e d s t r i n g a t o r n e a r " 符号，可以在其他工具执行（Navicate、pgAdmin）： unterminated dollar-quoted string at or near " 符号，可以在其他工具执行（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);

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/127155.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Web3.0的测试题

Web3.0的测试题

任务&#xff1a; 在前端开发一个查询UI&#xff0c;查询当前用户账户的ETH余额和指定ERC20合约中的余额 目标&#xff1a; UI框架指定使用 MUI (https://mui.com)需要查询到当前账户的ETH余额并展示在UI界面上需要输入ERC20合约地址后&#xff0c;查询到到当前账户在此ERC20…
阅读更多...
【Hadoop】YARN容量调度器详解

【Hadoop】YARN容量调度器详解

&#x1f984; 个人主页——&#x1f390;开着拖拉机回家_Linux,Java基础学习,大数据运维-CSDN博客 &#x1f390;✨&#x1f341; &#x1fa81;&#x1f341;&#x1fa81;&#x1f341;&#x1fa81;&#x1f341;&#x1fa81;&#x1f341; &#x1fa81;&#x1f341;&am…
阅读更多...
Chrony的基本原理

Chrony的基本原理

介绍 &#xff08;1&#xff09;Chrony是一个用于计算机系统时钟同步的程序。它使用网络时间协议NTP来与远程时间服务器通信&#xff0c;根据这些服务器提供的时间信息来调整系统时钟。Chrony具有高精度&#xff0c;可配置&#xff0c;易使用等特点。 &#xff08;2&#xff…
阅读更多...
集成MCU的OTP-2.4G合封芯片XL2401D，收发一体 上手简单

集成MCU的OTP-2.4G合封芯片XL2401D，收发一体 上手简单

芯岭技术的XL2401D是一颗2.4G合封芯片&#xff0c;收发一体。合封芯片可以很好的节省PCB面积和开发成本。一颗芯片可以做到之前两颗芯片才能做到的事情。XL2401D内含MCU为九齐NY8A054E。有九齐MCU开发经验的话开发起来非常容易上手。 XL2401D芯片是工作在2.400~2.483GHz世界通…
阅读更多...
11.7加减计数器，可置位~，数字钟分秒，串转并，串累加转并，24位串并128，流水乘法器，一些乘法器

11.7加减计数器，可置位~，数字钟分秒，串转并，串累加转并，24位串并128，流水乘法器，一些乘法器

信号发生器 方波&#xff0c;就是一段时间内都输出相同的信号 锯齿波就是递增 三角波就是先增后减 加减计数器 当mode为1则加&#xff0c;Mode为0则减&#xff1b;只要为0就输出zero 这样会出问题&#xff0c;因为要求是十进制&#xff0c;但是这里并没有考虑到9之后怎么办&a…
阅读更多...
分享76个Python管理系统源代码总有一个是你想要的

分享76个Python管理系统源代码总有一个是你想要的

分享76个Python管理系统源代码总有一个是你想要的 下载链接&#xff1a;百度网盘 请输入提取码 提取码&#xff1a;8888 项目名称 cms是一个基于kubernetes官方python SDKkubernetes开发的一个容器运维系统 Django erp 后台管理系统&#xff0c;仅限学习使用&#xff0c;不…
阅读更多...
边缘计算如何改变数据存储？

边缘计算如何改变数据存储？

边缘计算在整个价值链中提供多种优势——从降低成本到提高效率再到安全数据传输。该技术允许在源头收集和分析相关数据&#xff0c;这有助于减少延迟和带宽成本&#xff0c;同时显著提高计算过程的冗余系数和效率。 通过降低数据传输成本和损失&#xff0c;边缘计算帮助企业实现…
阅读更多...
【MySQL习题】各个视频的平均完播率【全网最详细教学】

【MySQL习题】各个视频的平均完播率【全网最详细教学】

目录 数据表描述 问题描述 输出示例 解题思路【重点】 正解代码 数据表描述 有以下两张表&#xff1a; 表1&#xff1a;用户-视频互动表tb_user_video_log 数据举例&#xff1a; 说明&#xff1a; uid-用户ID,video_id-视频ID start_time-开始观看时间end_time-结束观…
阅读更多...
java集合图谱

java集合图谱

集合
阅读更多...
【AI】自回归 (AR) 模型使预测和深度学习变得简单

【AI】自回归 (AR) 模型使预测和深度学习变得简单

自回归 (AR) 模型是统计和时间序列模型&#xff0c;用于根据数据点的先前值进行分析和预测。这些模型广泛应用于各个领域&#xff0c;包括经济、金融、信号处理和自然语言处理。 自回归模型假设给定时间变量的值与其过去的值线性相关&#xff0c;这使得它们可用于建模和预测时…
阅读更多...
Unity 跑酷游戏全部脚本（完结）

Unity 跑酷游戏全部脚本（完结）

脚本1 触发器脚本 这个脚本是主角身上的脚本&#xff0c;用于检测是否碰到其他触发器&#xff0c;并做出对应的行为 using System.Collections; using System.Collections.Generic; using UnityEngine; public class ColliidisonTrigger : MonoBehaviour { //触发检测 …
阅读更多...
docker部署mysql nginx redis

docker部署mysql nginx redis

一.创建网络 # 创建网络 docker network create liming # 查看网络 docker network ls二.部署mysql 删除并重新创建mysql容器&#xff0c;并完成本地目录挂载&#xff1a; 挂载/software/mysql/data到容器内的/var/lib/mysql目录挂载/software/mysql/init到容器内的/docker-…
阅读更多...
玄子Share-HTML5知识手册

玄子Share-HTML5知识手册

玄子Share-HTML5知识手册 前言&#xff1a; 这一版 HTML 笔记&#xff0c;算是我写的第四版了&#xff0c;第三版对照课本编写&#xff0c;第四版则是对照 MDN 官方文档编写&#xff0c;不论是术语亦或专业性&#xff0c;都更上一层 文章依托 MDN 文档&#xff0c;拓展了大量课…
阅读更多...
网络营销利器：海外IP代理如何助力你的网络营销？如何选择？

网络营销利器：海外IP代理如何助力你的网络营销？如何选择？

在当今数字化的时代&#xff0c;网络营销已经成为企业营销策略的重要组成部分。而对于进去海外市场的跨境玩家来说&#xff0c;海外的推广营销是重中之重。然而&#xff0c;在开展网络营销的过程中&#xff0c;我们常常会遇到各种挑战&#xff0c;如地域限制、访问速度慢等。 …
阅读更多...
理解透彻API接口电商API接口有哪些？你需要一分钟看这篇文章

理解透彻API接口电商API接口有哪些？你需要一分钟看这篇文章

什么是API呢&#xff1f;老实说啊&#xff0c;象征非常基础的概念&#xff0c;我原本以为大家都已经非常接楚&#xff0c;但是被突然这么一问呢&#xff0c;觉得有必要来认真复习一下这个概念&#xff0c;因为在我看来啊&#xff0c;技术上的问题呢&#xff0c;就没有高低贵贱之…
阅读更多...
Docker Desktop 和 WSL2 位置迁移

Docker Desktop 和 WSL2 位置迁移

迁移 WSL2 安装位置 WSL2 默认安装在 C 盘&#xff0c;我们可以通过以下步骤迁移安装位置 通过以下命令列出已安装的 Linux 发行版&#xff1a; wsl -l -v可以看到已安装了 Ubuntu-22.04&#xff0c;其运行状态为&#xff1a;Stopped 如果运行状态为 Running&#xff0c;需…
阅读更多...
洛谷 Equalize the Remainders

洛谷 Equalize the Remainders

洛谷没提供中文题面&#xff0c;这里大致翻译一下&#xff1a; 可以进行的操作&#xff1a;任选一个数加一。 一共有n个整数&#xff0c;还有一个约数m&#xff0c;n个数都对m进行求余&#xff0c;累计余数的数量&#xff0c;要求每个余数都有n/m个。 对于样例1的输入&#xff…
阅读更多...
JavaScript使用Ajax

JavaScript使用Ajax

Ajax(Asynchronous JavaScript and XML)是使用JavaScript脚本&#xff0c;借助XMLHttpRequest插件&#xff0c;在客户端与服务器端之间实现异步通信的一种方法。2005年2月&#xff0c;Ajax第一次正式出现&#xff0c;从此以后Ajax成为JavaScript发起HTTP异步请求的代名词。2006…
阅读更多...
bilibili快速升满级(使用Docker 容器脚本)

bilibili快速升满级(使用Docker 容器脚本)

部署bilibili升级运行容器脚本 docker run --name"bili" -v /bili/Logs:/app/Logs -e Ray_DailyTaskConfig__Cron"30 9 * * *" -e Ray_LiveLotteryTaskConfig__Cron"40 9 * * *" -e Ray_UnfollowBatchedTaskConfig__Cron"…
阅读更多...
传来喜讯，优维又获奖了！！！

传来喜讯，优维又获奖了！！！

优维科技作为国内DevOps领域的行业领先企业&#xff0c;从诞生之日起&#xff0c;就一直致力于为中国企业提供一流的数字化运维服务&#xff0c;不断深耕核心技术&#xff0c;向客户提供专业强大的产品与服务。多年来&#xff0c;不仅获得了大量客户认可&#xff0c;更是屡次获…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站