Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYZwy-000581-5V for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 22:01:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYZwv-0003Vd-Mg for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 22:01:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYZwv-0003VU-5a for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 22:01:53 +0000 Received: from mail-oa1-x31.google.com ([2001:4860:4864:20::31]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYZwo-0005FY-Co for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 22:01:52 +0000 Received: by mail-oa1-x31.google.com with SMTP id 586e51a60fabf-17235c8dab9so7262502fac.7 for ; Sat, 04 Mar 2023 14:01:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677967305; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=vXnXdT2JZ/k9GAnyx3nEwzaUG9CSmawBuIEu5owXXec=; b=pzDEyS7pxu2Yvxk9oUh7V7uXolG52DCscRY/keY2obg2uwoXb+vDU5c2SIz0GHXCSP otqCmOUloXbW29MFHJS/nco+FhzJH38VioiM0w9QvWA97R3rdMQx7JLSluAYYBxU+l2L Is1YYX70gS19m2ZiJzUbLZeaz39l5MTIKWtM0WX3J8++jKvN+Q3LmApREc33cF60kVzo GKQbd34kxHNEZvWE5k4av45/9H16QrTvlXgPUJZUCWk51fd6xBQFvwGLNtRWwA6PWXa8 4BzXn2TwLUFZtHs7hplgt21JKrq4WaOGfrT36r2SYtpZ/93gnBNZbbCZSgu614rV8mng U8AQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677967305; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=vXnXdT2JZ/k9GAnyx3nEwzaUG9CSmawBuIEu5owXXec=; b=wEIWz4uH5b+QPNUCxpys/SjMWZvTOZb65ARt/wVlnkOOUkHWBAg24iZN003Qaf62jo zlS9eaghwiPYvuPcOIeYVSymoYM63TKmh3KqIju0nZKcSSI1441XHVoV2bpyrstnPqyN mcmC7JIirdREqxVmelbwPV1Ir64+iEHK5aIXPVVqmnx85FvILnyGB8D8m3/GqcGHf2tL vLW75L1sVNBSkR3bakI99nCKp2j9UehZB0h7xP//kFIQ0fZsTRNxgo+cKV90Bk650ANu IXDNalbkXiKUimQttKSkZdekhpf69McAaPulquGVXJmeQbbdYfW8qP6mtVYhwE5p8o8z S7bg== X-Gm-Message-State: AO0yUKWBXGPIToDxx5EH3yYnoI/cFxDzoU4X0SU2Bm1JteO0sGSfru/n nelZqSPkzt2TdMPKVHWKkGCKcMfwgU0= X-Google-Smtp-Source: AK7set+/itAxG+QwmObUy/5NIPbRinDVKQOpANlfOH/FG96ggqIfetr6nkfaqz8uB/Ykt5wJ8QY7sw== X-Received: by 2002:a05:6870:4393:b0:175:72b6:ec4d with SMTP id r19-20020a056870439300b0017572b6ec4dmr4721398oah.25.1677967305446; Sat, 04 Mar 2023 14:01:45 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id v2-20020a056830140200b006941ecefc34sm2542383otp.18.2023.03.04.14.01.44 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Mar 2023 14:01:45 -0800 (PST) Message-ID: <4933fde5-f139-0d4d-13ed-b4ab96ffb537@gmail.com> Date: Sat, 4 Mar 2023 16:01:44 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Dropping behavior for unique CONSTRAINTs Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com> <20230304080303.a3oimbvcoo5lo3d5@hjp.at> <0d0e4771-a1f2-f883-a983-4a4aee1bce9b@gmail.com> <20230304115152.df5pmrgny7z5ogkm@hjp.at> From: Ron In-Reply-To: <20230304115152.df5pmrgny7z5ogkm@hjp.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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.