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

#pgsql 常用查询汇总(查询数据表字段)| 来源: 网络整理| 查看: 265

pgsql 查询表中字段信息 1.查询数据库表中字段（1）查询数据库表中字段及类型（这种方式没有字段备注信息）（2）查询字段类型及备注（这种表中没有数据查不出来）（3）查询字段类型及备注 2.常用空间查询语句积累（1）判断点是否在要素内（2）数据表转为geojson（3）获取要素中心点 3. 将数据表转为json4.查看数据库数据占用大小（1）查询数据占用磁盘空间（2）查询数据库单表的大小 5.查询语句常见问题5.1 查询条件 not like '%%' 查询不出数据为null的数据。5.2 查询条件 not in (null) 查询不出数据。5.3 查询条件 !='' 查询不出null的数据。 6.锁表处理7.查询正在执行的sql8.清理pg WAL 归档文件 pg_archivecleanup9.强制修改数据库名

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)

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 查看上次检查点的 pg_controldata $PGDATA 查询到 Latest checkpoint’s REDO WAL file 0000000100000391000000B9清理归档 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 pidpg_backend_pid(); ALTER DATABASE db_name RENAME TO db_name_new;

【本文地址】

公司简介

联系我们