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

电玩女神 2023-01-10 05:17 201阅读 0赞

使用PostgreSQL为表或视图创建备注的操作,备注,字段,我是,类型,视图

使用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;

以上为个人经验，希望能给大家一个参考，也希望大家多多支持易采站长站。如有错误或未考虑完全的地方，望不吝赐教。以上就是关于对使用PostgreSQL为表或视图创建备注的操作的详细介绍。欢迎大家对使用PostgreSQL为表或视图创建备注的操作内容提出宝贵意见