CODEWITHSUNDEEP
postgresqltimestamp-with-timezonearray-agg
gulfy
gulfy

Reputation: 47

"Function does not exist" errors when trying to split column containing array of timestampz into delimited text string in Postgres

I have a table with columns that contain arrays that I want converted into strings so I can split them by the delimiter into multiple columns.

I'm having trouble doing this with arrays of dates with timezones.

create materialized view matview1 as select
    (location) as location,
    (nullif(split_part(string_agg(distinct name,'; '),'; ',1),'')) as name1,
    (nullif(split_part(string_agg(distinct name,'; '),'; ',2),'')) as name2,
    (nullif(split_part(string_agg(distinct name,'; '),'; ',3),'')) as name3,
    (array_agg(distinct(event_date_with_timestamp))) as event_dates
    from table2 b
    group by location;

In the code above I'm creating a materialized view of table to consolidate all table entries related to certain locations into single rows.

How can I create additional columns for each event_date entry like I did with the names (e.g Name1, Name2 and Name3 from the 'name' array)?

I tried changing the array to a string format with:

(nullif(split_part(array_to_string(array_agg(distinct(event_date_with_timestamp))),'; ',1),'')) as event_date1

But this throws the error:

"function array_to_string(timestamp with time zone[]) does not exist"

And casting to different datatypes always produces errors saying I can't cast from type timestampz into anything else.

Upvotes: 0

Views: 902

Answers (1)

gulfy
gulfy

Reputation: 47

I found a way to accomplish this by casting from timestampz to text and then back again like this:

(nullif(split_part(string_agg(distinct event_date::text,'; '),'; ',1),'')::date) as date1,

Upvotes: 0

Related Questions