Oof, the subject line was meant to be DROP DEFAULT, not constraint
On Thu, Feb 13, 2025 at 11:13 AM Andrew Atkinson <andyatkin...@gmail.com> wrote: > 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! > > - Andrew >
