Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tbl (
   source  text
 , target1 text  -- separate target columns ...
 , target2 text  -- ... only to demonstrate 3 variants
 , target3 text
);

INSERT INTO tbl(source) VALUES
   ('<u><font color="#0000FF"><a href="http://virginialidar.com/index-3.html#.VgLbFPm6e73" target="_blank">VA Lidar</a></font></u> OR <u><font color="#0000FF"><a href="https://inport.nmfs.noaa.gov/inport/item/50122" target="_blank">InPort Metadata</a></font></u>')
 , ('<u><font color="#0000FF"><a href="http://single_link.com/index-3.html#.VgLbFPm6e73" target="_blank">VA Lidar</a></font></u>')
 , ('foo')  -- no match
 , ('')     -- empty string
 , (NULL);  -- NULL

CREATE TABLE



INSERT 0 5


-- variant 1 (use all array elements)
UPDATE tbl t
SET    target1 = (
   SELECT array_to_string(ARRAY(SELECT regexp_matches(t.source, 'href="([^"]+)', 'g')), ',')
   );

UPDATE 5


-- variant 2 (use only 1st array element)
UPDATE tbl t
SET    target2 = (
   SELECT string_agg(arr[1], ',')
   FROM   regexp_matches(t.source, 'href="([^"]+)', 'g') arr
   );

UPDATE 5


-- variant 3 (use all array elements)
UPDATE tbl t
SET    target3 = (
   SELECT string_agg(elem, ',')
   FROM   regexp_matches(t.source, 'href="([^"]+)', 'g') arr
        , unnest(arr) elem
   );

UPDATE 5


SELECT target1, target2, target3
FROM   tbl;


target1
target2
target3


http://virginialidar.com/index-3.html#.VgLbFPm6e73,https://inport.nmfs.noaa.gov/inport/item/50122
http://virginialidar.com/index-3.html#.VgLbFPm6e73,https://inport.nmfs.noaa.gov/inport/item/50122
http://virginialidar.com/index-3.html#.VgLbFPm6e73,https://inport.nmfs.noaa.gov/inport/item/50122

http://single_link.com/index-3.html#.VgLbFPm6e73
http://single_link.com/index-3.html#.VgLbFPm6e73
http://single_link.com/index-3.html#.VgLbFPm6e73


null
null


null
null


null
null



SELECT 5

target1	target2	target3
http://virginialidar.com/index-3.html#.VgLbFPm6e73,https://inport.nmfs.noaa.gov/inport/item/50122	http://virginialidar.com/index-3.html#.VgLbFPm6e73,https://inport.nmfs.noaa.gov/inport/item/50122	http://virginialidar.com/index-3.html#.VgLbFPm6e73,https://inport.nmfs.noaa.gov/inport/item/50122
http://single_link.com/index-3.html#.VgLbFPm6e73	http://single_link.com/index-3.html#.VgLbFPm6e73	http://single_link.com/index-3.html#.VgLbFPm6e73
	null	null
	null	null
	null	null