3

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
2
  • 1
    What is your question? What happens when you run your code? – pLumo Apr 12 '18 at 7:27
  • 1
    It seems like you have some good answers but I am curious as to why you want to do this? As opposed to say, using a GEOMETRYCOLLECTION? – wfgeo Apr 8 '19 at 20:40
5

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}
0

You can insert geometry into your table as follows, if it is convenient for you

CREATE TABLE array_data_demo ( id serial NOT NULL, txt_arr text[] COLLATE pg_catalog."default", bigint_arr bigint[], geom_arr geometry[], CONSTRAINT array_data_demo_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE array_data_demo OWNER TO postgres;

INSERT INTO array_data_demo (id, txt_arr, bigint_arr, geom_arr) VALUES (3, '{txt1,txt2}', '{1,2,5,3}',array[ST_GeomFromText('POINT(23.3 19.234)', 4326)]); SELECT * FROM public.array_data_demo ;

in the example, I removed the 'json_arr' field...

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.