使用PostgreSQL为表或视图创建备注的操作

1 为表和列创建备注

drop table if exists test;
create table test(
  objectid serial not null,
  num integer not null,
 constraint pk_test_objectid primary key (objectid),
 constraint ck_test_num check(num < 123 ),
);
comment on table test is '我是表';
comment on column test.objectid is '我是唯一主键';
comment on column test.num is '数量字段';
comment on constraint pk_test_objectid on test is '我是约束,唯一主键';
comment on constraint ck_test_num on test is '我是约束,num字段必须小于123';
\ds+ test;

2 为视图和列创建备注

drop view if exists vtest;
create or replace view vtest
 as select 1 as col1, 'a' as col2, now() as col3;
comment on view vtest is '视图备注';
comment on column vtest.col1 is '第一列备注,integer类型';
comment on column vtest.col2 is '第二列备注,字符类型';
comment on column vtest.col3 is '第三列备注,日期时间类型';

3 comment语法

comment on
{
 access method object_name |
 aggregate aggregate_name ( aggregate_signature ) |
 cast (source_type as target_type) |
 collation object_name |
 column relation_name.column_name |
 constraint constraint_name on table_name |
 constraint constraint_name on domain domain_name |
 conversion object_name |
 database object_name |
 domain object_name |
 extension object_name |
 event trigger object_name |
 foreign data wrapper object_name |
 foreign table object_name |
 function function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
 index object_name |
 large object large_object_oid |
 materialized view object_name |
 operator operator_name (left_type, right_type) |
 operator class object_name using index_method |
 operator family object_name using index_method |
 policy policy_name on table_name |
 [ procedural ] language object_name |
 publication object_name |
 role object_name |
 rule rule_name on table_name |
 schema object_name |
 sequence object_name |
 server object_name |
 statistics object_name |
 subscription object_name |
 table object_name |
 tablespace object_name |
 text search configuration object_name |
 text search dictionary object_name |
 text search parser object_name |
 text search template object_name |
 transform for type_name language lang_name |
 trigger trigger_name on table_name |
 type object_name |
 view object_name
} is 'text'
where aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] order by [ argmode ] [ argname ] argtype [ , ... ]

注意：sql 标准中没有comment命令。

补充：postgre 查询注释_postgresql查询表以及字段的备注

查询所有表名称以及字段含义

select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,substring(format_type(a.atttypid,a.atttypmod) from '.∗')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum

查看所有表名

select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0;
select * from pg_tables;

查看表名和备注

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
where relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0);
select * from pg_class;

查看特定表名备注

select relname as tabname,
cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
where relname ='表名';

查看特定表名字段

select a.attnum,a.attname,concat_ws('',t.typname,substring(format_type(a.atttypid,a.atttypmod) from '.∗')) as type,d.description 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;

以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方，望不吝赐教。