1

Using PostgreSQL, I can easily do a select statement to return a column that is an array of the datatype point. i.e. colA is the datatype point[]

select colA from tbl

Returns a column that contains an array of points:

+-----------------------------+
|            colA             |
+-----------------------------+
| (180.0,90.0),(-180.0,-90.0) |
| (64.0,38.0),(-26.0,-40.0)   |
| etc                         |
+-----------------------------+

I'm certain the column is of type point[], because select colA[0] returns as expected.

I'm trying to do this query from another server using DBLINK(). This is the query:

select
colA
from dblink(
  'my_conn',
  'select
   colA
   from public.tbl'
) as s (
  colA point[]
)

This query fails with this error message:

SQL Error [22P02]: ERROR: malformed array literal: "(180,90),(-180,-90)" Detail: Array value must start with "{" or dimension information.

I tested that I am able to select the datatype point using DBLINK. I.e. this query succeeds:

select
colA
from dblink(
  'my_conn',
  'select
   colA[0]
   from public.tbl'
) as s (
  colA point
)

So, my question is How do you select an array via the DBLINK function?.

  • The table you pasted looks like the output from psql. But, that is not what the output of an array of points would look like in psql. "because select colA[0] returns as expected": this doesn't communicate much, because we don't know what you expected it to return. – jjanes Jul 24 at 15:44
  • If you are using psql, then use \d tbl to see what the actual data type of the column is. If the type actually is point[], then the dblink queries you give work for me as written (except that subscript 0 is generally unused, so I changed it to a 1) – jjanes Jul 24 at 15:50
  • The table output is from an ascii table generator that I like to use. By expected, I mean that specifying an index I got the first of the two values back. – Zach Smith Jul 24 at 16:03
1

The solution I found was via casting:

  1. Specify the column in DBLINK SQL as TEXT
  2. Cast to text in the DBLINK SQL statement
  3. Cast from TEXT to POINT[] outside of DBLINK

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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