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 1pYNLF-0004fL-Sg for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 08:34:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYNLE-00063r-PX for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 08:34:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYNLE-00063b-F4 for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 08:34:08 +0000 Received: from mail-ot1-x32b.google.com ([2607:f8b0:4864:20::32b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYNLB-0005LS-Gr for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 08:34:08 +0000 Received: by mail-ot1-x32b.google.com with SMTP id r23-20020a05683001d700b00690eb18529fso2718011ota.1 for ; Sat, 04 Mar 2023 00:34:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677918843; 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=uuqUfnCRfQ/e9IhXzGKvSWuMOkVpNVkMDvTjTrUbCwQ=; b=U082bnYGcjj11rr56YPmVEBrRThJIDC1HJSotdaLLeUcv1OrxJ5okdWTqXlpFRuBgZ dBsWLuCuk/vLd2DHGAz5n4hPMu/4BZRXcLg/MmD1lrk0yCD7sHdlPHyanom5ZHAwlbGX EfPPhH7AQvYWIkguZ5hsXafUr2bMp0YDc/AQAfEbyb6MSHduLDbHi8Xbb1pgtu6xEx+g wjydfdOfkBBVjnlNGB1lO/SNVTPOEjEP3vz3lUf7+RTBAjXbNU1L64SrEcjUr71+Y4bG svimpxRn+x40lCIwN/0V493ISIpurIo8nY+gCAWf2RfY5d2BGAixZ2GEBvDRvPPGadOW OWUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677918843; 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=uuqUfnCRfQ/e9IhXzGKvSWuMOkVpNVkMDvTjTrUbCwQ=; b=4d2CV9N6sp9YZk/xXfb+MoJ3B4rHbGj+qntGEMgCtuqH2rd9hhr/J3Lpe5eMnqcNTC pjBbZW1FGXwr0xmQRrQIULf5r4XCVg29wvmKgWJS/qLBxji8wpRwAQ706DSYa/7vHgID uW4dpL3k6B/WFdH0ZiC9Ac7Nwne2wfWeepfoC61pUdbbXauDa1MVzt3o8xFCmwDklgE9 heg21DbgCwX+EDJpAMPap98SXUDoal3E9i8xoBs6ytGXZdY23Y6Om/D+RMXHLJkg77ok bBnmd94mY6vysnL6YwVNUelDm4kZ9z6WlLZVTLAwXlxaF4V+uObwCfsXAJvNoG1zp4Ge bRvA== X-Gm-Message-State: AO0yUKX8SCM+rL8PQBFR4WYn0xYdJGNiP7t8EJQ5S3rK4SwaHyhqh6jf pdyWbxS+xTUhA78xr5UgaWG37oylFvE= X-Google-Smtp-Source: AK7set/z/1u0kppT3cjYfMK8d830iYCd+qx6F+DlFjv36JouSyqwxs1Q8X+lS4rA8DsXlqpSHWrYow== X-Received: by 2002:a05:6830:4105:b0:68b:e2a3:8ef1 with SMTP id w5-20020a056830410500b0068be2a38ef1mr2387253ott.35.1677918843454; Sat, 04 Mar 2023 00:34:03 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id z18-20020a9d71d2000000b0068bd9a6d644sm2025462otj.23.2023.03.04.00.34.02 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Mar 2023 00:34:03 -0800 (PST) Message-ID: <0d0e4771-a1f2-f883-a983-4a4aee1bce9b@gmail.com> Date: Sat, 4 Mar 2023 02:34:02 -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> From: Ron In-Reply-To: <20230304080303.a3oimbvcoo5lo3d5@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 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... (Autocommit and the default transaction mode not being SERIALIZABLE were also a shock when I started using other systems.) -- Born in Arizona, moved to Babylonia.