使用PostgreSQL为表或视图创建备注的操作_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;