LOAD 'pg_diffix'; SET pg_diffix.strict = false; SET pg_diffix.noise_layer_sd = 0; SET pg_diffix.low_count_layer_sd = 0; SET pg_diffix.outlier_count_min = 1; SET pg_diffix.outlier_count_max = 1; SET pg_diffix.top_count_min = 3; SET pg_diffix.top_count_max = 3; -- Additional tables for SUM testing CREATE TABLE test_customers_negative AS SELECT id, city, -discount as discount, planet FROM test_customers; CREATE TABLE test_customers_mixed AS SELECT id, city, discount - 1.0 as discount, planet FROM test_customers; CALL diffix.mark_personal('public.test_customers_negative', 'id'); CALL diffix.mark_personal('public.test_customers_mixed', 'id'); SET ROLE diffix_test; SET pg_diffix.session_access_level = 'anonymized_trusted'; ---------------------------------------------------------------- -- Sanity checks ---------------------------------------------------------------- SELECT diffix.access_level(); access_level -------------------- anonymized_trusted (1 row) ---------------------------------------------------------------- -- Basic queries ---------------------------------------------------------------- SELECT COUNT(*) FROM test_customers; count ------- 18 (1 row) SELECT COUNT(*) FROM test_purchases; count ------- 36 (1 row) SELECT COUNT(city), COUNT(DISTINCT city) FROM test_customers; count | count -------+------- 17 | 2 (1 row) SELECT COUNT(DISTINCT cid) FROM test_purchases; count ------- 14 (1 row) SELECT city, COUNT(DISTINCT id) FROM test_customers GROUP BY 1; city | count --------+------- * | 3 Rome | 7 Berlin | 8 (3 rows) SELECT COUNT(*) FROM test_customers WHERE planet = 'Earth'; count ------- 17 (1 row) ---------------------------------------------------------------- -- Basic queries - sum ---------------------------------------------------------------- SELECT SUM(id), diffix.sum_noise(id) FROM test_customers; sum | sum_noise -----+----------- 151 | 0 (1 row) SELECT SUM(discount), diffix.sum_noise(discount) FROM test_customers; sum | sum_noise -----+----------- 19 | 0 (1 row) SELECT city, SUM(id), diffix.sum_noise(id) FROM test_customers GROUP BY 1; city | sum | sum_noise --------+-----+----------- * | | Rome | 59 | 0 Berlin | 68 | 0 (3 rows) SELECT city, SUM(discount), diffix.sum_noise(discount) FROM test_customers GROUP BY 1; city | sum | sum_noise --------+-----------+----------- * | | Rome | 4.8333335 | 0 Berlin | 9 | 0 (3 rows) SELECT SUM(discount), diffix.sum_noise(discount) FROM test_customers_negative; sum | sum_noise -----+----------- -19 | 0 (1 row) SELECT SUM(discount), diffix.sum_noise(discount) FROM test_customers_mixed; sum | sum_noise -----+----------- 2.5 | 0 (1 row) -- sum supports numeric type SELECT city, SUM(discount::numeric), pg_typeof(SUM(discount::numeric)), diffix.sum_noise(discount::numeric) FROM test_customers GROUP BY 1; city | sum | pg_typeof | sum_noise --------+------------------+-----------+----------- * | | numeric | Rome | 4.83333333333333 | numeric | 0 Berlin | 9 | numeric | 0 (3 rows) ---------------------------------------------------------------- -- Basic queries - avg ---------------------------------------------------------------- SELECT city, AVG(discount), diffix.avg_noise(discount) FROM test_customers GROUP BY 1 EXCEPT SELECT city, SUM(discount) / COUNT(discount), diffix.sum_noise(discount) / COUNT(discount) FROM test_customers GROUP BY 1; city | avg | avg_noise ------+-----+----------- (0 rows) SELECT city, AVG(id), diffix.avg_noise(id) FROM test_customers GROUP BY 1 EXCEPT SELECT city, SUM(id)::float8 / COUNT(id), diffix.sum_noise(id) / COUNT(id) FROM test_customers GROUP BY 1; city | avg | avg_noise ------+-----+----------- (0 rows) ---------------------------------------------------------------- -- Basic queries - expanding constants in target expressions ---------------------------------------------------------------- SELECT 1 FROM test_patients; ?column? ---------- 1 1 1 1 1 1 1 1 1 1 1 (11 rows) SELECT cast(1 as real) FROM test_patients; float4 -------- 1 1 1 1 1 1 1 1 1 1 1 (11 rows) SELECT 1, COUNT(*) FROM test_patients; ?column? | count ----------+------- 1 | 11 (1 row) SELECT 1, city FROM test_customers; ?column? | city ----------+-------- 1 | * 1 | * 1 | * 1 | Rome 1 | Rome 1 | Rome 1 | Rome 1 | Rome 1 | Rome 1 | Rome 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin (18 rows) SELECT city, 'aaaa' FROM test_customers; city | ?column? --------+---------- * | aaaa * | aaaa * | aaaa Rome | aaaa Rome | aaaa Rome | aaaa Rome | aaaa Rome | aaaa Rome | aaaa Rome | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa (18 rows) ---------------------------------------------------------------- -- Multi-AID queries ---------------------------------------------------------------- SELECT city FROM test_patients GROUP BY 1; city -------- Rome Berlin (2 rows) SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM test_patients; count | count | count -------+-------+------- 11 | 11 | 2 (1 row) ---------------------------------------------------------------- -- LCF & Filtering ---------------------------------------------------------------- SELECT city FROM test_customers; city -------- * * * Rome Rome Rome Rome Rome Rome Rome Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin (18 rows) SELECT city FROM test_customers GROUP BY 1 HAVING length(city) <> 4; city -------- * Berlin (2 rows) SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM test_customers WHERE city = 'London'; count | count | count -------+-------+------- 0 | 0 | 0 (1 row) ---------------------------------------------------------------- -- Empty tables ---------------------------------------------------------------- SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM empty_test_customers; count | count | count -------+-------+------- 0 | 0 | 0 (1 row) ---------------------------------------------------------------- -- Minimal count ---------------------------------------------------------------- -- Zero for global aggregation SELECT COUNT(DISTINCT planet) FROM test_customers; count ------- 1 (1 row) -- `low_count_min_threshold` for queries with GROUP BY SELECT city, COUNT(DISTINCT city) FROM test_customers GROUP BY 1; city | count --------+------- * | 3 Rome | 3 Berlin | 3 (3 rows) SELECT discount, COUNT(DISTINCT id) FROM test_customers GROUP BY 1; discount | count ----------+------- | 4 0.5 | 4 1 | 6 2 | 4 (4 rows) ---------------------------------------------------------------- -- Prepared statements ---------------------------------------------------------------- PREPARE prepared_floor_by(numeric) AS SELECT diffix.floor_by(discount, $1), count(*) FROM test_customers GROUP BY 1; EXECUTE prepared_floor_by(2.0); floor_by | count ----------+------- 0 | 13 2 | 4 (2 rows)