pgsql 实现用户自定义表结构信息获取
1.获取表中普通信息：如字段名，字段类型等
SELECTcolumn_name,data_type,ordinal_position,is_nullable FROMinformation_schema."columns" WHERE"table_name"='TABLE-NAME'--将'TABLE-NAME'换成自己的表
2.获取所有的表和视图
SELECTtable_name,table_typeFROMINFORMATION_SCHEMA.tablesWHEREtable_schema='public'ANDtable_typeIN('BASETABLE','VIEW')
3.获取约束注释
SELECTobj_description(oid,'pg_constraint')ASdFROMpg_constraintWHEREconname=constraint_name;
4.获取表的约束
--conname约束名称 --contype约束类型(p:主键,f:外键,c:检查约束,u:唯一约束) --conkey约束字段 --confkey外键字段 --consrc检查约束条件 --confreltable外键字段引用的表 SELECTconname,contype,conkey,consrc, (SELECTarray_agg(column_name::text)FROMINFORMATION_SCHEMA.COLUMNSWHEREordinal_position=any(conkey)ANDtable_name='TABLE-NAME')ASconkey, (SELECTcolumn_nameFROMINFORMATION_SCHEMA.COLUMNSWHEREordinal_position=any(confkey)ANDtable_name='TABLE-NAME')ASconfkey, (SELECTrelnameFROMpg_classWHEREoid=confrelid)ASconfreltable FROMpg_constraintWHEREconrelid=(SELECToidFROMpg_classWHERErelname='TABLE-NAME');--将'TABLE-NAME'换成自己的表
5.获取表的触发器
SELECTtrigger_name,event_manipulation,event_object_table,action_statement,action_orientation,action_timingFROMINFORMATION_SCHEMA.TRIGGERS;
6.获取字段的注释
--table_oid表的oid --col_position字段的位置 SELECTcol_description(table_oid,col_position);
补充：查询PostgreSQL库中所有表的表结构信息SQL
我就废话不多说了，大家还是直接看代码吧~
select (selectrelnameascommentfrompg_classwhereoid=a.attrelid)astable_name, row_number()over(partitionby(selectrelnameascommentfrompg_classwhereoid=a.attrelid)orderbya.attnum), a.attnameascolumn_name, format_type(a.atttypid,a.atttypmod)asdata_type, (casewhenatttypmod-4>0thenatttypmod-4else0end)data_length, (casewhen(selectcount(*)frompg_constraintwhereconrelid=a.attrelidandconkey[1]=attnumandcontype='p')>0then'是'else'否'end)as主键约束, (casewhen(selectcount(*)frompg_constraintwhereconrelid=a.attrelidandconkey[1]=attnumandcontype='u')>0then'是'else'否'end)as唯一约束, (casewhen(selectcount(*)frompg_constraintwhereconrelid=a.attrelidandconkey[1]=attnumandcontype='f')>0then'是'else'否'end)as外键约束, (casewhena.attnotnull=truethen'是'else'否'end)asnullable, col_description(a.attrelid,a.attnum)ascomment frompg_attributea whereattstattarget=-1andattrelidin(selectoidfrompg_classwhererelnamein(selectrelnamefrompg_classwhererelkind='r'andrelname in (selecttablenamefrompg_tableswheretablenamenotlike'pg_%'andtablenamenotlike'sql_%'andschemanamenotin(XXXX)andtablenamenotin(XXXX) )) orderbytable_name,a.attnum;
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。