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 1rbPRT-00FHmv-Gc for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 18:29:39 +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 1rbPRS-007kFL-0G for pgsql-general@arkaria.postgresql.org; Sat, 17 Feb 2024 18:29:38 +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 1rbPRR-007kFC-HQ for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 18:29:37 +0000 Received: from mout.gmx.net ([212.227.15.19]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbPRO-007FQn-Vi for pgsql-general@lists.postgresql.org; Sat, 17 Feb 2024 18:29:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1708194572; x=1708799372; i=karsten.hilbert@gmx.net; bh=FNvY4JRLP7O+cPeE8IbQZTDKG9pkkype4NYZXszn+WM=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References: In-Reply-To; b=gZal/LQdN2TSQSr7Zyi70JtB/dEvp3lq2h2z0pozavskZWJ/7xOnHFEUyYDfzyLp 2/bNTwvg7FHLGS8pLzGReHZhHwGNq+DmIKzkTrW/Y6Ehlm4pNWRvIExSWFgJk01wa R0B2MbloNvm/tw7O93ta9kBXBGEW7eJ8LjFf18Vk4rWPHWoLTnWEPTR9AaNrYzHQg HHAV1WxGYtnqKv5vjz0FqMmKcon/Z2S/u7qm9GtYutj4DbFFcFq+nOOqxHrGxGZ+q 2XJ746pLg8cIBNYM2/L+CLdUWq4mqfViwg+O+H9+o7umVSRGtf8Q5opS8YbJRyYe1 siFMGdx4BBIYR3Cflg== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.144.207.30]) by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MOzT4-1rIcXQ26km-00PNDH; Sat, 17 Feb 2024 19:29:32 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1rbPRM-0001nz-0N; Sat, 17 Feb 2024 19:29:32 +0100 Date: Sat, 17 Feb 2024 19:29:32 +0100 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org Cc: Greg Sabino Mullane Subject: Re: "reverse" (?) UPSERT -- how to ? Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="qlT60X6Y4Yx+rs5J" Content-Disposition: inline In-Reply-To: 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:UjcyV0tk1F/xvi6Cl+ToUwefKYc/wAtgN8pidD4sOWnGNtYsQlW xQcybyg5qBY4xIIAG1Q1UOcETD7sV3qYGa5GA1fKuScoiTTj9Ca5Ej6qHq7FUoshBuD45U4 BH7YXBt3KGTxPGuo70TduL5kEv98E2Bxs8/lAq8gdBruBe+kx0b8L8dwPtT17X76a4C8gkr QpWIgr92DOL6tCNqpBDwQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:ItnlkyEHGng=;JRuVZWM8qXWebSRK5df5tbk//2R v9E+ZIFNYJZ/FG7TGNBkEM2vZmTRiXAeS3HXQOKNCvJEidQ9CxJzbQz6PVaAstXRrzVk3qr+v CSeT2qatueAAzKQVeXTPoiOuvF2PqAQzHDu9zkLhQxGEK2hWdnmw20o9k1wHulvfMPzcbEf0f 47DpZ+ltPRiYe8MBKjubO0wcNYCxjo7Do4nrk9licz3KwZQROjwqUs8+8+b1QDOX2mIajVR4T Q8fg1OuMSYL64SbcnKs8bQzhjnXkPdlKuSFeme8sgIZGNxq/ccM6vHRhjhOo55klrZ8xK84/z fzZ0pMZNIxo1ImVobgO4Hvq4MvBlrmfdaSGkP0k9wSTu8DqpuQzYcyTVTRn+qPQOYejiHX+gV orZh04dBGvBvLgcBgkBD9u4xlbblz+F9Geik/RDROIZA0tVRJ2VNh74kBfyMJNI8HHaSnkJ2F LhsbhQuGIJ7cCiHqS+vyOC0KsaBKZcgfSoYotOUL6gUbzECXcdSkzk8ipKPHrTmTr1lChAmzQ N/RU955DxL4oe1x02AkR7YrfRU4png7cWMuegSEALij0/U/Y5soHxNbKh1IJXZQ2CfYkyP/6p SONox+ZnZ+EZoh3yJyQ5KB4GDN3y7D447dl2+9yhC/+pSDX75TvYdrn5OBq/wBIA6xtlvbLii RE4WadRIWlTj24o7ztCN6iruVdlwLNtYOtUDfCIqAOTibJPXP/BpbZPN975Ogj9+8xSqhB3m7 KgB0dYAbg4PW3VIPQJzdyTUyhIDkKY6Aepd5cnACmi+0duw3DTM+tXok9u18Brj7dLitNSwAU OMjzqlcczqp/1sw3Jtclf3IRRaqtWkQcSDsYfXnhBTCiM= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --qlT60X6Y4Yx+rs5J Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Am Sat, Feb 17, 2024 at 07:25:11PM +0100 schrieb Karsten Hilbert: > +1 except that I've got use for both parts of the UNION on > their own (they are both views themselves involving a bunch > of joins with yet other tables, 4 or 5 each or so ;-) Just for kicks, attached find the SQL change script ... Using a LEFT JOIN would get unwieldy, fast, I guess. Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B --qlT60X6Y4Yx+rs5J Content-Type: application/sql Content-Disposition: attachment; filename="v23-clin-v_intakes_with_regimens.sql" Content-Transfer-Encoding: quoted-printable -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A-- GNUmed database schema change = script=0A--=0A-- License: GPL v2 or later=0A-- Author: karsten.hilbert@gmx.= net=0A--=0A-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A\set ON_ERROR_STOP 1= =0A--set default_transaction_read_only to off;=0A=0A-- --------------------= ------------------------------------------=0Adrop view if exists clin.v_int= akes_with_pseudo_regimens cascade;=0A=0Acreate view clin.v_intakes_with_pse= udo_regimens as=0Aselect=0A -- context=0A c_i.pk=0A as pk_intake,=0A NULL:= :integer=0A as pk_intake_regimen,=0A c_enc.fk_patient=0A as pk_patient,= =0A -- intake=0A c_i.clin_when=0A as last_checked_when,=0A c_i.narrative= =0A as notes4provider,=0A c_i.soap_cat,=0A c_i.use_type,=0A c_i.fk_substan= ce=0A as pk_substance,=0A c_i.notes4patient,=0A -- regimen=0A NULL::intege= r=0A as pk_dose,=0A NULL::TEXT=0A as schedule,=0A NULL::timestamp with ti= me zone=0A as started,=0A NULL::TEXT=0A as comment_on_start,=0A NULL::tim= estamp with time zone=0A as discontinued,=0A NULL::TEXT=0A as discontinue= _reason,=0A NULL::INTERVAL=0A as planned_duration,=0A -- encounter=0A c_i.= fk_encounter=0A as pk_encounter,=0A c_i.fk_encounter=0A as pk_encounter__= intake,=0A NULL::INTEGER=0A as pk_encounter__regimen,=0A -- substance=0A r= _s.description=0A as substance,=0A r_s.atc=0A as atc_substance,=0A r_s.in= take_instructions,=0A -- dose=0A NULL::DECIMAL=0A as amount,=0A NULL::TEXT= =0A as unit,=0A NULL::TEXT=0A as dose_unit,=0A -- product=0A NULL::TEXT= =0A as drug_product,=0A NULL::TEXT=0A as preparation,=0A NULL::TEXT=0A a= s l10n_preparation,=0A NULL::BOOLEAN=0A as is_fake_product,=0A NULL::TEXT= =0A as external_code,=0A NULL::TEXT=0A as external_code_type,=0A NULL::TE= XT=0A as atc_drug_product,=0A NULL::INTEGER=0A as pk_drug_product,=0A -- = episode=0A c_epi.description=0A as episode,=0A c_epi.pk=0A as pk_episode,= =0A -- issue=0A c_hi.description=0A as health_issue,=0A c_hi.pk=0A as pk_= health_issue,=0A -- LOINCS=0A ARRAY (=0A select row_to_json(loinc_row) fro= m (=0A select=0A r_ll2s.loinc,=0A r_ll2s.comment,=0A extract(epo= ch from r_ll2s.max_age) as max_age_in_secs,=0A r_ll2s.max_age::text as m= ax_age_str=0A from ref.lnk_loinc2substance r_ll2s=0A where r_ll2s.fk_su= bstance =3D r_s.pk=0A ) as loinc_row=0A ) as loincs,=0A=0A c_i.row_version= =0A as row_version__intake,=0A c_i.modified_when=0A as modified_when__int= ake,=0A c_i.modified_by=0A as modified_by__intake,=0A c_i.xmin=0A as xmin= _intake,=0A NULL::INTEGER=0A as row_version__regimen,=0A NULL::timestamp w= ith time zone=0A as modified_when__regimen,=0A NULL::TEXT=0A as modified_= by__regimen,=0A NULL::XID=0A as xmin_regimen=0Afrom=0A clin.intake c_i=0A = join ref.substance r_s on (r_s.pk =3D c_i.fk_substance)=0A join clin.enco= unter c_enc on (c_i.fk_encounter =3D c_enc.pk)=0A join clin.episode c_epi = on (c_i.fk_episode =3D c_epi.pk)=0A left join clin.health_issue c_hi on (= c_epi.fk_health_issue =3D c_hi.pk)=0Awhere=0A c_i.pk not in (select fk_inta= ke from clin.intake_regimen)=0A;=0A=0Acomment on view clin.v_intakes_with_p= seudo_regimens is=0A'Substance intakes without any regimens, with pseudo re= gimen added to each.';=0A=0Agrant select on clin.v_intakes_with_pseudo_regi= mens to group "gm-doctors";=0A=0A-- ---------------------------------------= -----------------------=0Adrop view if exists clin.v_intakes_with_real_regi= mens cascade;=0A=0Acreate view clin.v_intakes_with_real_regimens as=0Aselec= t=0A -- context=0A c_i.pk=0A as pk_intake,=0A c_ir.pk=0A as pk_intake_reg= imen,=0A c_enc.fk_patient=0A as pk_patient,=0A -- intake=0A c_i.clin_when= =0A as last_checked_when,=0A c_i.narrative=0A as notes4provider,=0A c_i.s= oap_cat,=0A c_i.use_type,=0A c_i.fk_substance=0A as pk_substance,=0A c_i.n= otes4patient,=0A -- regimen=0A c_ir.fk_dose=0A as pk_dose,=0A c_ir.narrati= ve=0A as schedule,=0A case=0A when c_ir.comment_on_start =3D '?' then nul= l=0A else coalesce(c_ir.clin_when, c_i.clin_when)=0A end::timestamp with t= ime zone=0A as started,=0A c_ir.comment_on_start,=0A c_ir.discontinued,=0A= c_ir.discontinue_reason,=0A c_ir.planned_duration,=0A -- encounter=0A coal= esce(c_ir.fk_encounter, c_i.fk_encounter)=0A as pk_encounter,=0A c_i.fk_en= counter=0A as pk_encounter__intake,=0A c_ir.fk_encounter=0A as pk_encount= er__regimen,=0A -- substance=0A r_s.description=0A as substance,=0A r_s.at= c=0A as atc_substance,=0A r_s.intake_instructions,=0A -- dose=0A r_d.amoun= t,=0A r_d.unit,=0A r_d.dose_unit,=0A -- product=0A r_dp.description=0A as = drug_product,=0A r_dp.preparation,=0A _(r_dp.preparation)=0A as l10n_prepa= ration,=0A r_dp.is_fake=0A as is_fake_product,=0A r_dp.external_code,=0A r= _dp.external_code_type,=0A r_dp.atc_code=0A as atc_drug_product,=0A r_dp.p= k=0A as pk_drug_product,=0A -- episode=0A coalesce(c_epi__r.description, c= _epi__i.description)=0A as episode,=0A coalesce(c_epi__r.pk, c_epi__i.pk)= =0A as pk_episode,=0A -- issue=0A coalesce(c_hi__r.description, c_hi__i.de= scription)=0A as health_issue,=0A coalesce(c_hi__r.pk, c_hi__i.pk)=0A as = pk_health_issue,=0A -- LOINCS=0A ARRAY (=0A select row_to_json(loinc_row) = =66rom (=0A select=0A r_ll2s.loinc,=0A r_ll2s.comment,=0A extrac= t(epoch from r_ll2s.max_age) as max_age_in_secs,=0A r_ll2s.max_age::text= as max_age_str=0A from ref.lnk_loinc2substance r_ll2s=0A where r_ll2s.= fk_substance =3D r_s.pk=0A ) as loinc_row=0A ) as loincs,=0A=0A c_i.row_ve= rsion=0A as row_version__intake,=0A c_i.modified_when=0A as modified_when= __intake,=0A c_i.modified_by=0A as modified_by__intake,=0A c_i.xmin=0A as= xmin_intake,=0A c_ir.row_version=0A as row_version__regimen,=0A c_ir.modi= fied_when=0A as modified_when__regimen,=0A c_ir.modified_by=0A as modifie= d_by__regimen,=0A c_ir.xmin=0A as xmin_regimen=0Afrom=0A clin.intake c_i= =0A join ref.substance r_s on (r_s.pk =3D c_i.fk_substance)=0A join clin.= encounter c_enc on (c_i.fk_encounter =3D c_enc.pk)=0A join clin.episode c_= epi__i on (c_i.fk_episode =3D c_epi__i.pk)=0A left join clin.health_issue= c_hi__i on (c_epi__i.fk_health_issue =3D c_hi__i.pk)=0A left join clin.in= take_regimen c_ir on (c_ir.fk_intake =3D c_i.pk)=0A left join ref.dose r_= d on (r_d.pk =3D c_ir.fk_dose)=0A left join ref.drug_product r_dp on (r_d= p.pk =3D c_ir.fk_drug_product)=0A join clin.episode c_epi__r on (c_ir.fk_= episode =3D c_epi__r.pk)=0A left join clin.health_issue c_hi__r on (c_ep= i__r.fk_health_issue =3D c_hi__r.pk)=0A;=0A=0Acomment on view clin.v_intake= s_with_real_regimens is=0A'Substance intakes with real regimens, one row pe= r regimen, thus, denormalized intakes.';=0A=0Agrant select on clin.v_intake= s_with_real_regimens to group "gm-doctors";=0A=0A-- -----------------------= ---------------------------------------=0Adrop view if exists clin.v_intake= s_with_regimens cascade;=0A=0Acreate view clin.v_intakes_with_regimens as= =0A SELECT * FROM clin.v_intakes_with_pseudo_regimens=0A=0A UNION ALL=0A= =0A SELECT * FROM clin.v_intakes_with_real_regimens=0A;=0A=0Acomment on vie= w clin.v_intakes_with_regimens is=0A'Substance intake with regimens.=0A.=0A= - one row per regimen, thus, denormalized intakes=0A- one row per intake-wi= thout-any-regimen, with pseudo-regimen=0A';=0A=0Agrant select on clin.v_int= akes_with_regimens to group "gm-doctors";=0A=0A-- -------------------------= -------------------------------------=0Aselect gm.log_script_insertion('v23= -clin-v_intakes_with_regimens.sql', '23.0');=0A --qlT60X6Y4Yx+rs5J--