• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
123456825 显示全部楼层 发表于 2021-10-26 14:25:32 |阅读模式 打印 上一主题 下一主题
1 为表和列创建备注
  1. drop table if exists test;
  2. create table test(
  3.   objectid serial not null,
  4.   num integer not null,
  5. constraint pk_test_objectid primary key (objectid),
  6. constraint ck_test_num check(num < 123 ),
  7. );
  8. comment on table test is '我是表';
  9. comment on column test.objectid is '我是唯一主键';
  10. comment on column test.num is '数量字段';
  11. comment on constraint pk_test_objectid on test is '我是约束,唯一主键';
  12. comment on constraint ck_test_num on test is '我是约束,num字段必须小于123';
  13. \dS+ test;
复制代码
2 为视图和列创建备注
  1. drop view if exists vtest;
  2. create or replace view vtest
  3. as select 1 as col1, 'a' as col2, now() as col3;
  4. comment on view vtest is '视图备注';
  5. comment on column vtest.col1 is '第一列备注,integer类型';
  6. comment on column vtest.col2 is '第二列备注,字符类型';
  7. comment on column vtest.col3 is '第三列备注,日期时间类型';
复制代码
3 comment语法
  1. COMMENT ON
  2. {
  3. ACCESS METHOD object_name |
  4. AGGREGATE aggregate_name ( aggregate_signature ) |
  5. CAST (source_type AS target_type) |
  6. COLLATION object_name |
  7. COLUMN relation_name.column_name |
  8. CONSTRAINT constraint_name ON table_name |
  9. CONSTRAINT constraint_name ON DOMAIN domain_name |
  10. CONVERSION object_name |
  11. DATABASE object_name |
  12. DOMAIN object_name |
  13. EXTENSION object_name |
  14. EVENT TRIGGER object_name |
  15. FOREIGN DATA WRAPPER object_name |
  16. FOREIGN TABLE object_name |
  17. FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  18. INDEX object_name |
  19. LARGE OBJECT large_object_oid |
  20. MATERIALIZED VIEW object_name |
  21. OPERATOR operator_name (left_type, right_type) |
  22. OPERATOR CLASS object_name USING index_method |
  23. OPERATOR FAMILY object_name USING index_method |
  24. POLICY policy_name ON table_name |
  25. [ PROCEDURAL ] LANGUAGE object_name |
  26. PUBLICATION object_name |
  27. ROLE object_name |
  28. RULE rule_name ON table_name |
  29. SCHEMA object_name |
  30. SEQUENCE object_name |
  31. SERVER object_name |
  32. STATISTICS object_name |
  33. SUBSCRIPTION object_name |
  34. TABLE object_name |
  35. TABLESPACE object_name |
  36. TEXT SEARCH CONFIGURATION object_name |
  37. TEXT SEARCH DICTIONARY object_name |
  38. TEXT SEARCH PARSER object_name |
  39. TEXT SEARCH TEMPLATE object_name |
  40. TRANSFORM FOR type_name LANGUAGE lang_name |
  41. TRIGGER trigger_name ON table_name |
  42. TYPE object_name |
  43. VIEW object_name
  44. } IS 'text'
  45. where aggregate_signature is:
  46. * |
  47. [ argmode ] [ argname ] argtype [ , ... ] |
  48. [ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
复制代码
注意:SQL 尺度中没有COMMENT下令。
补充:postgre 查询注释_PostgreSQL查询表以及字段的备注
查询全部表名称以及字段寄义
  1. 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
  2. where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
  3. and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum
复制代码
检察全部表名
  1. select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0;
  2. select * from pg_tables;
复制代码
检察表名和备注
  1. select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
  2. where relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0);
  3. select * from pg_class;
复制代码
检察特定表名备注
  1. select relname as tabname,
  2. cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
  3. where relname ='表名';
复制代码
检察特定表名字段
  1. 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
  2. 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;
复制代码
以上为个人履历,盼望能给各人一个参考,也盼望各人多多支持草根技能分享。如有错误或未考虑完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作