Postgres 11

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 (city text, direction text);
INSERT INTO tbl VALUES
      (NULL, 'dir number, extra data, CITY 1,  AL 111111'::text)
    , (NULL, 'dir number, weird CITY,  AL 111111')
    , (NULL, 'number, dir, number, some CITY here, dir number, CITY,  AL 111111');

CREATE TABLE

INSERT 0 3

SELECT substring(direction FROM ', *([^,]+),[^,]+$') AS city, direction
FROM   tbl;

city
direction

CITY 1
dir number, extra data, CITY 1,  AL 111111

weird CITY
dir number, weird CITY,  AL 111111

CITY
number, dir, number, some CITY here, dir number, CITY,  AL 111111

SELECT 3

UPDATE tbl
SET    city = substring(direction FROM ',([^,]+),[^,]+$')
WHERE  direction ~ ', *\D\D \d{5}'
RETURNING *;

city
direction

 CITY 1
dir number, extra data, CITY 1,  AL 111111

 weird CITY
dir number, weird CITY,  AL 111111

 CITY
number, dir, number, some CITY here, dir number, CITY,  AL 111111

UPDATE 3

city	direction
CITY 1	dir number, extra data, CITY 1, AL 111111
weird CITY	dir number, weird CITY, AL 111111
CITY	number, dir, number, some CITY here, dir number, CITY, AL 111111