Hello. I noticed a small opportunity for a possible enhancement to DROP DEFAULT, and wanted to share the idea. Apologies if this idea was suggested before, I tried a basic search for pgsql-hackers similar things but didn’t find a hit.
I noticed when running an ALTER TABLE with DROP DEFAULT, whether the column default exists or not, ALTER TABLE is always printed as the result. This is arguably slightly confusing, because it’s unclear if anything was done. In the scenario where there is no column default, there isn’t a message saying “skipped” or something equivalent, indicating that there was no default that was dropped. Some of the commands in Postgres do have this kind of feedback, so it seems like an opportunity for greater consistency.
For example: if I create a column default, or repeatedly run the following ALTER TABLE statements for the "id_new" column, I always get ALTER TABLE back.
ALTER TABLE my_table ALTER COLUMN id_new DROP DEFAULT;
ALTER TABLE
ALTER TABLE my_table ALTER COLUMN id_new DROP DEFAULT;
ALTER TABLE
ALTER TABLE my_table ALTER COLUMN id_new DROP DEFAULT;
ALTER TABLE
An opportunity would be to add a NOTICE type of message when ALTER TABLE ALTER COLUMN DROP DEFAULT is issued, at least when no column default exists, and no action was taken. In that scenario, the operation could possibly be skipped altogether, which might have some additional benefits.
As a refreshed on a “Notice” type of message example, here’s one when adding an index and using the "if not exists" clause (an equivalent "if not exists" clause does not exist for DROP DEFAULT to my knowledge):
-- an index called “foo” already exists
psql> create index if not exists foo on organizations (id);
NOTICE: relation "foo" already exists, skipping
CREATE INDEX
The message being “NOTICE: relation "foo" already exists, skipping”
A similar message for DROP DEFAULT might look like:
“NOTICE: default does not exist, skipping”
Or an alternative that includes the column name might look like:
“NOTICE: default does not exist for column id_new, skipping”
Or another alternative might be a new (non-standard?) "if exists" clause for DROP DEFAULT. Example:
ALTER TABLE my_table ALTER COLUMN id_new DROP DEFAULT IF EXISTS;
-- Or an alternative placement of the "if exists" clause, because I don’t really know where it would go:
ALTER TABLE my_table ALTER COLUMN id_new DROP IF EXISTS DEFAULT;
Thanks!
