介绍
本篇文章给大家分享的是有关怎么在pgsql中利用自定义表结构获取信息,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。
1。获取表中普通信息:如字段名、字段类型等
SELECT column_name,, data_type,, ordinal_position,, is_nullable 得到information_schema !”columns" WHERE “table_name"=& # 39; TABLE-NAME& # 39;,,,将,& # 39;TABLE-NAME& # 39;,换成自己的表
2。获取所有的表和视图
SELECT table_name,, table_type 得到INFORMATION_SCHEMA.tables WHERE table_schema=& # 39;公共# 39;,以及table_type 拷贝(& # 39;BASE 表# 39;,& # 39;视图# 39;)
3。获取约束注释
SELECT obj_description (oid, & # 39; pg_constraint& # 39;), AS d 得到pg_constraint WHERE conname =, constraint_name;
4。获取表的约束
——, conname 约束名称 ——,contype 约束类型(p:主键,,f:外键,,c:,检查约束,,u:唯一约束) ——,conkey 约束字段 ——,confkey 外键字段 ——,consrc 检查约束条件 ——,confreltable 外键字段引用的表 SELECT conname, contype, conkey,, consrc,, ,,(SELECT array_agg (column_name:文本),得到INFORMATION_SCHEMA.COLUMNS WHERE ordinal_position =,任何(conkey),以及table_name=, & # 39; TABLE-NAME& # 39;), AS conkey,, (才能得到INFORMATION_SCHEMA.COLUMNS 获SELECT column_name ; WHERE  ordinal_position =,任何(confkey),以及table_name=& # 39; TABLE-NAME& # 39;), AS confkey,, (才能得到pg_class 获SELECT relname ; WHERE  oid =, confrelid), AS confreltable 得到pg_constraint WHERE  conrelid=(SELECT oid 得到pg_class WHERE relname =& # 39; TABLE-NAME& # 39;);,,,将,& # 39;TABLE-NAME& # 39;,换成自己的表
5。获取表的触发器
SELECT trigger_name,, event_manipulation,, event_object_table,, action_statement,, action_orientation,, action_timing 得到INFORMATION_SCHEMA.TRIGGERS;
6。获取字段的注释
——table_oid 表的oid ——col_position 字段的位置 SELECT col_description (table_oid, col_position);
<强>补充:查询PostgreSQL库中所有表的表结构信息SQL 强>
我就废话不多说了,大家还是直接看代码吧~
选择 (select relname as comment 得到pg_class where oid=a.attrelid), as table_name, ,row_number ()/(partition by (select relname  as comment 得到pg_class where oid=a.attrelid), order by a.attnum), a.attname as  column_name, format_type (a.atttypid a.atttypmod), as data_type, (case when atttypmod-4> 0, then atttypmod-4 else 0,) data_length, (case when (select count(*),得到pg_constraint where conrelid =, a.attrelid 以及conkey [1]=attnum 以及contype=& # 39; p # 39;)在0,then & # 39;是& # 39;,else & # 39;否& # 39;,结束),as 主键约束, (case when (select count(*),得到pg_constraint where conrelid =, a.attrelid 以及conkey [1]=attnum 以及contype=& # 39; u # 39;)在0,then & # 39;是& # 39;,else & # 39;否& # 39;,结束),as 唯一约束, (case when (select count(*),得到pg_constraint where conrelid =, a.attrelid 以及conkey [1]=attnum 以及contype=& # 39; f # 39;)在0,then & # 39;是& # 39;,else & # 39;否& # 39;,结束),as 外键约束, (case when a.attnotnull=true then & # 39;是& # 39;,else & # 39;否& # 39;,结束),as 可以为空, col_description (a.attrelid a.attnum), as 发表评论 得到pg_attribute where attstattarget=1,以及attrelid 拷贝(select oid 得到pg_class where relname (select relname 得到pg_class where relkind =& # 39; " # 39;,以及relname 拷贝; (select tablename 得到pg_tables where tablename not like & # 39; pg_ % & # 39;,以及tablename not like & # 39; sql_ % & # 39;,以及schemaname not 在(XXXX),以及tablename not 在(XXXX) )) order by  table_name a.attnum;怎么在pgsql中利用自定义表结构获取信息