[postgis-users] How best to create and use associative array type in Postgres?

[Regina Obe]

Use JSONB datatype.  

 

CREATE TABLE test(id integer, data jsonb);

TRUNCATE tABLE test;

INSERT INTO test(id, data)

VALUES (1, '{"color": "red", "height_m": 10}');

 

-- PG14 or higher – you can used subscript feature

UPDATE test SET data['color'] = to_jsonb('blue'::text), 

    data['height_m'] = to_jsonb(10), data['width_m'] = to_jsonb(2)

WHERE id = 1;

 

-- PG14 or lower

UPDATE test SET data = jsonb_set(data, ARRAY['color'], to_jsonb('blue'::text), true)

WHERE id = 1;

 

-- PG14 or lower to set multiple

UPDATE test SET data = data || '{"color": "blue", "height_m": 10}'::jsonb;

 

-- To read (all versions)

SELECT data->>'color' AS color, (data->>'height_m')::integer As height_m

FROM test;

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Shaozhong SHI
Sent: Wednesday, January 5, 2022 1:30 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: [postgis-users] How best to create and use associative array type in Postgres?

 

 

In Oracle, one can create and use associative array.  For instance,

TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38);

NODES_WAITING FID_SET;

 

How best to create and use associative array type in Postgres?

 

Or, what is the best/most efficient equivalent in Postgres?

 

Regards,

 

David

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220107/ce509cf5/attachment.html>