LOAD 'pg_diffix'; SET pg_diffix.noise_layer_sd = 7; SET pg_diffix.low_count_layer_sd = 3; SET ROLE diffix_test; SET pg_diffix.session_access_level = 'publish_trusted'; ---------------------------------------------------------------- -- Sanity checks ---------------------------------------------------------------- SELECT diffix.access_level(); access_level ----------------- publish_trusted (1 row) ---------------------------------------------------------------- -- Basic queries ---------------------------------------------------------------- SELECT COUNT(*) FROM test_customers; count ------- 15 (1 row) SELECT COUNT(*) FROM test_purchases; count ------- 23 (1 row) SELECT COUNT(city), COUNT(DISTINCT city) FROM test_customers; count | count -------+------- 14 | 1 (1 row) SELECT COUNT(DISTINCT cid) FROM test_purchases; count ------- 9 (1 row) SELECT city, COUNT(DISTINCT id) FROM test_customers GROUP BY 1; city | count --------+------- Rome | 8 Berlin | 25 (2 rows) ---------------------------------------------------------------- -- Basic queries - expanding constants in target expressions ---------------------------------------------------------------- SELECT 1 FROM test_patients; ?column? ---------- 1 1 1 1 1 (5 rows) SELECT cast(1 as real) FROM test_patients; float4 -------- 1 1 1 1 1 (5 rows) SELECT 1, COUNT(*) FROM test_patients; ?column? | count ----------+------- 1 | 5 (1 row) SELECT 1, city FROM test_customers; ?column? | city ----------+-------- 1 | Rome 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 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin 1 | Berlin (33 rows) SELECT city, 'aaaa' FROM test_customers; city | ?column? --------+---------- Rome | 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 Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa Berlin | aaaa (33 rows) ---------------------------------------------------------------- -- Multi-AID queries ---------------------------------------------------------------- SELECT city FROM test_patients GROUP BY 1; city ------ * (1 row) SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM test_patients; count | count | count -------+-------+------- 5 | 5 | 0 (1 row) ---------------------------------------------------------------- -- LCF & Filtering ---------------------------------------------------------------- SELECT id FROM test_customers; id ---- (27 rows) SELECT city FROM test_customers; city -------- Rome Rome Rome Rome Rome Rome Rome Rome Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin Berlin (33 rows) SELECT city FROM test_customers GROUP BY 1 HAVING length(city) <> 4; city -------- Berlin (1 row) SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM london_customers; 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)