[GIS] Inserting Geometry [] using PostGIS

postgispostgresql-9.6

I am trying to figure out how to insert Geometry Array in Geometry [] column in PostGIS.

CREATE TABLE public.array_data_demo
(
id bigint PRIMARY KEY,
txt_arr text[] COLLATE pg_catalog."default",
bigint_arr bigint[],
jsonb_arr jsonb[],
geom_arr geometry[]

)

--------------------------------
INSERT INTO public.array_data_demo
(id, txt_arr, bigint_arr, jsonb_arr, geom_arr)
VALUES 
(3, '{txt1,txt2}', '{1,2,5,3}',  (array['{"sender":"pablo","body":"they are 
on to us"}'::json, '{"sender":"arthur"}'::json]), 
{ST_GeomFromText('POINT(23.3 19.234)',4326)}
);

SELECT * FROM  public.array_data_demo ;

Gives the following error:

ERROR:  syntax error at or near "{"
LINE 6:     {ST_GeomFromText('POINT(23.3 19.234)',4326)}
            ^
SQL state: 42601
Character: 243

Best Answer

The issue is that creating an array item using {} requires the content to be within quotes, though we need to evaluate an expression in order to create the element.

It is therefore possible to use the ARRAY[] constructor, as it doesn't require quoting the content. The array doc is helpful.

select ARRAY[st_geomFromText('POINT(1 1)')];
-->  
{0101000000000000000000F03F000000000000F03F}
Related Question