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 1plh4s-0005lg-Jk for pgsql-general@arkaria.postgresql.org; Mon, 10 Apr 2023 02:16:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1plh4r-00033c-AG for pgsql-general@arkaria.postgresql.org; Mon, 10 Apr 2023 02:16:17 +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 1plh4q-00033S-T4 for pgsql-general@lists.postgresql.org; Mon, 10 Apr 2023 02:16:16 +0000 Received: from mail-pj1-x1032.google.com ([2607:f8b0:4864:20::1032]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1plh4k-001wbK-1U for pgsql-general@lists.postgresql.org; Mon, 10 Apr 2023 02:16:16 +0000 Received: by mail-pj1-x1032.google.com with SMTP id bp17-20020a17090b0c1100b0023f187954acso3283584pjb.2 for ; Sun, 09 Apr 2023 19:16:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1681092967; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=9dnG+nrwoVBDwJ5ukn+VDeZhVwkdFVoAMKUhAubM+yo=; b=cPrrQnaCxqmvHhpQ/uqkduMpz4EM4QUlJgZjndGNcAY6Tm2TUhdGZ59JWV3QfaWxNU qtxuX58SUlfEPD21OG6+8SG726ZWVaFepyuKHD2GaC1qgAgvuXJe8C1EhswXhp5BTnh/ bJI7xxmPnwZRwSe0hph7u6nbQhFrIE7HRO6blZ7c9E/2LwhMSzoiWvmqQcAtPOYDueE5 BsPLA+JcPWroVS6m7SYWwdKZl80ll90GwhhFlASbGsaJKxHE9i0e2iL82HoXztKSyKGS 61WUtCOv9e7cCr8AX8eq9KNur3yZrlSL2+o/jlE1DNZ8k8W2sUC4QWc8MF+L0qmC+aCS i8OQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1681092967; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=9dnG+nrwoVBDwJ5ukn+VDeZhVwkdFVoAMKUhAubM+yo=; b=kWyJS25/fpkpQRgPVn1TpBniHLcaJc8Kc3UwhM550Laua7raqzrxxP8ilT7lUxX31v DjiK+chQi+1xOGjDZCqAaFl4sAmiiGlP0VZtliBvY8PxrHBzRwVfoOKYVkGXZacTw6qg 4qhC3qZKkWTAUYa4ZHbWIUEpNUmAqMJKtK0vG7bA+F7hQQGQKdy70IxgbdujYnqAcEFW iqDGU52afOt/Z4vSpaMkr9O+MTMDYi6wKnC8Tz9inS0l09fKVJQTr0fChaN5n9PUmBTI viIqp8qvo15y8GT/kpsiULxguSyU1wqnEQPoh7AH3qiJktElKpdVA2oUm7R0VAV0t4NT 3lxw== X-Gm-Message-State: AAQBX9e5Pa86A/LSxY3pVUTmGw56tHljmB1natLcuAc7hiSpMI++n14E jVll/iTtWbCkP9ViJD6pCKNIqoJsl24= X-Google-Smtp-Source: AKy350Zh7Di++6RfgbwpRF3Eq3VpqV1N9toPETPJK8SZD6OdgA30CILuI76cbGAeqknHh/bczDSWfQ== X-Received: by 2002:a17:903:1250:b0:1a5:a1b:bbd8 with SMTP id u16-20020a170903125000b001a50a1bbbd8mr13004133plh.45.1681092967116; Sun, 09 Apr 2023 19:16:07 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:3e16:fcbd:215e:6776? ([2601:681:5a00:f790:3e16:fcbd:215e:6776]) by smtp.gmail.com with ESMTPSA id u5-20020a17090282c500b001a3d041ca71sm6485706plz.275.2023.04.09.19.16.06 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 09 Apr 2023 19:16:06 -0700 (PDT) Message-ID: Date: Sun, 9 Apr 2023 20:16:05 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres To: pgsql-general@lists.postgresql.org References: Content-Language: en-CA From: Rob Sargent In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/9/23 19:55, Louis Tian wrote: > Hi Alban, > > "I am not expecting an error here", by "here" I means when doing a TRUE UPSERT (an upsert current does not exist in Postgres). > I am NOT referring to an "Insert on conflict do update" (which despite its intention and wide acceptance is not fully equivalent to a true upsert). > I understand the error I am getting now is due to not null constraint given how "insert on conflict" works. > > An UPSERT checks whether a row exists, if so, it does an update, if not it does an insert. This is the literal definition. This the part that's always eluded me: How does the client, the UPSERTer, come to hold an id and not know whether or not it's already in the database.