0

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[]

1 Answer 1

2

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;
3
  • 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?
    – Zach Smith
    Jul 24, 2019 at 14:56
  • array_to_string(a, ";"), I'm afraid I have no idea about the dblink Jul 24, 2019 at 15:06
  • 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...
    – Zach Smith
    Jul 25, 2019 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.