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:[email protected]] On Behalf Of
Shaozhong SHI
Sent: Wednesday, January 5, 2022 1:30 PM
To: PostGIS Users Discussion <[email protected]>
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
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
