Append between two html tags in postgres table

Question

Postgres table with name postgrestable:

ID    NAME    HTML
1     FRONT   <map> 
              <entry> test </entry>
              </map>

How can i create a update query that appends a new <entry></entry> between the <map> tags. The table should be updated to this form:

ID    NAME    HTML
1     FRONT   <map> 
              <entry> test </entry>
              <entry> secondentry </entry>
              </map>

I have tried with REGEXP_REPLACE between tags, but that deletes all entries between <map> tags and inserts the entry you updated. Any other idea?

Your map tag is not closed at the end. Is this right?

S-Man
– S-Man

2018-10-31 14:47:01 +00:00
Commented Oct 31, 2018 at 14:47 — S-Man
– S-Man, Commented Oct 31, 2018 at 14:47
Yes it was a typo, is edited now.

Mr ASD
– Mr ASD

2018-10-31 14:49:13 +00:00
Commented Oct 31, 2018 at 14:49 — Mr ASD
– Mr ASD, Commented Oct 31, 2018 at 14:49

S-Man · Accepted Answer · 2018-10-31 15:18:36Z

1

demo: db<>fiddle

SELECT 
     regexp_replace(xml, match, match || '<entry>secondentry</entry>')
FROM (
    SELECT
        xml,
        (regexp_matches(xml,'^<map>(.*)</map>$'))[1] as match
    FROM (
        SELECT '<map><entry>test</entry></map>' as xml
    ) s
) s

Using your way with regexp_replace:

regexp_matches gives the whole content of <map>
regexp_replace replaces the whole content of <map> with its content AND the new one.

Edit: The whole UPDATE would look like:

UPDATE postgrestable
SET html =
(
    SELECT 
        regexp_replace(xml, match, match || '<entry>secondentry</entry>')
    FROM (
        SELECT
            xml,
            (regexp_matches(xml,'^<map>(.*)</map>$'))[1] as match
        FROM (
            SELECT '<map><entry>test</entry></map>' as xml
        ) s
    ) s
);

edited Oct 31, 2018 at 15:18

answered Oct 31, 2018 at 15:05

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr ASD Over a year ago

Thank you very much for the effort. It is not meant to be done only by regexp_replace. As i am not experienced with postgres, how can the update query look like with this statement, as i could not make it use.

S-Man Over a year ago

Updated fiddle and added the UPDATE code. Is this what you are looking for?

Mr ASD Over a year ago

I was trying to make it use but unfortunately could not, receive a error : failed to find conversion function from unknown to text. I tried a way now, as following : update postgrestable set html = regexp_replace(html,'</map>', '<entry>secondentry</entry>\1') where id=1; and worked!!! Thank you for the ffort once more!

Collectives™ on Stack Overflow

Append between two html tags in postgres table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related