Add Column Comments in PostgreSQL and MySQL

Yes, you can add or change a column comment in both PostgreSQL and MySQL without altering the column’s data type, default value, or any stored data. The operation is purely a metadata change, so it does not rewrite the table or lock it for longer than a brief moment in most cases. Below is a detailed guide covering the exact SQL syntax for each database, how the underlying storage engines handle it, and the practical considerations you should know before running the command.

Why Column Comments Matter

Column comments are short text descriptions stored in the database catalog. They serve as inline documentation for developers, data analysts, and future maintainers. Unlike a separate README file, comments travel with the schema and can be exported with tools like pg_dump (PostgreSQL) or mysqldump (MySQL). They appear in many database GUIs, in information_schema queries, and in auto-generated API documentation. A well-commented schema reduces onboarding time and prevents misinterpretation of ambiguous column names like status or value.

Both databases allow comments on tables, columns, and (in PostgreSQL) on constraints, indexes, and other objects. The syntax is deliberately simple, and the operation is considered a DDL (Data Definition Language) statement, but it does not touch the actual data rows.

PostgreSQL: COMMENT ON COLUMN

PostgreSQL uses the COMMENT statement, which is part of the SQL standard. To add or replace a comment on an existing column, run:

COMMENT ON COLUMN table_name.column_name IS 'Your comment here';

For example:

COMMENT ON COLUMN users.email IS 'Primary contact email, unique per user';

To remove a comment, set it to NULL:

COMMENT ON COLUMN users.email IS NULL;

PostgreSQL stores comments in the pg_description system catalog. The command acquires an ACCESS EXCLUSIVE lock on the table, which sounds heavy but is actually very short because it only updates the catalog entry. In practice, on a busy table, the lock is held for milliseconds, but it can queue behind other long-running transactions. If you have a high-write environment, you may want to run it during a maintenance window or use LOCK_TIMEOUT to avoid blocking.

MySQL: ALTER TABLE … COMMENT

MySQL does not have a standalone COMMENT ON statement. Instead, you use ALTER TABLE with the MODIFY COLUMN clause and include the comment. The critical part is that you must repeat the full column definition exactly as it currently is, plus the new comment. For example:

ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL COMMENT 'Primary contact email';

If you omit or change any part of the column definition (e.g., drop NOT NULL), you will unintentionally alter the column. To avoid mistakes, first inspect the current definition:

SHOW CREATE TABLE users;

Copy the column’s type, nullability, default, and any attributes (like AUTO_INCREMENT) into the MODIFY statement. To remove a comment, set it to an empty string:

ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL COMMENT '';

In MySQL 8.0, the INFORMATION_SCHEMA.COLUMNS table has a COLUMN_COMMENT field that you can query to verify the change.

How the Databases Handle It Internally

In PostgreSQL, comments are stored in the pg_description catalog, which is a separate system table. The table’s physical data file is untouched. The operation is transactional, so if you roll back, the comment change is undone. In MySQL, the comment is stored in the table’s .frm file (in MySQL 5.7 and earlier) or in the Data Dictionary (in MySQL 8.0+). In InnoDB, the ALTER TABLE with MODIFY COLUMN may trigger a table rebuild if the column definition changes, but if the definition is identical except for the comment, MySQL 8.0 uses an “in-placeâ€� algorithm that only changes metadata. However, the exact behavior depends on the storage engine and the ALGORITHM clause you specify. To force metadata-only change, you can use:

ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL COMMENT 'New comment', ALGORITHM=INPLACE, LOCK=NONE;

This tells MySQL to avoid copying the table and to allow concurrent reads and writes. If the column definition is exactly the same, this should succeed. If you omit the clause, MySQL will choose its own algorithm, which may be more conservative.

Common Mistakes and How to Avoid Them

• Typing the wrong column definition in MySQL – Always copy from SHOW CREATE TABLE to avoid dropping a default or null constraint.
• Using single quotes inside the comment – Escape them by doubling the quote ('') or using dollar-quoting in PostgreSQL (e.g., $tag$...$tag$).
• Expecting comments to be visible in SELECT * – Comments are not returned in query results; they are metadata.
• Forgetting that comments are not versioned – If you change a comment, the old one is lost. Consider using a migration tool that logs changes.
• Assuming comments are exported by default – In MySQL, mysqldump includes comments only if you use the --comments option. In PostgreSQL, pg_dump includes them by default.

Performance and Locking Considerations

Adding a comment is a metadata operation, but the lock behavior differs. In PostgreSQL, the COMMENT ON acquires an ACCESS EXCLUSIVE lock, which blocks all other operations on the table until the transaction commits. Because the operation is fast, the lock is usually brief, but on a table with many concurrent transactions, it may wait. In MySQL, the ALTER TABLE with ALGORITHM=INPLACE and LOCK=NONE allows concurrent DML, but if the server decides to rebuild the table (e.g., because the column definition differs), it will take a shared lock and copy the entire table, which can be very slow on large tables. To avoid this, double-check that the column definition matches exactly. In both databases, the operation does not change the physical order of rows or the size of the data files, so there is no risk of data corruption or loss.

Alternatives and Best Practices

If you need richer documentation than a short comment, consider using a schema documentation tool like SchemaSpy or a migration tool like Flyway or Liquibase, which can manage comments as part of versioned migrations. Some teams also use extended properties in SQL Server, but for PostgreSQL and MySQL, the built-in comment is sufficient for most cases. A good practice is to write comments that explain the “whyâ€� rather than the “whatâ€�. For example, instead of user_id, write Foreign key to users.id; null if anonymous. This adds context that the column name alone does not convey.

Frequently Asked Questions

Can I add a comment to a column without locking the table?

In PostgreSQL, the lock is exclusive but very short; you can use LOCK_TIMEOUT to avoid waiting indefinitely. In MySQL, you can use ALGORITHM=INPLACE, LOCK=NONE to allow concurrent reads and writes, but only if the column definition is unchanged. If the server decides to rebuild the table, it will lock. Test on a staging environment to see the actual behavior.

Does adding a comment change the column’s data type or default value?

No, if you use the correct syntax. In PostgreSQL, the COMMENT ON statement only touches the comment. In MySQL, you must copy the exact column definition; if you do, the data type, default, and nullability remain unchanged. If you accidentally omit an attribute, it will be reset to the default.

How do I see existing column comments?

In PostgreSQL, query pg_description or use \\d table_name in psql. In MySQL, run SHOW FULL COLUMNS FROM table_name or query INFORMATION_SCHEMA.COLUMNS. Many GUI tools display comments in the table editor.

Can I add comments to multiple columns at once?

In PostgreSQL, you need separate COMMENT ON statements for each column. In MySQL, you can use a single ALTER TABLE with multiple MODIFY COLUMN clauses, but you must repeat the full definition for each column. Example: ALTER TABLE t MODIFY a INT COMMENT 'A', MODIFY b VARCHAR(10) COMMENT 'B';

Do comments affect query performance?

No. Comments are stored as metadata and are not read during query execution. They have no effect on index usage, query plans, or row retrieval.

What happens to comments when I rename a column?

In both databases, renaming a column preserves its comment. In PostgreSQL, ALTER TABLE ... RENAME COLUMN keeps the comment associated with the column object. In MySQL, the comment follows the column definition, so it remains after a rename.

Related

• Mini LED vs OLED for Gaming: HDR, Response, Burn-In
• Show Hidden Files Mac Finder: 3 Ways
• Set Time Limit App iPhone iOS 12

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2