使用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 dwhere a.attnum>
    0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnumand 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 cwhere 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 cwhere 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 dwhere 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;
    

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

• PostgreSQL 更新视图脚本的注意事项说明
• PostgreSQL物化视图（materialized view）过程解析
• PostgreSQL教程(十六)：系统视图详解
• PostgreSQL 修改视图的操作

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！