如何获取PostgreSQL数据库的注释？ [英] How to retrieve the comment of a PostgreSQL database?

问题描述

我最近发现您可以为PostgreSQL中的所有对象添加注释 。特别是，我对玩数据库注释感兴趣。例如，要设置数据库的注释：

I recently discovered you can attach a comment to all sort of objects in PostgreSQL. In particular, I'm interested on playing with the comment of a database. For example, to set the comment of a database:

COMMENT ON DATABASE mydatabase IS 'DB Comment';

但是，相反的语句是什么，获取 code> mydatabase ？

However, what is the opposite statement, to get the comment of mydatabase?

在 psql 命令行中，我可以通过 \l + 命令查看注释以及其他信息；我可以借助awk使用它来实现我的目标。但是，如果可能的话，我宁愿使用SQL语句。

From the psql command line, I can see the comment along with other information as a result of the \l+ command; which I could use with the aid of awk in order to achieve my goal. But I'd rather use an SQL statement, if possible.

推荐答案

要获取有关数据库的注释，请使用以下查询：

To get the comment on the database, use the following query:

select description from pg_shdescription
join pg_database on objoid = pg_database.oid
where datname = '<database name>'

此查询将为您提供给定表名的表注释：

This query will get you table comment for the given table name:

select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
where relname = '<your table name>'

如果在不同模式中使用相同的表名，则需要对其进行修改位：

If you use the same table name in different schemas, you need to modify it a bit:

select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
join pg_namespace on pg_class.relnamespace = pg_namespace.oid
where relname = '<table name>' and nspname='<schema name>'

这篇关于如何获取PostgreSQL数据库的注释？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！