grocery-manager/maintenance.sql

UPDATE products SET unit_id = (SELECT unit_id FROM (SELECT count(t.id), defaults.unit_id, product_id
FROM transactions t
JOIN units ON unit_id = units.id
JOIN unit_types_default defaults USING(unit_type_id)
GROUP BY defaults.unit_id, product_id) q
WHERE id = product_id
ORDER BY count DESC
LIMIT 1)
WHERE unit_id IS NULL;

BEGIN;
--ALTER TABLE transactions DROP CONSTRAINT fk_product_id ;
--ALTER TABLE transactions ADD CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE CASCADE;
--ALTER TABLE conversions_complex DROP CONSTRAINT fk_product_id ;
--ALTER TABLE conversions_complex ADD CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE CASCADE;
--ALTER TABLE transactions DROP CONSTRAINT fk_store_id ;
--ALTER TABLE transactions ADD CONSTRAINT fk_store_id FOREIGN KEY (store_id) REFERENCES stores(id) ON UPDATE CASCADE; 
ALTER TABLE products DROP CONSTRAINT fk_category_id ;
ALTER TABLE products ADD CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE CASCADE;
ALTER TABLE categories DROP CONSTRAINT fk_group_id ;
ALTER TABLE categories ADD CONSTRAINT fk_group_id FOREIGN KEY (group_id) REFERENCES groups(id) ON UPDATE CASCADE; 
DO
$$
DECLARE
  item record;
  next int;
BEGIN
  --SELECT 1 INTO next;
  --FOR item IN (SELECT id FROM products ORDER BY id) LOOP
  --  UPDATE products SET id = next WHERE id = item.id;
  --  SELECT next+1 INTO next;
  --END LOOP;
  --SELECT 1 INTO next;
  --FOR item IN (SELECT id FROM stores ORDER BY id) LOOP
  --  UPDATE stores SET id = next WHERE id = item.id;
  --  SELECT next+1 INTO next;
  --END LOOP;
  -- SELECT 1 INTO next;
  -- FOR item IN (SELECT id FROM transactions ORDER BY id) LOOP
    -- UPDATE transactions SET id = next WHERE id = item.id;
    -- SELECT next+1 INTO next;
  -- END LOOP;
  SELECT 1 INTO next;
  FOR item IN (SELECT id FROM categories ORDER BY id) LOOP
    UPDATE categories SET id = next WHERE id = item.id;
    SELECT next+1 INTO next;
  END LOOP;
  SELECT 1 INTO next;
  FOR item IN (SELECT id FROM groups ORDER BY id) LOOP
    UPDATE groups SET id = next WHERE id = item.id;
    SELECT next+1 INTO next;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
--ALTER TABLE transactions DROP CONSTRAINT fk_product_id ;
--ALTER TABLE transactions ADD CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(id);
--ALTER TABLE conversions_complex DROP CONSTRAINT fk_product_id ;
--ALTER TABLE conversions_complex ADD CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products(id);
--ALTER TABLE transactions DROP CONSTRAINT fk_store_id ;
--ALTER TABLE transactions ADD CONSTRAINT fk_store_id FOREIGN KEY (store_id) REFERENCES products(id);
ALTER TABLE products DROP CONSTRAINT fk_category_id ;
ALTER TABLE products ADD CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES categories(id);
ALTER TABLE categories DROP CONSTRAINT fk_group_id ;
ALTER TABLE categories ADD CONSTRAINT fk_group_id FOREIGN KEY (group_id) REFERENCES groups(id); 
ROLLBACK;
grocery=> SELECT product_id, count(*) FROM transactions GROUP BY product_id ORDER BY product_id;
 product_id | count
