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 1tNqu9-002Znl-Fw for pgsql-general@arkaria.postgresql.org; Wed, 18 Dec 2024 10:03:45 +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 1tNqu8-00D2zT-Qo for pgsql-general@arkaria.postgresql.org; Wed, 18 Dec 2024 10:03:44 +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 1tNDct-008SDK-32 for pgsql-general@lists.postgresql.org; Mon, 16 Dec 2024 16:07:20 +0000 Received: from mail-vs1-xe35.google.com ([2607:f8b0:4864:20::e35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tNDcr-003944-LU for pgsql-general@postgresql.org; Mon, 16 Dec 2024 16:07:19 +0000 Received: by mail-vs1-xe35.google.com with SMTP id ada2fe7eead31-4afd68271b6so1127511137.0 for ; Mon, 16 Dec 2024 08:07:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734365237; x=1734970037; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=ovcxMuwbGi9Ec+dAOaPLJbaut8LC2MWl79LMLSdC7aY=; b=FPilhTMMEKQj5Bg+yJLAGSJA/crZa7Ackl5xL25biuhT+TJZqvCv2KFDCptLffalWm SyptvPOjpDRAjLSdEnH+HbZZ3Wp22WTO+8orz+hc0vQfBbSesbSrpWbJnd5CQhEzOCMy fSlrWaouluC2ekxdryGjMMkqAI0hXwBwHnPtNdT534w8JcTwYC2NlK7RgE78O++6Lg8Z wzFDjIr5lc6iIsCp5sHEFQNdnIvjPAStreSXBGucgT2vNLyX+VfkFyizuCsoa9S+UfLQ S7+z/wgRPLmmoGp/G5H3+gbb79uqrM53HLDAQWHAp28x/yu28YCjihBhuxHVKwcSx2JX ZRTg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734365237; x=1734970037; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=ovcxMuwbGi9Ec+dAOaPLJbaut8LC2MWl79LMLSdC7aY=; b=daE8vbFeUmqEda6OyQR3mYalieuUc2lNnJ3/7T1ZsrS5K/26qIRU9q/eSnzBbyrm+J dguYdcwG+Azm5/SVjygQPe4Qm/Kg/3B2az8pkJlMoAMASU4WlSOdUBe0IcN2naQ/7GI5 K9O8P3t/ABmzbLES77Ps+wQmL5RLKpuupF1dy546BOEjweIWc5AhG74o9zHP4HcedoRo ZsURO1pNnYgahNrjU3ieeqj9awwyAD5Kc38tGt62UNhwEfeXN8WGhY9JqEVryeN2HnhY yTCvac5ppJyO8usWgZgLLoKhS4mu8/nQ2Hzx++1mDejSrlmHRFb5jQwYjMyU/1baowzy 0Cew== X-Gm-Message-State: AOJu0YySglLv11GT8OpndVzjSw4c1fNOhvkN/Nt7eUMaCFSOD0OYfPhA 3PeEeuGeUfDKSRR/nR0dc5XEt3aZETYujEqFZDNqsnK9to0tipIAqdvQrHHSBzBjJ/rNHhtttSY 5ia+3N8kNsQCtynCAwbGJrk4Xj1C1UbT3 X-Gm-Gg: ASbGnct0S58dfumQAoAOmEwexpHr8MzTg/KAB2S7AVDTHM2zMXdGsvg/qGia+7ZkTo1 26WPMSV37fDbktfoGGkzjY5OriFI4Sj3oX4frc+s7 X-Google-Smtp-Source: AGHT+IElr7/Vyowljb/X7vR21AL4O7h2A6l5Ub6RHuB3jPs/Rwe9ChrJwQFqd22GCU0usDNctI5yl+i9c9YZFRKRrGA= X-Received: by 2002:a05:6122:1806:b0:518:859e:87ab with SMTP id 71dfb90a1353d-518ca468491mr11508039e0c.9.1734365236381; Mon, 16 Dec 2024 08:07:16 -0800 (PST) MIME-Version: 1.0 From: celati Laurent Date: Mon, 16 Dec 2024 17:07:05 +0100 Message-ID: Subject: How to do an update with XML column ? To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000d351a20629655fa8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d351a20629655fa8 Content-Type: text/plain; charset="UTF-8" Dear all, I'm taking the liberty to ask an question. Do you know if there are some ways in order to update and/or do group by with an XML column ? For instance, select id, unnest(xpath( '//cit:CI_Organisation/cit:name/gco:CharacterString/text()', CAST(data AS XML), ARRAY[ ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'], ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'], ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'], ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'], ])) as orga_name, changedate, createdate, displayorder, doctype, extra, popularity, rating, root, schemaid, title, istemplate, isharvested, harvesturi, harvestuuid, groupowner, metadata.owner, metadata.source, uuid from public.metadata group by orga_name I obtained : ERROR: could not identify an equality operator for type xml LINE 39: group by orga_name ^ SQL state: 42883 Character: 2556 Thanks so much --000000000000d351a20629655fa8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear all,
I'm taking the liberty to ask an questio= n. Do you know if there are some ways in order to update and/or do group by= with an XML column ?=C2=A0
For instance,=C2=A0

select id, unnest(xpath(
        '//cit:CI_Organisation/cit:name/gco:CharacterString/text()'=
,
       CAST(data AS XML),
        ARRAY[
            ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'=
;],
            ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'=
;],
            ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'=
],
            ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'=
],
        ])) as orga_name, changedate, createdate, displayorder, doctype, ex=
tra, popularity, rating, root, schemaid, title, istemplate, isharvested, ha=
rvesturi,
       harvestuuid, groupowner, metadata.owner, metadata.source, uuid
from public.metadata
group by orga_name

I obtained : 
ERROR:  could not identify an equality operator for type xml
LINE 39: group by orga_name
                  ^=20

SQL state: 42883
Character: 2556



Thanks so much
--000000000000d351a20629655fa8--