pgsql 常用SQL语句

1.获取当前时间函数，年月日时分秒

select now();

2022-01-12 22:18:32.045755+08

2.将当前时间转化为字符串 年月日 或者是灵活取值

select to_char(now(),'yyyy-MM-DD')

2022-01-12

3.获取前一个月（现在是2022年1月）

select to_char(now() - interval '1 month','yyyy-MM');

2021-12

4.获取前一天（现在是2022年1月12日）

select to_char(now() - interval '1 day','yyyy-MM-DD');

2022-01-11

5.灵活使用 like 直接用SQL 不需要用mybatis 使用 ||

  where
  signstatus = '1'
  and nowtime like '%'||to_char(now() - interval '1 month','yyyy-MM')||'%'

6.获取指定月份的天数

select date_part('days', date_trunc('month', to_timestamp('2021-12', 'yyyy-MM-dd hh24:mi:ss')) + '1 month'::interval - '1 day':: interval)

7.给定开始时间、结束时间获取之间的天数

select to_char(t,'MM') as day1 from generate_series('2021-12-28'::DATE,'2022-01-03','1day') as t

示例

select count(r.day1) from
(select to_char(t,'MM') as day1
 from 
 generate_series('2021-12-28'::DATE,'2022-01-03', '1 day') as t)r where r.day1 = '12';

8.创建自增序列

从1开始

 CREATE SEQUENCE 序列名  START 1;

示例
 CREATE SEQUENCE kx_sign_statistics2_id START 1;

从指定数字开始

CREATE SEQUENCE 序列名   START WITH 1490527538458529792;

示例

CREATE SEQUENCE kx_sign_statistics2_id START WITH 1490527538458529792;

9.将序列与指定的表字段绑定

 ALTER TABLE tablename ALTER COLUMN 字段名 SET DEFAULT nextval('序列名'::regclass);
 

示例

 ALTER TABLE kx_sign_statistics2 ALTER COLUMN id SET DEFAULT nextval('kx_sign_statistics2_id'::regclass);
 

10.UUID

1. 在pgsql中安装扩展
   create extension "uuid-ossp" ;
2. 查询
   select uuid_generate_v4()
3. 注意：必须是超级用户才能安装这个扩展

11.权限不够的情况下进行表的复制

select * into table1 from table2;

将表2的结构以及数据复制到表1中，要求表1 不存在

12.是否存在

select position('bc' in 'abcd,bcd,ddd');
 

13.根据查询出来的字段 添加序列号

使用：ROW_NUMBER () OVER (ORDER BY 字段 desc) as字段别名 

写法：SELECT  ROW_NUMBER () OVER (ORDER BY  字段  DESC) AS  字段别名  FROM  表名

14.查询数值出现的次数

示例：select array_length(regexp_split_to_array('abcdfffffffff','f'),1)-1 as result;

15.将字符串 转为 日期

select to_date('2022-12-06 22:24:15', 'YYYY-MM-DD HH24:MI:SS')

16.将查询到的结果合并

方法一：

SELECT string_agg(字段名,',') AS 别名

FROM

TABLE;

方法二(推荐使用)：

SELECT

array_to_string(ARRAY(SELECT unnest(array_agg(字段名))),',') AS 别名

FROM

TABLE;

如果需要排序后再拼接：

SELECT

array_to_string(ARRAY(SELECT unnest(array_agg(字段名 order by 字段名 desc))),',') AS 别名

FROM

TABLE;

17.查看语句的执行计划

在SQL前加explain 可以查看语句的执行计划--预计

比如：explain select * from table;

加 ANALYSE 语句会执行 --实际执行

比如：explain ANALYSE select * from table;

18.根据表创建视图

创建物化视图

CREATE MATERIALIZED VIEW view_plan as  select * from tn_newchangeexpand_plan;

创建普通视图
CREATE VIEW view_plan01 as  select * from tn_newchangeexpand_plan;

刷新物化视图

refresh materialized view view_plan;

select * from view_plan;

select count(id) from tn_newchangeexpand_plan;
select count(id) from view_plan;
select count(id) from view_plan01;

物化视图需要手动刷新，视图中的数据才是最新数据。普通视图不需要手动刷新。

19.创建用户权限

-- 创建用户"test"并设置密码：
create user test with password '123456';
-- 将视图的权限给到test
GRANT ALL ON view_plan01 TO test;
--将权限撤销
REVOKE ALL ON view_plan01 FROM test;

PostgreSQL 创建用户并赋予权限_postgres创建用户权限_qq_40760486的博客-CSDN博客

20.比较两个时间差额是否达4小时

select timestamp '2013-11-11 10:00:00' - timestamp '2013-11-08 12:00:00' > interval '4 hour';

21.查看pgsql库中数据总量

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;