Supported Versions: Current (11) / 10 / 9.6 / 9.5 / 9.4
Development Versions: 12 / devel
Unsupported versions: 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3

# F.17. intagg

The intagg module provides an integer aggregator and an enumerator. intagg is now obsolete, because there are built-in functions that provide a superset of its capabilities. However, the module is still provided as a compatibility wrapper around the built-in functions.

## F.17.1. Functions

The aggregator is an aggregate function `int_array_aggregate(integer)` that produces an integer array containing exactly the integers it is fed. This is a wrapper around `array_agg`, which does the same thing for any array type.

The enumerator is a function `int_array_enum(integer[])` that returns setof integer. It is essentially the reverse operation of the aggregator: given an array of integers, expand it into a set of rows. This is a wrapper around `unnest`, which does the same thing for any array type.

## F.17.2. Sample Uses

Many database systems have the notion of a one to many table. Such a table usually sits between two indexed tables, for example:

```CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
```

It is typically used like this:

```SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
WHERE one_to_many.left = item;
```

This will return all the items in the right hand table for an entry in the left hand table. This is a very common construct in SQL.

Now, this methodology can be cumbersome with a very large number of entries in the one_to_many table. Often, a join like this would result in an index scan and a fetch for each right hand entry in the table for a particular left hand entry. If you have a very dynamic system, there is not much you can do. However, if you have some data which is fairly static, you can create a summary table with the aggregator.

```CREATE TABLE summary AS
SELECT left, int_array_aggregate(right) AS right
FROM one_to_many
GROUP BY left;
```

This will create a table with one row per left item, and an array of right items. Now this is pretty useless without some way of using the array; that's why there is an array enumerator. You can do

```SELECT left, int_array_enum(right) FROM summary WHERE left = item;
```

The above query using `int_array_enum` produces the same results as

```SELECT left, right FROM one_to_many WHERE left = item;
```

The difference is that the query against the summary table has to get only one row from the table, whereas the direct query against one_to_many must index scan and fetch a row for each entry.

On one system, an EXPLAIN showed a query with a cost of 8488 was reduced to a cost of 329. The original query was a join involving the one_to_many table, which was replaced by:

```SELECT right, count(right) FROM
( SELECT left, int_array_enum(right) AS right
FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
ON (summary.left = lefts.left)
) AS list
GROUP BY right
ORDER BY count DESC;
```
{"ft_lang_label":"__label__en","ft_lang_prob":0.8344591,"math_prob":0.9313369,"size":2940,"snap":"2019-26-2019-30","text_gpt3_token_len":651,"char_repetition_ratio":0.125,"word_repetition_ratio":0.04489796,"special_character_ratio":0.2207483,"punctuation_ratio":0.123239435,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9810565,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-07-21T23:53:01Z\",\"WARC-Record-ID\":\"<urn:uuid:69848f9c-9403-45c1-a7fd-a6486b0552c3>\",\"Content-Length\":\"15130\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:92f97c26-8d7b-4c0d-b255-31f269ec1a15>\",\"WARC-Concurrent-To\":\"<urn:uuid:9c54b0aa-a56d-4406-a324-2ba523ece0b3>\",\"WARC-IP-Address\":\"72.32.157.230\",\"WARC-Target-URI\":\"https://www.postgresql.org/docs/9.2/intagg.html\",\"WARC-Payload-Digest\":\"sha1:7VVWVRSB6K5NVEOOKVCARNJZHRDFRUED\",\"WARC-Block-Digest\":\"sha1:LH4G3GXJHJRES7W5M5DLDNRQ6MOSYE3Z\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-30/CC-MAIN-2019-30_segments_1563195527396.78_warc_CC-MAIN-20190721225759-20190722011759-00063.warc.gz\"}"}