PostgreSQL常用语法

1、导出表结构

SELECT a.attnum AS "序号",
c.relname AS "表名",
a.attname AS "字段名",
CASE A.attnotnull
	WHEN 'f' THEN
		'否'
	ELSE
		'是'
END as 是否为空, 
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "字段类型",
d.description AS "备注"
FROM pg_attribute A LEFT JOIN pg_class C ON A.attrelid = C.oid LEFT JOIN pg_description D ON D.objoid=A.attrelid AND D.objsubid=A.attnum LEFT JOIN pg_type T ON A.atttypid = T.oid 
WHERE A.attnum>0 AND C.relname = 'yw_product_meta'

2、查看当前配置的最大连接数：

show max_connections

查询当前实际连接数：

select count(1) from pg_stat_activity

查看来自某IP的连接：

select * from pg_stat_activity where client_addr='192.168.129.17

如果需要修改最大连接数，可修改配置文件：

在postgresql.conf中设置：
	max_connections = 500
修改该参数需要重启postgres库

查看配置文件位置：

select name,setting from pg_settings where category='File Locations'

查看数据库大小：

select pg_database_size('<dbname>')

查询数据表占用空间大小：

SELECT   table_schema || '.' || table_name AS table_full_name,pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables  ORDER BY  pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC