postgres development pg_anonymizer - ghdrako/doc_snipets GitHub Wiki

\c boutique paul 
DROP TABLE IF EXISTS employee CASCADE;
CREATE TABLE employee ( 
id INT PRIMARY KEY, 
full_name TEXT, 
first_day DATE, 
last_day DATE, 
height INT, 
hair TEXT, 
eyes TEXT, 
size TEXT, 
asthma BOOLEAN, 
CHECK(hair = ANY(ARRAY['bald','blond','dark','red'])), 
CHECK(eyes = ANY(ARRAY['blue','green','brown'])) , 
CHECK(size = ANY(ARRAY['S','M','L','XL','XXL'])) 
);

curl -Ls https://dali.bo/employee -o /tmp/employee.tsv 

\COPY employee FROM '/tmp/employee.tsv'

SELECT full_name,first_day, hair, size, asthma 
FROM employee LIMIT 3 ;
full_name | first_day | hair | size | asthma 
--------------+------------+-------+------+--------
Luna Dickens | 2018-07-22 | blond | L | t 
Paul Wolf | 2020-01-15 | bald | M | f 
Rowan Hoeger | 2018-12-01 | dark | XXL | t 

DROP MATERIALIZED VIEW IF EXISTS v_asthma_eyes ;
CREATE MATERIALIZED VIEW v_asthma_eyes AS 
SELECT eyes, asthma FROM employee ; 

SECURITY LABEL FOR anon ON COLUMN v_asthma_eyes.eyes IS 'INDIRECT IDENTIFIER';
SECURITY LABEL FOR anon ON COLUMN v_asthma_eyes.asthma IS 'INDIRECT IDENTIFIER';
SELECT anon.k_anonymity('v_asthma_eyes');

SELECT anon.k_anonymity('v_asthma_eyes');
k_anonymity 
-------------
2 
DROP MATERIALIZED VIEW IF EXISTS v_staff_per_month ;
CREATE MATERIALIZED VIEW v_staff_per_month AS 
SELECT 
  anon.generalize_daterange(first_day,'month') AS first_day, 
  anon.generalize_daterange(last_day, 'month') AS last_day 
FROM employee ;
GRANT SELECT ON v_staff_per_month TO pierre ;

SELECT COUNT(1) 
       FILTER ( 
         WHERE make_date(2019,11,1) 
         BETWEEN lower(first_day) 
         AND COALESCE(upper(last_day),now()) 
       ) 
  FROM v_staff_per_month;

SECURITY LABEL FOR anon ON COLUMN v_staff_per_month.first_day IS 'INDIRECT IDENTIFIER';
SECURITY LABEL FOR anon ON COLUMN v_staff_per_month.last_day IS 'INDIRECT IDENTIFIER';
SELECT anon.k_anonymity('v_staff_per_month');