Re: DROP CONSTRAINT, printing a notice and/or skipping when no action is taken

Oof, the subject line was meant to be DROP DEFAULT, not constraint

On Thu, Feb 13, 2025 at 11:13 AM Andrew Atkinson <andyatkinson(at)gmail(dot)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
>