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.
Ok, thanks to your help, I found the following commands pretty useful:
To delete a comment from a given column position of a specific object (here, mytable), you could go:
DELETE FROM pg_description WHERE (SELECT relname FROM pg_class WHERE oid=objoid)='mytable' AND objsubid=2;
...but note that it's not more efficient than using COMMENT ON mytable.myfield IS NULL;
Now, to delete all comments from my user-defined views and underlying columns, here's what works very well:
DELETE FROM pg_description WHERE (SELECT relkind FROM pg_class WHERE oid=objoid)='v' AND (SELECT relname FROM pg_class WHERE oid=objoid) ~ 'v_';
where:
If you want to do this without hacking a system table, then this will generate the statements for you:
SELECT 'COMMENT ON COLUMN ' || quote_ident(pg_namespace.nspname) || '.' || quote_ident(pg_class.relname) || '.' || quote_ident(columns.column_name) || ' IS NULL;'
FROM pg_description
JOIN pg_class ON pg_class.oid = pg_description.objoid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN information_schema.columns
ON columns.table_schema = pg_namespace.nspname
AND columns.table_name = pg_class.relname
AND columns.ordinal_position = pg_description.objsubid
You should probably add a WHERE clause that constrains it to your schemas.
pg_descriptionand deletes coments with dynamic SQL. It will be much faster than any bash script.