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 1rbMYO-00F5xb-I5 for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 15:24:36 +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 1rbMYM-006vVu-9W for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 15:24:34 +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 1rbMYL-006vVm-G5 for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 15:24:33 +0000 Received: from mout.gmx.net ([212.227.17.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbMYE-007EC5-6Z for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 15:24:32 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1708183463; x=1708788263; i=karsten.hilbert@gmx.net; bh=askX+SDEnSrR7Z/3LFpV9nc0AwsVbPTpidsK6r2pD3k=; h=X-UI-Sender-Class:Date:From:To:Subject; b=gyMSc5HtL3T1NJHCZLHvT1VtS7RNTGlIt9fonLKvPwRfTSZeuKiiqGovBKuLQzzt P2n1VMlcR2R9zUbhVEEZOeyihZ2l4WiTWTzEn/JKw5p9GIigkVbK0P6PG+iIPlmmT +I5zAWGKXspDXapfAZqsS5TGkXNj6+hGocyrLEOkKW17ll6jXCQ8BhShSFfsWBhsl IcsLm/WtWZQWg8++0I9Hg5i8Wn362EHwRIsZ7UNZqZlFFc4g5VIsbLjXs5Wh/heeu AwOH7rC4KB8x5o4wZql8ny2dtpd0/yRgShlNlc6oSlNaMSeSJo0ZJFSPcEB2V6Kuy vZ0Rx1OXitKpuPvCXA== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.144.207.30]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M6lpG-1raoae3UNi-008NrG for ; Sat, 17 Feb 2024 16:24:22 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1rbMY9-0002Mu-38 for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 16:24:21 +0100 Date: Sat, 17 Feb 2024 16:24:21 +0100 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org Subject: "reverse" (?) UPSERT -- how to ? Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:nMZViyy3csw479p1sgY/2C7+96ACzqxhyo5AyC0NjCR1mhMPUvj selmjRq/OTqlt/XCqxCEADlwqpkD1aFoH/xHJXELiiWUg2OUAi1JKaxdsbquO4i6qbXpXYL xjkCuJ5u33+H4h2uVRp3rvF2q0tE70WhTAU1JcMyA65UWJCYMWUXTDkfqNnaPfd9YxgqqLe AHjOobNjF9LuGL8AwBliQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:lVthU1JeFrA=;v18niGfWDjuG0UY8tju0lEBOOlS E6stBN/stC4e8YEvrHm4zEajJcqZ+xeLr4W8RT8YvUf0k2O/eIEWsl7mXZq+aQp6KAdqfJJX3 CbqL2m0CGQ02aa82l0m4kc03sOvSnThSVCYo3j1xUUstszpEnxNaFvG9t2EE84FMB7B2WJUas HuAdzph3KT20E4Gd0X4wbqj5Fp2rGd6C2UYZAFKJaVe+V8zhxl3b/6hbsTmRhmcTCjYGHitCg cGY3pu96S7+EwwJOXqsK1X/dv9H7znzkyV3145h/o5pJt0t0plVd91OJzs4qOkQUuW/Ltok/W TiB7GZ4aijy7HEk+zEiVZE6es1VQRe/t8+U0fZvXMrEpgUBbtixbb2ojwfT5nBeQDPel3FIhp YkLghYL6orJmsENAU+9Rxo3l4+ZdDaZnpDAKgyqrnK2YpV6s+skeeI4Mo3dPCUxVHgqddzbPf 4d+j2QSjg4yLBx3NLfvKNlx4nax0fBNwsmdrgmmwkQex7nKIfQ1BbME5iNSlv4xN9WFHRbnhV MN0OwUT/2n2gEhfj0en64YcOba56hVeFvBtIvqOsUgqfOrDByg7cwIZqcJwByhH4qUHs53rWR +12izoRMMxTMq6sFOEZlySZGi/hx1i19dbdm5Hx3Y+AmKpZ1QplHhxMWH3exLP6A+A2Fhxer0 AjXW5JFs3vxTiYMVXyRDi05rTD4hKocDZp7pFHA70PHpvi9N20dAqTILfBAN+Ygt1Vr7/fmQf HiU9yZmMQ2CdUZb0U2gSqxIZCtX7/j6XyV+ptHerYif4Vj1bZsC2fpqepL+lbEy/BQ22ziF3f obhDNqqtsX5zbSJExgdv/4SKSa8+huMWJn+l/BRUdlP0Q= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Dear list members, maybe I am overlooking something. PostgreSQL offers UPSERT functionality by way of INSERT INTO ... ON CONFLICT ... DO UPDATE ...; Consider this pseudo-code schema table master pk_master serial primary key value text ; table detail pk_detail serial primary key fk_master int foreign key master(pk_master) detail text ; where there can be any number of rows in detail linking to a particular row in master, including none (master rows without details, that is). There will be a view giving rows for each detail row enriched with master table data UNION ALL rows for each master row that does not have any detail row with detail ta= ble columns NULLed What I want to achieve: Given a pk_detail (and pk_master) having been obtained from the view (therefore pk_detail being NULL or an integer value) UPDATE that detail row (namely when pk_detail is distinct from NULL) or insert a new detail row (when pk_detail IS NULL) linking that row to the master row identified by pk_master. I know I can do so from client code. I also know I can wrap this functionality inside a plpgsql function. I am wondering though whether it can be done as one SQL statement. It seems to me that would call for an UPDATE ... ON MISSING ... DO INSERT ...; or CASE WHEN pk_detail IS NULL THEN INSERT ... WHEN pk_detail IS DISTINCT FROM NULL THEN UPDATE ... END both of which don't exist/work, of course. So, is this doable within one SQL statement (short of creating and running the abovementioned function in one go ;-) ? (the real story is about medication and intake regimens / schedules thereof involving a whole lot more columns, of course, which should not be relevant to the problem though) Thanks for taking the time to read, Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B