PostgreSQL数据库常用方法

1. 数据库监控

1.1 查询数据库最大连接数、当前连接数

-- 查看数据库最大连接数，此数值包含为超级用户预留的连接数
SELECT setting FROM pg_settings where name = 'max_connections' --1000
-- 查看数据库当前连接数
select count(*) from pg_stat_activity;	-- 385
1
2
3
4

1.2 查询指定数据库大小（已用空间）

-- 查询当前数据库大小（已用空间）
select pg_size_pretty(pg_database_size(current_database())) 
1
2

• pg_size_pretty()：将size数据格式化显示
• pg_database_size()：计算指定数据库大小，参数是数据库名称

1.3 查看当前数据库下所有sehcma大小

SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as "disk space"
FROM (SELECT pg_catalog.pg_namespace.nspname as schema_name,
	pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
	FROM  pg_catalog.pg_class
	JOIN pg_catalog.pg_namespace 
	ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
1
2
3
4
5
6
7
8

1.4 查询指定schema的大小

-- 查询当前schema的大小
SELECT pg_size_pretty(SUM(pg_relation_size)) 
FROM (SELECT relname, pg_total_relation_size(relid) pg_relation_size 
	FROM pg_stat_user_tables WHERE schemaname = CURRENT_SCHEMA() 
	ORDER BY pg_relation_size(relid) DESC ) t;
1
2
3
4
5

1.5 查询数据库是否死锁

-- 查询当前数据库是否死锁
SELECT count(1) FROM pg_stat_activity 
WHERE datname = current_database() and waiting = 't';
1
2
3

1.6 查询数据库地址

select inet_server_port();
select inet_client_addr();
select inet_server_addr();
1
2
3

2. 分组统计

2.1 按小时分组统计，需要使用’hh24’转换日期

使用’HH’转换日期，是12小时制
使用’hh24’转换日期，是24小时制，示例如下：

SELECT code, to_char(file_date, 'hh24'), count(id) FROM product_info  
WHERE file_date BETWEEN '2022-04-28 15:46:00' AND '2022-04-29 15:46:00'
GROUP BY code, to_char(file_date, 'hh24') 
ORDER BY code, to_char(file_date, 'hh24')
1
2
3
4

3. PostGis

3.1 postgis插件安装

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
# 查询扩展
SELECT * FROM pg_extension
1
2
3
4
5
6

4. 常用函数

4.1 聚合函数

• COUNT 函数：用于计算数据库表中的行数。
• MAX 函数：用于查询某一特定列中最大值。
• MIN 函数：用于查询某一特定列中最小值。
• AVG 函数：用于计算某一特定列中平均值。
• SUM 函数：用于计算数字列所有值的总和。
• ARRAY 函数：用于输入值(包括null)添加到数组中。
• Numeric 函数：完整列出一个 SQL 中所需的操作数。
• String 函数：完整列出一个 SQL 中所需的操作字符。

4.2 数学函数

4.3 三角函数

4.4 字符串函数

4.5 类型转换相关函数