从PostgreSQL中的所有对象中删除COMMENT ON [英] Removing COMMENT ON from all objects in PostgreSQL

问题描述

与 pg_dump一样，对对象没有评论吗?是否有人知道有一条命令可以快速消除所有对象上的注释(使用 COMMENT ON 创建的命令)?

In the same vein as pg_dump without comments on objects?, is anyone aware of a command to quickly get rid of the comments (created with COMMENT ON) on all objects at once ?

就目前而言，我依靠bash生成一个SQL脚本，该脚本将逐个删除每个表/视图/列上的注释，但是它非常慢，尤其是对于> 4000列.示例:

For now, I resorted to bash generating a SQL script that would void one by one the comments on each table/view/column, but it is quite slow, especially with >4000 columns.
Example:

COMMENT ON COLUMN table1.column1 IS NULL;
COMMENT ON COLUMN table1.column2 IS NULL;
COMMENT ON COLUMN table1.column3 IS NULL;
...

推荐答案

前段时间我遇到了一个非常类似的问题，并提出了一个非常简单的解决方案:从系统目录表中删除

I have faced a very similar problem some time ago and came up with a very simple solution: delete from the system catalog table pg_description directly. Comments are just "attached" to objects and don't interfere otherwise.

DELETE FROM pg_description WHERE description = 'something special';

免责声明:
直接操作目录表是危险，并且是不得已的措施.您必须知道自己在做什么，而这样做的后果自负！如果搞砸了，您可能搞砸了数据库(集群).

Disclaimer:
Manipulating catalog tables directly is dangerous and a measure of last resort. You have to know what you are doing and you are doing it at your own risk! If you screw up, you may screw up your database (cluster).

我在pgsql-admin列表上询问了这个想法，并得到了

I asked about the idea on pgsql-admin list and got an encouraging answer from Tom Lane:

> DELETE FROM pg_description WHERE description = 'My very special
> totally useless comment.';

> AFAICS, there are no side-effects. Are there any?

It's safe enough, as long as you don't delete the wrong comments.
There's no hidden infrastructure for a comment.

            regards, tom lane

您应该确保没有要保留的任何评论.首先检查您要删除的内容.请注意，许多内置的Postgres对象也有注释.

You should make sure that there aren't any comments you'd rather keep. Inspect what your are going to delete first. Be aware that many built-in Postgres objects have comments, too.

例如，要仅删除表列上的所有注释，您可以使用:

For instance, to only delete all comments on table columns, you could use:

SELECT *
-- DELETE
FROM   pg_description
WHERE  objsubid > 0;

该手册提供有关 objsubid列的信息:

对于表列的注释，这是列号( objoid 和 classoid 是指表格本身).对于所有其他对象类型，此列为零.

For a comment on a table column, this is the column number (the objoid and classoid refer to the table itself). For all other object types, this column is zero.

这篇关于从PostgreSQL中的所有对象中删除COMMENT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！