From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Dropping behavior for unique CONSTRAINTs |
Date: | 2023-03-04 22:01:44 |
Message-ID: | 4933fde5-f139-0d4d-13ed-b4ab96ffb537@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/4/23 05:51, Peter J. Holzer wrote:
> On 2023-03-04 02:34:02 -0600, Ron wrote:
>> On 3/4/23 02:03, Peter J. Holzer wrote:
>> [snip]
>>> So your plan is to create a unique constraint (backed by a unique
>>> index) and then to drop the index and keep the constraint?
>>>
>>> That doesn't work. A unique constraint can't exist without a (unique)
>>> index. Think about it: With a unique constraint PostgreSQL needs to
>>> check for every insert whether the value already exists in the table.
>>> Without an index this would mean a full table scan.
>> I cut my teeth on an RDBMS which didn't automagically create a backing
>> index. You had to do it yourself...
> Just curious: Which RDBMS was that?
Rdb/VMS (the DEC product for OpenVMS, which has been owned by Oracle for the
past 25 years).
> Speaking of foreign key constraints: Neither Oracle nor PostgreSQL
> automatically add an index on a foreign key. That bit me hard back in
> the day ...
Us too. (Well, the developer, from before I arrived.)
--
Born in Arizona, moved to Babylonia.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-03-05 00:20:29 | psql \set variables in crosstab queries? |
Previous Message | Thorsten Glaser | 2023-03-04 20:00:33 | Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y |