Create and access a dictionary type

On 1/5/22 14:17, Shaozhong SHI wrote:

Any examples in Postgres to create a dictionary type to store and access
key value pairs?

I'd say JSONB can be used as an associative array, and e.g. in Python
can map to dict data type.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

I just checked operators. I could not find any operator to set a new value
given a key.

Regards,

David

On Wed, 5 Jan 2022 at 13:22, Tomas Vondra <tomas.vondra@enterprisedb.com>
wrote:

On 1/5/22 7:22 AM, Tomas Vondra wrote:

On 1/5/22 14:17, Shaozhong SHI wrote:

Any examples in Postgres to create a dictionary type to store and access
key value pairs?

I'd say JSONB can be used as an associative array, and e.g. in Python can
map to dict data type.

The question confuses me.  Why isn't "create a table with Primary Key" the
answer to "Any examples in Postgres to create a dictionary type to store and
access key value pairs?"

--
Angular momentum makes the world go 'round.

On 1/5/22 17:24, Ron wrote:

On 1/5/22 7:22 AM, Tomas Vondra wrote:

On 1/5/22 14:17, Shaozhong SHI wrote:

Any examples in Postgres to create a dictionary type to store and
access key value pairs?

I'd say JSONB can be used as an associative array, and e.g. in Python
can map to dict data type.

The question confuses me.  Why isn't "create a table with Primary Key"
the answer to "Any examples in Postgres to create a dictionary type to
store and access key value pairs?"

Well, yes - you can always decompose the dict and store it in an EAV,
but sometimes it's more convenient to just use "nested" dictionary at
the row level. Which is what a JSONB column does.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 1/5/22 14:36, Shaozhong SHI wrote:

I just checked operators.  I could not find any operator to set a new
value given a key.

I'd say jsonb_set is what you're looking for.

Postgres does not have a thing that'd match the Oracle feature exactly,
you'll have to adjust your application to use what's available.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 1/5/22 10:35 AM, Tomas Vondra wrote:

On 1/5/22 17:24, Ron wrote:

On 1/5/22 7:22 AM, Tomas Vondra wrote:

On 1/5/22 14:17, Shaozhong SHI wrote:

Any examples in Postgres to create a dictionary type to store and
access key value pairs?

I'd say JSONB can be used as an associative array, and e.g. in Python
can map to dict data type.

The question confuses me.  Why isn't "create a table with Primary Key"
the answer to "Any examples in Postgres to create a dictionary type to
store and access key value pairs?"

Well, yes - you can always decompose the dict and store it in an EAV, but
sometimes it's more convenient to just use "nested" dictionary at the row
level. Which is what a JSONB column does.

Edgar Codd is rolling in his grave.

--
Angular momentum makes the world go 'round.

Well, yes - you can always decompose the dict and store it in an EAV, but sometimes it's more convenient to just use "nested" dictionary at the row level. Which is what a JSONB column does.

Edgar Codd is rolling in his grave.

Contributing mightily to the global angular momentum, for sure.