Associative arrays in PostgreSQL

Directly there is no such entity as an associative array in Postgresql. JSON or the older contrib hstore module can be used to store and process this structure. In essence, they are the same thing, the authors of both types are the same people and a significant part of the code is common to these types.

JSON is just JSON . Added in version 9.2, since then actively developed and overgrown with functionality. It is better to use the JSONB data JSONB (added in 9.4) instead of the older JSON because of the possibility to index JSONB .

 create table kvtest (id serial primary key, arr JSONB); insert into kvtest (arr) values('{"key1": 1}') returning id; update kvtest set arr = arr || '{"key2": 1}'::jsonb where id=1; 

hstore is a much older type. By default, the extension is disabled, you need to load the command once for the database:

 create extension hstore; 

But the use seems to be true, only the syntax of the values ​​is different:

 create table kvtest2 (id serial primary key, arr hstore); insert into kvtest2 (arr) values('key1 => 1') returning id; update kvtest2 set arr = arr || 'key2=>2'::hstore where id=1; 

For details on the available functions for processing and manipulations, see the manual for your version of postgresql. In recent versions of postgresql, it makes sense to choose JSONB , with json it is usually more convenient to work on the application than through hstore.

If in relational databases there were associative arrays as a data type, then there would be no point in inventing non-relational databases :) Practically every subd has crutches with which you can implement something remotely resembling an associative array. In Oracle, this is, for example, Nested Tables.

In pure PostgreSQL it is possible to use only temporary tables.

 CREATE OR REPLACE FUNCTION my_func(id integer) RETURNS SETOF record AS $BODY$declare begin CREATE TEMP TABLE vr_table (id integer, value varchar); .... /*делаете что-то */ DROP TABLE vr_table; return; end;$BODY$ 

CREATE TEMP TABLE - TEMP indicates that the table is temporary.

Also, you can store JSON in a cell and access it by index, like this:

 SELECT * FROM json_test WHERE data ->> 'a' > '1'; 

If you can install plugins, you can try hstore : https://www.postgresql.org/docs/9.3/static/hstore.html

It implements the full Key-Value type.