count_distinct

Overview

Version

Build

You can build the RPM / DEB packages for count_distinct using pig build:

pig build pkg count_distinct         # build RPM / DEB packages

Install

You can install count_distinct directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install count_distinct;          # Install for current active PG version

pig ext install -y count_distinct -v 18  # PG 18
pig ext install -y count_distinct -v 17  # PG 17
pig ext install -y count_distinct -v 16  # PG 16
pig ext install -y count_distinct -v 15  # PG 15
pig ext install -y count_distinct -v 14  # PG 14

dnf install -y count_distinct_18       # PG 18
dnf install -y count_distinct_17       # PG 17
dnf install -y count_distinct_16       # PG 16
dnf install -y count_distinct_15       # PG 15
dnf install -y count_distinct_14       # PG 14

apt install -y postgresql-18-count-distinct   # PG 18
apt install -y postgresql-17-count-distinct   # PG 17
apt install -y postgresql-16-count-distinct   # PG 16
apt install -y postgresql-15-count-distinct   # PG 15
apt install -y postgresql-14-count-distinct   # PG 14

Create Extension:

CREATE EXTENSION count_distinct;

Usage

Sources:

• v3.0.2 README
• v3.0.2 aggregate definitions
• Control file

Provides an alternative to COUNT(DISTINCT ...) that avoids sorting and supports parallel aggregation.

CREATE EXTENSION count_distinct;

Functions

Examples

CREATE TABLE test_table (id INT, val INT);
INSERT INTO test_table
SELECT mod(i, 1000), (1000 * random())::int
FROM generate_series(1, 10000) s(i);

-- Instead of:  SELECT id, COUNT(DISTINCT val) FROM test_table GROUP BY 1;
-- Use:
SELECT id, count_distinct(val) FROM test_table GROUP BY 1;

-- Aggregate distinct values into an array
SELECT id, array_agg_distinct(val) FROM test_table GROUP BY 1;

-- Count distinct elements across arrays
SELECT count_distinct_elements(ARRAY[1, 2, 2, 3]);

Types, Memory and Version

Version 3.0.2 accepts fixed-length values passed by value, such as integers, and arrays of those values; it is not a general replacement for distinct text aggregation. Hashing a larger value first makes collisions possible and changes the result into an estimate. These aggregates keep their state in RAM and cannot reliably enforce work_mem; high cardinality or many concurrent groups can exhaust memory. Compare correctness, plans and memory use with the built-in aggregate on the actual workload rather than assuming a speedup. The control file is relocatable and has no preload requirement; C-extension installation normally needs a superuser.