Postgresql – How to select “array”-type columns via the DBLINK function

dblinkpostgresql

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?.

Best Answer

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

Related Question