Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1HAX-006ZDm-4K for pgsql-general@arkaria.postgresql.org; Wed, 24 Sep 2025 04:31:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v1HAU-00A3Vt-Ie for pgsql-general@arkaria.postgresql.org; Wed, 24 Sep 2025 04:31:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1HAT-00A3Vh-JB for pgsql-general@lists.postgresql.org; Wed, 24 Sep 2025 04:31:50 +0000 Received: from fout-a2-smtp.messagingengine.com ([103.168.172.145]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1v1HAQ-0027AM-2t for pgsql-general@lists.postgresql.org; Wed, 24 Sep 2025 04:31:48 +0000 Received: from phl-compute-06.internal (phl-compute-06.internal [10.202.2.46]) by mailfout.phl.internal (Postfix) with ESMTP id 0F964EC00E2; Wed, 24 Sep 2025 00:31:46 -0400 (EDT) Received: from phl-mailfrontend-01 ([10.202.2.162]) by phl-compute-06.internal (MEProxy); Wed, 24 Sep 2025 00:31:46 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1758688306; x=1758774706; bh=rRaJTwrqGNXNzc0EmwjW1k8140lSpxyLEcSlEsQXVcM=; b= h0O9D/lvptD+3NFKx3cb6sAEdz7WyT5cW5vLW9BlhQJfCm5ngNoSS6rPcgCYej1F eWMHHSfngMREA8y/7hqQMYB1HmtmnUzz8k/dMMigusOX6lsg4HXhKO+fGofbD6kg pck+0IDIZrEGaV7huNfRpPL3SJ0wQrlsTUn/P4P8obCjL7Uv50UbiFASix5f0rej ik/3FSaAvizwJ2hvPggM4k0Sj6D1EA2CdHQ41dudnzlh5rDUc3mtAaZFKTOhXS10 ivKineSRvO3q28VjZa0j6ejGFYgz3TuZwbzecAvd8LtE9OppWRa2R1hTvyWsfpOV lkftq/MIY1Hp/VLDn3FVqw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1758688306; x= 1758774706; bh=rRaJTwrqGNXNzc0EmwjW1k8140lSpxyLEcSlEsQXVcM=; b=T rKUV30e9KtBRQfdjuKA0Zlo6QZUJq54fISZR2kSYGssgcg7L6Ey+pRKcLSjwBS0C vAkQSjdXqPm+2NaLP6xPMYM4ZovnUQClsixYJaUgkmlM/lRS4+UTD9zPIcNkAPT3 zO4e0Ob5G2/03mpzYNpHReC4X5OWJ5am3IhFx/+qf0jvk+KPU+a+EX7ziNAQzjrG 9EDJiXPnPxYJsoojUHmKGbXOXtgwJVnDP9D5hIfIZdPV1438q9HE+sL11D2SgWY1 bi5jyXtZzod8dkebGMeo/uDVLaGej+rzjukBSa8eN1yKqpQVC9Dul8DuJbsBp0Vc WXefoJ9V5IBnFJZcnI0zw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeffedrtdeggdeivdeihecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpuffrtefokffrpgfnqfghnecuuegr ihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenucfjug hrpefkffggfgfuvfevfhfhjggtgfesthejredttddvjeenucfhrhhomheptegurhhirghn ucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhmqe enucggtffrrghtthgvrhhnpeeuudefjeejgffgtddtgeehgfekueevkeekieettedtkeff heeivdethfevffegueenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgnecuve hluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghn rdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomhdpnhgspghrtghpthhtohepfedpmhhoug gvpehsmhhtphhouhhtpdhrtghpthhtoheprhhoughrihhgohgsuhhrghhoshhmvghllhgr sehgmhgrihhlrdgtohhmpdhrtghpthhtohepshgrmhhuvghlmhgrrhhkshesghhmrghilh drtghomhdprhgtphhtthhopehpghhsqhhlqdhgvghnvghrrghlsehlihhsthhsrdhpohhs thhgrhgvshhqlhdrohhrgh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 24 Sep 2025 00:31:45 -0400 (EDT) Message-ID: <04a9b38c-4872-4f75-b798-c7dbb40eaf5f@aklaver.com> Date: Tue, 23 Sep 2025 21:31:44 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How do I upsert depending on a second table? To: Juan Rodrigo Alejandro Burgos Mella , Samuel Marks Cc: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver 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 9/23/25 17:25, Juan Rodrigo Alejandro Burgos Mella wrote: > Hi Samuel > > Using ON CONFLICT is a headache. Like any tool ON CONFLICT has usage it is best for, if you try to force it do something it was not designed for then it will not perform as expected. Stick to what it good at and it will not be a headache. It is good at moving data into a table where the incoming data is a mix of entirely new rows and changes to existing rows for which there is some sort of arbiter to decide on whether there is a conflict or not. > It's better to use the versatility of a Trigger: you have the full > record at your fingertips, and if you're going to UPDATE, you have the > previous record too. https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT " The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to the row proposed for insertion using the special excluded table. " > There's much more control. > > Also, you can always count on the beloved foreign keys, which are also > quite useful. > > Atte. > JRBM -- Adrian Klaver adrian.klaver@aklaver.com