SELECT utc_offset, is_dst,

ltrim(trim(string_agg(distinct (CASE WHEN abbrev NOT LIKE '+%' AND abbrev NOT LIKE '-%' AND abbrev != name THEN abbrev ELSE '' END), ' '))

|| ' ' || string_agg(name, ', ' ORDER BY name)

)

FROM (

SELECT utc_offset, is_dst, name, abbrev

FROM pg_timezone_names

WHERE name NOT LIKE 'posix/%'

AND name NOT LIKE 'Etc/%'

AND (lower(abbrev) <> abbrev)

AND name NOT IN ('HST', 'Factory', 'GMT', 'GMT+0', 'GMT-0', 'GMT0', 'localtime', 'UCT', 'Universal', 'UTC', 'PST8PDT', 'ROK', 'W-SU', 'MST', 'CST6CDT')

EXCEPT

SELECT n.utc_offset, n.is_dst, n.name, n.abbrev

FROM pg_timezone_names n

JOIN pg_timezone_abbrevs a ON a.abbrev = n.name

WHERE n.utc_offset <> a.utc_offset

) sub

GROUP BY utc_offset, is_dst

ORDER BY utc_offset, is_dst

;

SELECT utc_offset, is_dst

, concat_ws(' ', string_agg(DISTINCT abbrev, ' ' ORDER BY abbrev) FILTER (WHERE NOT abbrev ^@ ANY ('{+,-}') AND abbrev <> name)

, string_agg(name, ', ' ORDER BY name)) AS abbrevs_and_names

FROM pg_timezone_names n

WHERE lower(abbrev) <> abbrev

AND NOT name ^@ ANY ('{posix/, Etc/}')

AND name <> ALL ('{HST, Factory, GMT, GMT+0, GMT-0, GMT0, localtime, UCT, Universal, UTC, PST8PDT, ROK, W-SU, MST, CST6CDT}')

AND NOT EXISTS ( -- !!!

SELECT FROM pg_timezone_abbrevs a

WHERE a.abbrev = n.name

AND a.utc_offset <> n.utc_offset

)

GROUP BY utc_offset, is_dst

ORDER BY utc_offset, is_dst

;