Thread: 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?
Regards,
David
On Wed, 5 Jan 2022 at 14:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
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.
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:
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 > <http://justatheory.com>) > > <https://justatheory.com/2010/08/postgres-key-value-pairs/ > <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