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 1spQ2l-001LRY-RH for pgsql-general@arkaria.postgresql.org; Sat, 14 Sep 2024 10:30:21 +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 1spQ2l-009knj-2d for pgsql-general@arkaria.postgresql.org; Sat, 14 Sep 2024 10:30:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1spQ2k-009knb-Am for pgsql-general@lists.postgresql.org; Sat, 14 Sep 2024 10:30:18 +0000 Received: from mail-40136.proton.ch ([185.70.40.136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1spQ2g-0018b0-0K for pgsql-general@postgresql.org; Sat, 14 Sep 2024 10:30:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kortschak.io; s=protonmail; t=1726309813; x=1726569013; bh=9Bm4A6Mi7FKssrN30WLcIaaHvo0s6mU2bW6uiSRHx6E=; h=Date:To:From:Cc:Subject:Message-ID:In-Reply-To:References: Feedback-ID:From:To:Cc:Date:Subject:Reply-To:Feedback-ID: Message-ID:BIMI-Selector; b=sRFauFjeb9++CZoXPWFj4ivoBQKLk8VIGuHSVEH3y1bXRinmgFrSJK+l+RqgeV1pg 4iVlUWMo6Fd/SzUdPrBHzmS52ErKrylVRCaDG61IJv+24DDcREFIynGqjLQ0WLtz4q V3RxTVqQR43TO77pyRTYmh5q9wZal6DQcVfMKYGwif2ZstXw7UuB0ZcmjKnLd7BmtK cyLnTDvCma5FX0sDS9XJvm/haHOmTMmNIvAddi65Zp/MnYzzIs3Z93gzok0IgcpiGE Qy3W6slM0eMYfPNgyzlsG4OzCZ/enoWiZN9QlbTWAL8f+9JTZTH33K5j2yEgr82on6 JzEYRwsyIqnxA== Date: Sat, 14 Sep 2024 10:30:08 +0000 To: Alban Hertroys From: Dan Kortschak Cc: PostgreSQL General Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Message-ID: <48019d21fd7c669afb83161337f7e007ae619534.camel@kortschak.io> In-Reply-To: <7320B905-E950-4035-9A13-8C9447AB52EC@gmail.com> References: <1aea530e4c96109b91fdde53a29e38beb625dc77.camel@kortschak.io> <20240723221121.51f2b3872d0ebfc36a6fa8ff@wanadoo.fr> <2f11b2ba-3182-492a-ab46-23cfa5ec913c@aklaver.com> <735913e4c9601a2a7ad57d2253d74451c42ba899.camel@kortschak.io> <21078220628bcebca0395a52d42b558ed965bbc0.camel@kortschak.io> <5c86256a4e83b1af73da22faab54889564e93129.camel@kortschak.io> <7320B905-E950-4035-9A13-8C9447AB52EC@gmail.com> Feedback-ID: 18519778:user:proton X-Pm-Message-ID: e810b50abfa42e2eb41b546283d7edb7a4ad1451 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, 2024-09-14 at 12:05 +0200, Alban Hertroys wrote: >=20 > That=E2=80=99s because the replacement data is an array of objects, not a > single object. >=20 > You need to iterate through the array elements to build your > replacement data, something like what I do here with a select > (because that=E2=80=99s way easier to play around with): >=20 > with dollar6 as ( > select jsonb($$[ > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 { > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 "data": { > "foo": 1, "bar": 2 > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 }, > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 "end": "2023- > 06-12T19:54:51Z", > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 "start": > "2023-06-12T19:54:39Z" > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 } > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 ]$$::text) replacement > ) > select * > from dollar6 > cross join lateral jsonb_array_elements(replacement) r > where (r->>'start')::timestamptz <=3D current_timestamp; Thanks