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 ?
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).
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
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;
The manual informs about the column objsubid:
For a comment on a table column, this is the column number (the
objoidandclassoidrefer to the table itself). For all other object types, this column is zero.