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

Question

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.

Add table definitions & some sample data and explain what you are trying to achieve, It seems you your attempt to *"convert into strings so I can split them by the delimiter into multiple columns"* is not at all required if you clearly explain what you want. — Kaushik Nayak, Feb 15 '19 at 03:29
Maybe not (and if it's not then please let me know), but I think it was because the source table has multiple unique records for each location (there are multiple rows for each location where all other columns are distinct values). The materialized view is consolidating multiple rows of each location into a single row per location and transposing the associated columns from multiple rows into a single row. The only way to do that to my knowledge is using arrays. — gulfy, Feb 15 '19 at 08:32
Without sample data and expected results along with table definitions, it's hard to tell anything — Kaushik Nayak, Feb 15 '19 at 08:55

score 0 · Answer 1 · answered Feb 15 '19 at 03:42

0

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,

answered Feb 15 '19 at 03:42

gulfy

47
2
6

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

1 Answers1