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
Shaozhong SHI <shishaozhong@gmail.com> writes:
How best to create and use associative array type in Postgres?
I think the closest thing you'd find to that is jsonb, or
contrib/hstore if you'd like something with a bit less complexity.
The notation is unlikely to look much like Oracle, but they
both have the ability to store sets of key/value pairs.
https://www.postgresql.org/docs/current/datatype-json.html
https://www.postgresql.org/docs/current/hstore.html
regards, tom lane
What do you think this attempt by using create type and create a function?
Managing Key/Value Pairs in PostgreSQL (justatheory.com)
<https://justatheory.com/2010/08/postgres-key-value-pairs/>
Regards,
David
On Wed, 5 Jan 2022 at 14:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Shaozhong SHI <shishaozhong@gmail.com> writes:
How best to create and use associative array type in Postgres?
I think the closest thing you'd find to that is jsonb, or
contrib/hstore if you'd like something with a bit less complexity.
The notation is unlikely to look much like Oracle, but they
both have the ability to store sets of key/value pairs.https://www.postgresql.org/docs/current/datatype-json.html
https://www.postgresql.org/docs/current/hstore.htmlregards, tom lane
Shaozhong SHI <shishaozhong@gmail.com> writes:
What do you think this attempt by using create type and create a function?
Managing Key/Value Pairs in PostgreSQL (justatheory.com)
<https://justatheory.com/2010/08/postgres-key-value-pairs/>
The date alone should suggest to you that there might be
better ways to do it by now.
regards, tom lane
Have a look at this one.
GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL
<https://github.com/theory/kv-pair>
There is no documentation on how to use it.
Regards,
David
On Wed, 5 Jan 2022 at 16:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Shaozhong SHI <shishaozhong@gmail.com> writes:
What do you think this attempt by using create type and create a
function?
Managing Key/Value Pairs in PostgreSQL (justatheory.com)
<https://justatheory.com/2010/08/postgres-key-value-pairs/>The date alone should suggest to you that there might be
better ways to do it by now.regards, tom lane
On 1/5/22 12:26, Shaozhong SHI wrote:
Have a look at this one.
GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL
<https://github.com/theory/kv-pair>There is no documentation on how to use it.
Why use it when you have:
https://www.postgresql.org/docs/current/datatype-json.html
Or if you want something simpler:
https://www.postgresql.org/docs/current/hstore.html
Regards,
David
On Wed, 5 Jan 2022 at 16:24, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:Shaozhong SHI <shishaozhong@gmail.com
<mailto:shishaozhong@gmail.com>> writes:What do you think this attempt by using create type and create a
function?
Managing Key/Value Pairs in PostgreSQL (justatheory.com
<https://justatheory.com/2010/08/postgres-key-value-pairs/>>
The date alone should suggest to you that there might be
better ways to do it by now.regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com