pgsql查询表中字段名,类型,备注以及是否非空 SELECT col_description ( A.attrelid, A.attnum ) AS COMMENT, format_type ( A.atttypid, A.atttypmod ) AS TYPE, A.attname AS NAME, A.attnotnull AS NOTNULL FROM pg_class AS C, pg_attribute AS A WHERE C.relname='dm_ass_gp_income'AND ...
tablename SIMILAR TO 'dwd_[a-z,_]+_[0-9]+' (二)从pg_class和pg_attribute根据指定的表名查询字段信息 SELECT C.relname, A.attname AS NAME, A.attnotnull AS NOTNULL, format_type ( A.atttypid, A.atttypmod ) AS TYPE, col_description ( A.attrelid, A.attnum ) AS COMMENT FROM pg...
pcolumn.numeric_scale as scale, col_description(pclass.oid, pcolumn.ordinal_position) as ColumnDescription, case when pkey.colname = pcolumn.column_name then true else false end as IsPrimaryKey, case when pcolumn.column_default like 'nextval%' then true else false end as IsIdentity, case ...
col_description ( A.attrelid, A.attnum ) AS COMMENT:字段注释 attrelid:字段对应的表id(与class中的oid关联) pg_description 用来存储数据库对象(如字段、表)的描述(注释)信息。 objoid:对象OID classoid:对应表 OID objsubid:如果是字段对象,则是字段序号(位置),否则为0。 descriptiontext:描述、注释内容...
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, ...
select c.relname, col_description(a.attrelid, a.attnum) as comment, format_type(a.atttypid, a.atttypmod) as type, a.attname as name, a.attnotnull as notnull from pg_class as c, pg_attribute as a where a.attrelid = c.oid and a.attnum > 0 and c.relname = 'student'; ...
在这个查询中: pg_catalog.pg_attribute 表包含了表的字段信息。 pg_catalog.pg_class 表包含了表的信息。 pg_catalog.col_description 函数用于获取字段的注释。 请将your_table_name替换为你想要查询的表名。执行这个查询后,你将得到该表所有字段的名称及其对应的注释。
SELECT col_description(table_oid, col_position); 补充:查询PostgreSQL库中所有表的表结构信息SQL 我就废话不多说了,大家还是直接看代码吧~ select (select relname as comment from pg_class where oid=a.attrelid) as table_name, row_number() over(partition by (select relname as comment from pg_class...
SELECTcol_description(table_oid, col_position); AI代码助手复制代码 补充:查询PostgreSQL库中所有表的表结构信息SQL 我就废话不多说了,大家还是直接看代码吧~ select(selectrelnameascommentfrompg_classwhereoid=a.attrelid)astable_name,row_number()over(partitionby(selectrelnameascommentfrompg_classwhereoid=a...
SELECT c.relname, a.attname as 字段名, col_description(a.attrelid,a.attnum) as 注释, concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '(.*)')) as 字段类型, concat_ws('', ' COMMENT ON COLUMN public.', c.relname ,'.', a.attname ,' IS null; '...