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)