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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply