count_distinct

pig build pkg count_distinct;		# build rpm/deb

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

pig install count_distinct;		# install via package name, for the active PG version

pig install count_distinct -v 18;   # install for PG 18
pig install count_distinct -v 17;   # install for PG 17
pig install count_distinct -v 16;   # install for PG 16
pig install count_distinct -v 15;   # install for PG 15
pig install count_distinct -v 14;   # install for PG 14

CREATE EXTENSION count_distinct;

CREATE EXTENSION count_distinct;

CREATE TABLE test_table (id INT, val INT);
INSERT INTO test_table
SELECT mod(i, 1000), (1000 * random())::int
FROM generate_series(1, 10000000) 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]);