使用PostgreSQL为表或视图创建备注的操作
1为表和列创建备注
droptableifexiststest; createtabletest( objectidserialnotnull, numintegernotnull, constraintpk_test_objectidprimarykey(objectid), constraintck_test_numcheck(num<123), ); commentontabletestis'我是表'; commentoncolumntest.objectidis'我是唯一主键'; commentoncolumntest.numis'数量字段'; commentonconstraintpk_test_objectidontestis'我是约束,唯一主键'; commentonconstraintck_test_numontestis'我是约束,num字段必须小于123'; \dS+test;
2为视图和列创建备注
dropviewifexistsvtest; createorreplaceviewvtest asselect1ascol1,'a'ascol2,now()ascol3; commentonviewvtestis'视图备注'; commentoncolumnvtest.col1is'第一列备注,integer类型'; commentoncolumnvtest.col2is'第二列备注,字符类型'; commentoncolumnvtest.col3is'第三列备注,日期时间类型';
3comment语法
COMMENTON
{
ACCESSMETHODobject_name|
AGGREGATEaggregate_name(aggregate_signature)|
CAST(source_typeAStarget_type)|
COLLATIONobject_name|
COLUMNrelation_name.column_name|
CONSTRAINTconstraint_nameONtable_name|
CONSTRAINTconstraint_nameONDOMAINdomain_name|
CONVERSIONobject_name|
DATABASEobject_name|
DOMAINobject_name|
EXTENSIONobject_name|
EVENTTRIGGERobject_name|
FOREIGNDATAWRAPPERobject_name|
FOREIGNTABLEobject_name|
FUNCTIONfunction_name[([[argmode][argname]argtype[,...]])]|
INDEXobject_name|
LARGEOBJECTlarge_object_oid|
MATERIALIZEDVIEWobject_name|
OPERATORoperator_name(left_type,right_type)|
OPERATORCLASSobject_nameUSINGindex_method|
OPERATORFAMILYobject_nameUSINGindex_method|
POLICYpolicy_nameONtable_name|
[PROCEDURAL]LANGUAGEobject_name|
PUBLICATIONobject_name|
ROLEobject_name|
RULErule_nameONtable_name|
SCHEMAobject_name|
SEQUENCEobject_name|
SERVERobject_name|
STATISTICSobject_name|
SUBSCRIPTIONobject_name|
TABLEobject_name|
TABLESPACEobject_name|
TEXTSEARCHCONFIGURATIONobject_name|
TEXTSEARCHDICTIONARYobject_name|
TEXTSEARCHPARSERobject_name|
TEXTSEARCHTEMPLATEobject_name|
TRANSFORMFORtype_nameLANGUAGElang_name|
TRIGGERtrigger_nameONtable_name|
TYPEobject_name|
VIEWobject_name
}IS'text'
whereaggregate_signatureis:
*|
[argmode][argname]argtype[,...]|
[[argmode][argname]argtype[,...]]ORDERBY[argmode][argname]argtype[,...]
注意：SQL标准中没有COMMENT命令。
补充：postgre查询注释_PostgreSQL查询表以及字段的备注
查询所有表名称以及字段含义
selectc.relname表名,cast(obj_description(relfilenode,'pg_class')asvarchar)名称,a.attname字段,d.description字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod)from'.∗'))as列类型frompg_classc,pg_attributea,pg_typet,pg_descriptiond
wherea.attnum>0anda.attrelid=c.oidanda.atttypid=t.oidandd.objoid=a.attrelidandd.objsubid=a.attnum
andc.relnamein(selecttablenamefrompg_tableswhereschemaname='public'andposition('_2'intablename)=0)orderbyc.relname,a.attnum
查看所有表名
selecttablenamefrompg_tableswhereschemaname='public'andposition('_2'intablename)=0;
select*frompg_tables;
查看表名和备注
selectrelnameastabname,cast(obj_description(relfilenode,'pg_class')asvarchar)ascommentfrompg_classc
whererelnamein(selecttablenamefrompg_tableswhereschemaname='public'andposition('_2'intablename)=0);
select*frompg_class;
查看特定表名备注
selectrelnameastabname, cast(obj_description(relfilenode,'pg_class')asvarchar)ascommentfrompg_classc whererelname='表名';
查看特定表名字段
selecta.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod)from'.∗'))astype,d.descriptionfrompg_classc,pg_attributea,pg_typet,pg_descriptiond
wherec.relname='表名'anda.attnum>0anda.attrelid=c.oidanda.atttypid=t.oidandd.objoid=a.attrelidandd.objsubid=a.attnum;
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。