PostgreSQL is the best way to return an array of key-value pairs

Question

I am trying to select multiple fields, one of which should be an array with each element of the array containing two values. Each element of the array must contain a name (a changing character) and an identifier (numeric). I know how to return an array of single values ​​(using the ARRAY keyword), but I'm not sure how to return an array of an object that itself contains two values.

The request is similar to

 SELECT t.field1, t.field2, ARRAY(--with each element containing two values ie {'TheName', 1 }) FROM MyTable t 

I read that one way to do this is to select values ​​into a type and then create an array of that type. The problem is that the rest of the function already returns a type (which means that I would have nested types - is this normal? If so, how would you read this data back in the application code, i.e. with the .Net data provider like this how is NPGSQL?)

Any help is greatly appreciated.

Answer 1

I suspect that, having no more knowledge about your application, I will not be able to bring you to the desired result. But we can get pretty far. For starters, there is a ROW function:

 # SELECT 'foo', ROW(3, 'Bob'); ?column? | row ----------+--------- foo | (3,Bob) (1 row) 

So you can associate an entire row with a cell. You can also make things more explicit by creating a type for this:

 # CREATE TYPE person(id INTEGER, name VARCHAR); CREATE TYPE # SELECT now(), row(3, 'Bob')::person; now | row -------------------------------+--------- 2012-02-03 10:46:13.279512-07 | (3,Bob) (1 row) 

By the way, whenever you create a table, PostgreSQL makes a type with the same name, so if you already have such a table, you also have a type. For example:

 # DROP TYPE person; DROP TYPE # CREATE TABLE people (id SERIAL, name VARCHAR); NOTICE: CREATE TABLE will create implicit sequence "people_id_seq" for serial column "people.id" CREATE TABLE # SELECT 'foo', row(3, 'Bob')::people; ?column? | row ----------+--------- foo | (3,Bob) (1 row) 

In the third query, I used people as a type.

Now this is unlikely to be as useful as you think for two reasons:

• I cannot find convenient syntax for pulling data from a nested string.

  I might be missing something, but I just don’t see many people use this syntax. The only example I see in the documentation is a function that takes the value of a string as an argument and does something with it. I do not see an example of pulling a row from a cell and polling its parts. It looks like you can pack the data this way, but after that it's hard to deconstruct. You will have to do a lot of stored procedures.

• Your PostgreSQL language driver may not process data with a string nested in a string.

  I cannot speak for NPGSQL, but since it is a very PostgreSQL-specific function, you will not find it in libraries that support other databases. For example, Hibernate will not be able to process a selection of an object stored as a cell value in a row. I'm not even sure that JDBC can usefully provide Hibernate with information, so the problem can be quite profound.

So, what you are doing here is possible if you can live without a lot of subtleties. I would recommend not to pursue him, because it will be a difficult battle, if I am not mistaken.

Answer 2

ARRAY can only contain items of the same type.

Your example displays the value of text and integer (without quotes around 1 ). It is generally not possible to mix types in an array. To get these values ​​into an array, you must create a composite type and then form an array of this composite type, as you already mentioned.

Alternatively, you can use json data types in Postgres 9.2+, jsonb in Postgres 9.4+, or hstore for key-value pairs.

---

Of course, you can cast integer to text and work with a two-dimensional text array. Consider the two syntax options for entering an array in the demo below and refer to the guide for entering an array .

There is a limitation to overcome. If you try to aggregate ARRAY (to build from a key and a value) into a two-dimensional array, there is an error in assembling the array_agg() aggregate or the ARRAY constructor:

 ERROR: could not find array type for data type text[] 

There are ways around this.

Combining key-value pairs in a two-dimensional array

PostgreSQL 9.1 with standard_conforming_strings= on :

 CREATE TEMP TABLE tbl( id int ,txt text ,txtarr text[] ); 

The txtarr column is for demonstration of syntax options in the INSERT command only. The third row contains metacharacters:

 INSERT INTO tbl VALUES (1, 'foo', '{{1,foo1},{2,bar1},{3,baz1}}') ,(2, 'bar', ARRAY[['1','foo2'],['2','bar2'],['3','baz2']]) ,(3, '}b",a{r''', '{{1,foo3},{2,bar3},{3,baz3}}'); -- txt has meta-characters SELECT * FROM tbl; 

A simple case: aggregate two integers (I use them twice) into a two-dimensional int array:

Update: Better with custom aggregation function

Using the polymorphic type anyarray it works for all base types:

 CREATE AGGREGATE array_agg_mult (anyarray) ( SFUNC = array_cat ,STYPE = anyarray ,INITCOND = '{}' ); 

Call:

 SELECT array_agg_mult(ARRAY[ARRAY[id,id]]) AS x -- for int ,array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS y -- or text FROM tbl; 

Note the optional ARRAY[] layer to make it multidimensional.

Update for Postgres 9.5 +

Postgres now sends the input option of the receiving array to array_agg() , and you can replace my custom function from above:

Leadership:

array_agg(expression)
...
input arrays combined into an array of one higher size (all inputs must have the same dimension and cannot be empty or NULL)