PostgreSQL: What is the best way to cast from from text to point[]?

Question

I'm looking for a way to cast from a text value:

'(180,90),(-180,-90)'

into a point[] value:

{"(180,90)","(-180,-90)"}

What is the best way to do this?

One possibility that I can think of is to manipulate the string to be of this form:

'{"(180, 90)", "(-180,-90)"}'

Which I can then cast to point[] like so:

select '{"(180, 90)", "(-180,-90)"}'::point[];

Surely there's a better way? The solution I currently have is this:

select concat('{', replace(replace(<text>, '(', '"('), ')', ')"'), '}')::point[]

Answer 1

Not any better than what you already got, but:

select string_to_array(replace('(180,90),(-180,-90)','),(',');('), ';')::point[];

If the separator token in the array would have been something else (say ";"), it would have been sufficient with:

select string_to_array('(180,90);(-180,-90)', ';')::point[];

EDIT:

If I get it right you have a foreign table like:

create table mytbl (p point[]);
insert into mytbl (p) 
values ( '{"(180, 90)", "(-180,-90)"}'::point[] );

which you access through a dblink. Would the following work ( I cant use DBLink at the moment )?

select string_to_array(array_to_string(p,';'),';') from mytbl;

• Zach Smith: I'm actually casting from point[] to a string. Is there a way to specify the join character? Then I could use this, which I think is cleaner. The reason I'm doing this is because I can't select an array over dblink dba.stackexchange.com/questions/243659/…. Do you know why?
• Lennart - Slava Ukraini: array_to_string(a, ";"), I'm afraid I have no idea about the dblink
• Zach Smith: I think it should work... This does: select string_to_array(array_to_string('{"(180, 90)", "(-180,-90)"}'::point[], ';'), ';') result. However, trying that function in the DBLINK query results in an error: [42883] ERROR: function array_to_string(box, unknown) does not exist. I don't know what this box is...