Re: Disable unique constraint in Postgres

Hi Ron,

Dropping and creating indexing can also be automated, and it will prevent
any kind of human error caused by typing errors or forgetting one of
indexes. I support the idea of things that make our life easier. However, I
don't think this is one of the nice-to-have features.

To get back to the topic, if you want to achieve such a task and prevent
human error on definition of indexes or typing error or something like
that, there is a cool function in Postgresql that returns index's create
statement :)

postgres=# create table t1 (id serial primary key, c1 int);
CREATE TABLE
postgres=*# create index t1_test_idx on t1 (c1);
CREATE INDEX
postgres=*# select pg_get_indexdef('t1_test_idx'::regclass);
pg_get_indexdef
--------------------------------------------------------
CREATE INDEX t1_test_idx ON public.t1 USING btree (c1)
(1 row)

Best regards.
Samed YILDIRIM

On Sun, 27 Nov 2022 at 23:23, Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> On 11/27/22 10:58, Scott Ribe wrote:
>
> On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM <samed(at)reddoc(dot)net> <samed(at)reddoc(dot)net> wrote:
>
> Important point is why you want to disable a unique constraint.
> • If you want to add some duplicate rows into a table, you try to do something fundamentally wrong.
>
> Even more so: why is disabling superior to dropping???
>
> It is at most an extremely minor convenience to be able to re-enable it without having to re-create it, in that you can, presumably, just re-enable by name without specifying the constraint details. In other words, in my opinion, it is a virtually worthless feature. (Typical "enterprise software" feature creep.)
>
>
> Being an "enterprise dba", IMNSHO "disable constraint" (and more
> specifically "*disable index*") is a *great* feature for maintaining
> tables. For example, if you need to purge a *lot* of records into a
> table, disable all indices except the index supporting your WHERE CLAUSE,
> delete the data, and then re-enable the indices. Bonus points if the
> REENABLE commands can be done in parallel.
>
> Sure, you *can* dig around for all of the CREATE INDEX statements, but
> that leads to possible errors: "oops, forgot to recreate one of them", or
> "typo caused one to fail". OTOH, DISABLE INDEX and REENABLE INDEX are
> idiot-proof, and can be automated.
>
> --
> Angular momentum makes the world go 'round.
>