rtHNSmi9

1#### WORK WITH PARTITIONED TABLES
21. Create table
3CREATE TABLE IF NOT EXISTS "test_table"
4(
5  "customer_id"          integer                  not null,
6  "created_at"           timestamp with time zone not null
7) PARTITION BY RANGE (created_at);
8NOTE: no primary key
92. Create partition
10CREATE TABLE test_table_y2019m10 PARTITION OF test_table
11    FOR VALUES FROM ('2019-10-01') TO ('2019-11-01');
123. Create index for partition
13CREATE INDEX concurrently IF NOT EXISTS test_table_y2019m10_customer_id_created_at_idx ON test_table_y2019m09 (customer_id, created_at);
144. Create index for entire table (will be created for all partitions, incuding those that will be created in the future)
15CREATE INDEX IF NOT EXISTS aaa ON test_table (customer_id, created_at);
165. See the indexes created
17SELECT * from pg_catalog.pg_indexes where tablename like 'test%';
18NOTE: you can manually create another partition and see that it has the index already
19
20
21##### CURSOR EXAMPLE FOR PARTITIONED TABLE
22SELECT add_orders_env_customer_id_index();
23
24CREATE OR REPLACE FUNCTION add_orders_env_customer_id_index()
25    RETURNS text AS $$
26DECLARE
27    pName RECORD;
28    cmd TEXT;
29    sout TEXT;
30    pNames CURSOR
31        FOR SELECT child.relname as name FROM pg_inherits
32            JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
33            JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
34        WHERE parent.relname='orders';
35BEGIN
36    OPEN pNames;
37    sout = '';
38    LOOP
39        FETCH pNames INTO pname;
40        EXIT WHEN NOT FOUND;
41        cmd = 'CREATE INDEX IF NOT EXISTS ' || pname.name || '_env_customer_id_idx ON ' || pname.name || ' (env, customer_id);';
42        execute cmd;
43        sout = sout || ' , '|| cmd;       
44    END LOOP;
45    CREATE INDEX IF NOT EXISTS idx_participants_env_customer_id ON orders (env, customer_id);
46   
47    CLOSE pNames;
48
49    return sout;
50END; $$
51
52    LANGUAGE plpgsql;
53
54NOTES:
55- CREATE INDEX CONCURRENTLY cannot be performed for entire partitioned table; we should go partitions one by one
56- CREATE INDEX CONCURRENTLY cannot be performed from inside a function; still keep this problem as the example, though not usable
57- Index name for partition should be  `partitionName_env_customer_id_idx` and for table it should be `idx_orders_env_driver_id` to stick to the default convention though postgres do not use it, just work with the fields configured