------------+-------
          2 |    57
          3 |    23
          4 |    32
          5 |    75
          6 |     5
          7 |    61
          8 |     9
         19 |     2
         20 |    56
         21 |    26
         22 |    45
         23 |    33
         30 |     1
         31 |    31
         32 |    42
         33 |    13
         34 |    45
         35 |    30
        141 |    10
        142 |     3
        143 |    12
        144 |    10
        145 |     6
        148 |    32
        149 |    27
        208 |     1
        312 |     3
        313 |     8
        314 |     2
        315 |    14
        317 |     4
        318 |    12
        319 |     8
        320 |     2
        321 |     6
        322 |     2
        489 |    12
        490 |     8
        496 |     2
        497 |     2
        501 |    31
        502 |    10
        533 |     4
        534 |     1
        535 |     3
        536 |    22
        537 |    22
        538 |     2
        539 |    19
        540 |     1
        541 |    14
        542 |     3
        544 |     9
        545 |     8
        546 |     5
        547 |    12
        548 |     7
        549 |    17
        551 |     2
        552 |    15
        553 |     2
        554 |     2
        555 |     3
        556 |     3
        557 |     6
        559 |     2
        563 |    11
        564 |     1
        565 |     5
        566 |     2
        567 |     5
        568 |     5
        569 |     2
        570 |     1
        571 |     6
        572 |     4
        695 |     4
        701 |    10
        704 |     5
        708 |     2
        712 |     2
        715 |     1
       1338 |     1
       1339 |     2
       1340 |     2
       1341 |     6
       1342 |     4
       1362 |     2
       1363 |     1
       1364 |     1
       1374 |     2
       1922 |    14
       1923 |     2
       1924 |     1
       1925 |     5
       1926 |     2
       1927 |     2
       1928 |     1
       1929 |     1
       1930 |     6
       1931 |     3
       1944 |     3
       1945 |     4
       1946 |     5
       1947 |     1
       1948 |     1
       1951 |     5
       1965 |     5
       1966 |     5
       1991 |     2
       1992 |     1
       1993 |     9
       1994 |     3
       2005 |     1
       2006 |     3
       2118 |     2
       2119 |     1
       2120 |     1
       2128 |     3
       2129 |     2
       2132 |     1
       2149 |     3
       2150 |     2
       2151 |     1
       2152 |     1
       2155 |     1
       2197 |    13
       2198 |     3
       2199 |    11
       2200 |     6
       2203 |     2
       2206 |     3
       2215 |     3
       2216 |     3
       2217 |     3
       2226 |     2
       2262 |     1
       2263 |     1
       2264 |     2
       2267 |     2
       2347 |     1
       2377 |     1
       2378 |     1
       2379 |     1
       2380 |     1
       2381 |    12
       2399 |     2
       2400 |     1
       2401 |     1
       2404 |     2
       2407 |     1
       2411 |     1
       2425 |     1
       2426 |     4
       2427 |     4
       2436 |     1
       2437 |     1
       2448 |     1
       2449 |     2
       2450 |     1
       2688 |     2
       2711 |     2
       2712 |     1
       2713 |     3
       2714 |     2
       2715 |     2
       2718 |     1
       2722 |     2
       2726 |     1
       2731 |    22
       2734 |     1
       2737 |     2
       2741 |     4
       2746 |     1
       2747 |     4
       2750 |     1
       2760 |     2
       2765 |     3
       2766 |     7
       2769 |     4
       2776 |     2
       2777 |     1
       2781 |     9
       2784 |     1
       2792 |     1
       2793 |     1
       2794 |     1
       2842 |     1
       2843 |     1
       2861 |     1
       2970 |     1
       2971 |     1
       2972 |     5
       2976 |     1
       3189 |     1
       3190 |     1
       3197 |     5
       3198 |     1
       3201 |     2
       3204 |     2
       3208 |     3
       3220 |     2
       3261 |     1
       3262 |     1
       3263 |     1
       3264 |     1
       3265 |     1
       3266 |     1
       3267 |     1
       3279 |     1
       3280 |     1
       3286 |     1
       3292 |     1
       3307 |     1
       3308 |     1
(215 rows)
grocery=>  SELECT store_id, count(*) FROM transactions GROUP BY store_id ORDER BY store_id;
 store_id | count
----------+-------
        1 |   791
        2 |   368
        3 |     3
        4 |    12
        5 |    16
        6 |    57
        9 |    19
       10 |    22
       11 |     9
       12 |     1
       13 |    31
       14 |     1
       15 |    11
       16 |     2
       17 |     1
       18 |     3
       19 |     1
       20 |    18
       21 |     1
       22 |     4
       23 |     3
       24 |     1
       25 |    22
       26 |     1
       27 |     1
       28 |     3
       29 |     3
       30 |     1
(28 rows)