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

[Shaozhong SHI]

Hi, Regina,

That is interesting!

How to add new entries to the dictionary.  E.g., weight: 40?

Can the dictionary to serve as a collection of paired key, value set, so
that we can accumulate data to be processed?

Then, we can deal with the first, then 2nd and so on in turn?

Or, we can do things like pip and pop?  Namely, when we have dealt with the
first key, value pair, it will be out the dictionary, so that we can be
sure that we are dealing with each key, value pair in turn?

Alternatively, can we fetch each key, value pair by its index or position?

Regards,

David

On Fri, 7 Jan 2022 at 21:19, Regina Obe <lr at pcorp.us> wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220108/e92d3c52/attachment.html>