Function array_trim

Removes empty strings and null entries from a given array. In addition the function can remove duplicate entries. The function supports strings, numbers, dates, and timestamps with or without time zone.

Examples

-- Untrimmed timestamp array with time zone with duplicates
SELECT array_trim(ARRAY['2018-11-11 11:00:00 MEZ',NULL,'2018-11-11 11:00:00 MEZ']::TIMESTAMP WITH TIME ZONE[]) AS trimmed_array;

Result:

untrimmed_array
{‘2018-11-11 11:00:00.000’,,’2018-11-11 11:00:00.000’}

-- Timestamp array with time zone with duplicates
SELECT ARRAY['2018-11-11 11:00:00 MEZ',NULL,'2018-11-11 11:00:00 MEZ']::TIMESTAMP WITH TIME ZONE[] AS untrimmed_array;

Result:

trimmed_array
{‘2018-11-11 11:00:00.000’,’2018-11-11 11:00:00.000’}

-- Timestamp array with time zone without duplicates
SELECT array_trim(ARRAY['2018-11-11 11:00:00 MEZ',NULL,'2018-11-11 11:00:00 MEZ']::TIMESTAMP WITH TIME ZONE[], TRUE) AS trimmed_array_distinct;

Result:

trimmed_array_distinct
{‘2018-11-11 11:00:00.000’}