postgresql 常用的操作

一、postgresql 常用的操作

1.查询结果保留小数

• 数据库存储小数位数很多，但是结果显示只需要1、2位。使用round()方法进行四舍五入的保留。

-- 注：字段1为varchar类型，可先将其转换为数字进行运算
select round(字段1::numeric,1) as 字段1,round(字段2,2) as 字段2 from table

2.格式化时间戳

SELECT to_char(now(), 'yyyy-MM-dd hh24:MI:ss')

3.行列转换

• 行转列 string_agg

id	name	age
1	张三	23
2	李四	24
3	王五	23

id	name	age
1	张三,王五	23
2	李四	24

-- 表名为student 把学生通过年龄分组并把姓名合并起来
SELECT age, string_agg(name,',') FROM student GROUP BY age

• 列转行 regexp_split_to_table

SELECT regexp_split_to_table('a,b,c',',')
--a
--b
--c
SELECT regexp_split_to_table('hello world','\s+')
--hello
--world

4.用指定分隔符截取字符串

split_part(字段名,'分隔符', 位置)

SELECT split_part('业务系统/统计模块/统计数量/计算','/', 3) 
--统计数量

5.将空字符串或NULL的字段替换成指定默认值

• CASE WHEN的方式

CASE WHEN state = 'del' THEN '删除' ELSE '正常' END

• COALESCE(NULLIF(trim(字段名), ''), '默认值')

--nullif(value1, value2) 如果value1 == value2 返回null
select COALESCE(NULLIF(trim(''), ''), '无') 

6. 拼接字段 concat和concat_ws

• concat中有一个参数是null, 查出来的结果就是null (我自己本地测试不为null)

select concat('大','小')
---查询出结果为：大小
 
select concat('大',NULL)
---查询出结果为：null ,自己测试结果是: 大

• concat_ws用某个符号来拼接

select concat_ws('_','大','小','中')
---查询出结果为：大_小_中
 
select concat_ws('_','大','小',NULL)
---查询出结果为：大_小

二、postgresql 表操作

1.查询表字段

select * from information_schema.columns
where table_schema='public' and table_name='表名称 '

2.查询表和记录数

select relname as TABLE_NAME, reltuples as rowCounts from pg_class 
where relkind = 'r' 
and relnamespace = (select oid from pg_namespace where nspname='public') 
order by rowCounts desc;