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 1rbNdA-00FAYL-9l for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 16:33: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 1rbNd6-0071C2-Kq for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 16:33:32 +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 1rbNd6-0071BU-7T for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 16:33:32 +0000 Received: from mail-pf1-x42a.google.com ([2607:f8b0:4864:20::42a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rbNcy-007Eak-VY for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 16:33:30 +0000 Received: by mail-pf1-x42a.google.com with SMTP id d2e1a72fcca58-6e3e7d693ebso62702b3a.1 for ; Sat, 17 Feb 2024 08:33:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1708187603; x=1708792403; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=cnRKVlB7kT3WNQBFIuvoOhynI0bjDRlKN/Jb1HqhtuY=; b=g2hGEu/DiPh47t0h6XJWOzGOmQ7f31U6ngIHB7xmiejd4HQswU04njS0+KJ+syeY/+ WML5K1FYM/VsfRrguh3UNSYW/uohCyQH4+nhJCgzzT84ZEVqN9JX/2ENCgh4reZ2Vcv8 CPQ1PTvnaqEo1ioEyZGkfke/9p+zTn88f2vbZf7wMZD6gTxv1QNRna7rXEJAd8ItseVf tGj40GWDOIU06q8V4Ihu8I7rL66TZlEgd0cNvJMfFmn7nVIcZaB6rXpZyR8Z1FtWLUPH EP/vIfd1fL1PFaJYZcHK+Vz2vJA9PxWkzL2Hi5rSkBfPM5kpNDtZQGzb/aokZmkhIsz0 nYFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1708187603; x=1708792403; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=cnRKVlB7kT3WNQBFIuvoOhynI0bjDRlKN/Jb1HqhtuY=; b=BWfuWZzt+L/B9QDU5dAxG34VlfNgHz/PBaA4J6qhXpg+QiXXxgH4tAdnjMIdGHC7sS jxB+ijtdCM7kQ5ElakvSi7Q1Uj/BO5EiXAZdgFyVgpQvnkHobph+JjSICCJc9U2E1RSn nJMKD5xBIs/OIHbACnurpblzdLmDoRgiEFPnkB1l0cTW6oqVGsP8BcQsiXYUIXBZleb7 VnhvASKmcqbR9bWV6x5NifJn6s9+VNStGaNW5pLYndr8QVGhrcDNy5QFaaE74RWIVFOj +EEL7WTH/U5aw/gS6krIr+rysQRuc7b0MYeHxiUNtfZQ4CX1xuP0TmYPxdrWpIP2ZAW5 3anw== X-Gm-Message-State: AOJu0YwIgrF7MnkirrXP2q9xLfHu7uGLdPofOkUiNCH7DVp9haTfe478 vi3aL/NRQFoTdYX+gXoeiWoBw3nyTkuwt7zPxNL4VSrkuixcopEDGa+X+6Av X-Google-Smtp-Source: AGHT+IHlJfZz4oBIgkOR/S4QYOHuC+piosEKj8KQzMd3irOe5xY3SIkYsJCTEd4z0Fc2jVxsNojjrA== X-Received: by 2002:aa7:84c3:0:b0:6e0:5960:861f with SMTP id x3-20020aa784c3000000b006e05960861fmr7654930pfn.17.1708187603164; Sat, 17 Feb 2024 08:33:23 -0800 (PST) Received: from smtpclient.apple ([2601:681:5a00:f790:6941:54a9:d4c4:388a]) by smtp.gmail.com with ESMTPSA id p11-20020a63fe0b000000b005c6617b52e6sm1819070pgh.5.2024.02.17.08.33.22 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 17 Feb 2024 08:33:22 -0800 (PST) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: Rob Sargent Mime-Version: 1.0 (1.0) Subject: Re: "reverse" (?) UPSERT -- how to ? Date: Sat, 17 Feb 2024 09:33:11 -0700 Message-Id: <5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com> References: Cc: pgsql-general@lists.postgresql.org In-Reply-To: To: Karsten Hilbert X-Mailer: iPhone Mail (21C66) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Feb 17, 2024, at 8:24=E2=80=AFAM, Karsten Hilbert wrote: >=20 > =EF=BB=BFDear list members, >=20 > maybe I am overlooking something. >=20 > PostgreSQL offers UPSERT functionality by way of >=20 > INSERT INTO ... ON CONFLICT ... DO UPDATE ...; >=20 > Consider this pseudo-code schema >=20 > table master > pk_master serial primary key > value text > ; >=20 > table detail > pk_detail serial primary key > fk_master int foreign key master(pk_master) > detail text > ; >=20 > 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). >=20 > 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 t= able columns NULLed >=20 > What I want to achieve: >=20 > 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. >=20 > I know I can do so from client code. I also know I can wrap > this functionality inside a plpgsql function. >=20 > I am wondering though whether it can be done as one SQL > statement. It seems to me that would call for an >=20 > UPDATE ... ON MISSING ... DO INSERT ...; >=20 > or >=20 > CASE > WHEN pk_detail IS NULL THEN INSERT ... > WHEN pk_detail IS DISTINCT FROM NULL THEN UPDATE ... > END >=20 > both of which don't exist/work, of course. >=20 > So, is this doable within one SQL statement (short of > creating and running the abovementioned function in > one go ;-) ? >=20 > (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) >=20 > Thanks for taking the time to read, > Karsten Perhaps your pk_detail can be defined as generated always identity? > -- > GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B >=20 >=20