pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDisable unique constraint in Postgres
9+ messages / 7 participants
[nested] [flat]
* Disable unique constraint in Postgres
@ 2022-11-25 07:18 Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Nikhil Ingale @ 2022-11-25 07:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
We have an alter command to disable any constraints in the oracle db.
Similarly do we have any command to disable the UNIQUE constraint in
postgres. Most of the postgres db forum suggests dropping the UNIQUE
constraint. But, I don't want to drop the constraint here instead just
disable the unique constraint.
Need your inputs on the same.
Regards,
Nikhil Ingale
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-25 07:46 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Nikhil Ingale <niks.bgm@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2022-11-25 07:46 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org
On Fri, 2022-11-25 at 12:48 +0530, Nikhil Ingale wrote:
> We have an alter command to disable any constraints in the oracle db. Similarly do we have any
> command to disable the UNIQUE constraint in postgres. Most of the postgres db forum suggests
> dropping the UNIQUE constraint. But, I don't want to drop the constraint here instead just
> disable the unique constraint.
>
> Need your inputs on the same.
The db forums were right: you cannot disable a unique constraint in PostgreSQL.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-25 13:00 Norbert Poellmann <np@ibu.de>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 9+ messages in thread
From: Norbert Poellmann @ 2022-11-25 13:00 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org
On Fri, Nov 25, 2022 at 08:46:22AM +0100, Laurenz Albe wrote:
> On Fri, 2022-11-25 at 12:48 +0530, Nikhil Ingale wrote:
> > We have an alter command to disable any constraints in the oracle db. Similarly do we have any
> > command to disable the UNIQUE constraint in postgres. Most of the postgres db forum suggests
> > dropping the UNIQUE constraint. But, I don't want to drop the constraint here instead just
> > disable the unique constraint.
> >
> > Need your inputs on the same.
>
> The db forums were right: you cannot disable a unique constraint in PostgreSQL.
Hi,
hope, that I've understood the problem correctly...
At least since v13.x dropping a unique constraint seems to work while
dropping the related index:
-- two constraints added:
np=# create table a (i integer unique constraint hu check(i<100));
CREATE TABLE
np=# \d+ a
Table "public.a"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Indexes:
"a_i_key" UNIQUE CONSTRAINT, btree (i)
Check constraints:
"hu" CHECK (i < 100)
Access method: heap
np=# insert into a values(1);
INSERT 0 1
np=# insert into a values(1);
ERROR: duplicate key value violates unique constraint "a_i_key"
DETAIL: Key (i)=(1) already exists.
np=# insert into a values(2);
INSERT 0 1
np=# insert into a values(101);
ERROR: new row for relation "a" violates check constraint "hu"
DETAIL: Failing row contains (101).
-- So far, so good. It works as expected.
-- Now:
np=# alter table a drop constraint a_i_key;
ALTER TABLE
np=# \d+ a
Table "public.a"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Check constraints:
"hu" CHECK (i < 100)
Access method: heap
-- only constraint i<100 left:
np=# insert into a values(1);
INSERT 0 1
np=# insert into a values(101);
ERROR: new row for relation "a" violates check constraint "hu"
DETAIL: Failing row contains (101).
np=# select * from a;
i
---
1
2
1
-- have to check that against different postgresql versions and docs...
cheers
/np
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-27 16:51 Samed YILDIRIM <samed@reddoc.net>
parent: Nikhil Ingale <niks.bgm@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Samed YILDIRIM @ 2022-11-27 16:51 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Nikhil,
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.
- If you want to violate a constraint temporarily or postpone validity
checking of a constraint, it is possible. It is called DEFERRABLE. You can
delay validation of a constraint until the end of the transaction or until
the end of the statement execution. I'm adding links to related documents
for more details.
https://www.postgresql.org/docs/15/sql-set-constraints.html
https://www.postgresql.org/docs/15/sql-createtable.html
Best regards.
Samed YILDIRIM
On Fri, 25 Nov 2022 at 09:19, Nikhil Ingale <niks.bgm@gmail.com> wrote:
> Hi All,
> We have an alter command to disable any constraints in the oracle db.
> Similarly do we have any command to disable the UNIQUE constraint in
> postgres. Most of the postgres db forum suggests dropping the UNIQUE
> constraint. But, I don't want to drop the constraint here instead just
> disable the unique constraint.
>
> Need your inputs on the same.
>
> Regards,
> Nikhil Ingale
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-27 16:58 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2022-11-27 16:58 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org
> On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM <samed@reddoc.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.)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-27 21:23 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 9+ messages in thread
From: Ron @ 2022-11-27 21:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/27/22 10:58, Scott Ribe wrote:
>> On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM<samed@reddoc.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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-27 21:56 Mladen Gogala <gogala.mladen@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Mladen Gogala @ 2022-11-27 21:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/27/22 16:23, Ron wrote:
>
> 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.
You are aware that in Oracle "ALTER TABLE ENABLE CONSTRAINT" rebuilds
the index used to enforce the constraint? You will not save any time by
disabling and re-enabling constraints.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-27 22:07 Ron <ronljohnsonjr@gmail.com>
parent: Mladen Gogala <gogala.mladen@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ron @ 2022-11-27 22:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/27/22 15:56, Mladen Gogala wrote:
> On 11/27/22 16:23, Ron wrote:
>>
>> 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.
>
>
> You are aware that in Oracle "ALTER TABLE ENABLE CONSTRAINT" rebuilds the
> index used to enforce the constraint? You will not save any time by
> disabling and re-enabling constraints.
>
Eliminating mistakes and reducing DBA work are the point of DISABLE and
ENABLE INDEX, not saving clock time during the maintenance window.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Disable unique constraint in Postgres
@ 2022-11-27 22:23 Samed YILDIRIM <samed@reddoc.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Samed YILDIRIM @ 2022-11-27 22:23 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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@gmail.com> wrote:
> On 11/27/22 10:58, Scott Ribe wrote:
>
> On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM <samed@reddoc.net> <samed@reddoc.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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-11-27 22:23 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-25 07:18 Disable unique constraint in Postgres Nikhil Ingale <niks.bgm@gmail.com>
2022-11-25 07:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-11-25 13:00 ` Norbert Poellmann <np@ibu.de>
2022-11-27 16:51 ` Samed YILDIRIM <samed@reddoc.net>
2022-11-27 16:58 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-27 21:23 ` Ron <ronljohnsonjr@gmail.com>
2022-11-27 21:56 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-11-27 22:07 ` Ron <ronljohnsonjr@gmail.com>
2022-11-27 22:23 ` Samed YILDIRIM <samed@reddoc.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox