pgsql 常用查询汇总(查询数据表字段)

1.查询数据库表中字段

（1）查询数据库表中字段及类型 （这种方式没有字段备注信息）

SELECT table_catalog,table_schema,column_name,ordinal_position,is_nullable,data_type,character_maximum_length,numeric_precision,udt_name
    FROM information_schema.columns As c
        WHERE table_name = '表名' 

查询结果：

table_catalog:	数据库名
table_schema:	约束
table_name:	表名
column_name:	字段名

（2）查询字段类型及备注（这种表中没有数据查不出来）

select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description,typnotnull from pg_class c,pg_attribute a,pg_type t,pg_description d
where c.relname='表名' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum;

查询结果

（3）查询字段类型及备注

对（2）的基础上，解决了表中无数据无法查出字段的问题

SELECT
 A.attname AS field_name,-- 字段名
 t.typname as typename,    --字段类型
 NULLIF(information_schema._pg_char_max_length(A.atttypid, A.atttypmod), -1) AS maxlen,  -- 字符串最大长度
 col_description ( A.attrelid, A.attnum ) AS COMMENT,   -- 字段备注
 format_type ( A.atttypid, A.atttypmod ) AS TYPE,
 A.attnotnull AS NOTNULL ,  -- 是否非空
 A.atthasdef ,  --是否存在默认值
 A.atttypmod
FROM
	pg_class AS C,
	pg_attribute AS A ,
	pg_type as T
WHERE
	C.relname = 'aaa' 
	AND A.attrelid = C.oid 
	AND A.atttypid= T.oid
	AND A.attnum > 0
	AND NOT A.attisdropped

		AND NOT A.attisdropped   过滤掉已删除的字段

查询结果：

2.常用空间查询语句积累

（1）判断点是否在要素内

select "AREA_CODE" from geo_cun where st_intersects(geom,ST_GeomFromText('POINT(116 39)', 4326));

（2）数据表转为geojson

SELECT jsonb_build_object ('type','FeatureCollection','features',jsonb_agg (feature)) as geojson 
  FROM (SELECT jsonb_build_object ('type','Feature','geometry',ST_AsGeoJSON (mgeom) :: jsonb,'properties',to_jsonb (ROW)  - 'mgeom') AS feature FROM 
(SELECT mgeom,ST_CoordDim(mgeom)  FROM  zrzhczt_ggfwss_xx where not ST_IsEmpty(mgeom) ) ROW) features

（3）获取要素中心点

select ST_Centroid(geometry);

3. 将数据表转为json

select array_to_json(array_agg(row_to_json(t))) from (
  select * from tableA order by px
) t ;

4.查看数据库数据占用大小

（1）查询数据占用磁盘空间

geoc_data为数据库的名

SELECT pg_size_pretty( pg_database_size('geoc_data') );

查询结果：

（2）查询数据库单表的大小

data_11001000031为表名

SELECT pg_size_pretty( pg_total_relation_size('data_11001000031') );

查询结果:

5.查询语句常见问题

5.1 查询条件 not like ‘%%’ 查询不出数据为null的数据。

5.2 查询条件 not in (null) 查询不出数据。

5.3 查询条件 !=‘’ 查询不出null的数据。

如果想查出所有不为某个值的条件，需要考虑null的情况:
eg: and (field!=‘1’ or field is null)

5.4 重复数据处理，窗口函数

delete from ( SELECT *,ROW_NUMBER() OVER(PARTITION by ${业务主键字段} ORDER BY "${排序字段}" desc)idx FROM "${数据表名}" ) aa where aa.idx!=1

6.锁表处理

select oid from pg_class where relname='xzq_statistic'   

select pid from pg_locks where relation='oid'  -- 上个查询出来的oid  如果存在锁表，能查询出来pid

select pg_cancel_backend(pid)   -- 上个查询出来的pid

7.查询正在执行的sql

SELECT
pid,
	datname,
	usename,
	client_addr,
	application_name,
	STATE,
	backend_start,
	xact_start,
	xact_stay,
	query_start,
	query_stay,
	REPLACE ( query, chr( 10 ), ' ' ) AS query 
FROM
	(
SELECT
pid,
	pgsa.datname AS datname,
	pgsa.usename AS usename,
	pgsa.client_addr client_addr,
	pgsa.application_name AS application_name,
	pgsa.STATE AS STATE,
	pgsa.backend_start AS backend_start,
	pgsa.xact_start AS xact_start,
	EXTRACT (
	epoch 
FROM
	( now() - pgsa.xact_start )) AS xact_stay,
	pgsa.query_start AS query_start,
	EXTRACT (
	epoch 
FROM
	( now() - pgsa.query_start )) AS query_stay,
	pgsa.query AS query 
FROM
	pg_stat_activity AS pgsa 
WHERE
	pgsa.STATE != 'idle' 
	AND pgsa.STATE != 'idle in transaction' 
	AND pgsa.STATE != 'idle in transaction (aborted)' 
	) idleconnections 
ORDER BY
	query_stay DESC 
	LIMIT 50;

8.清理pg WAL 归档文件 pg_archivecleanup

1. 查看上次检查点的
   pg_controldata $PGDATA
   查询到 Latest checkpoint’s REDO WAL file 0000000100000391000000B9
2. 清理归档
   pg_archivecleanup -d $PGDATA/pg_wal 0000000100000391000000B9

9.强制修改数据库名

-- 首先断开正在的连接（可多执行几次）
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='db_name' AND pid<>pg_backend_pid();
 
ALTER DATABASE db_name RENAME TO db_name_new;