|
| 1 | +\set VERBOSITY terse |
| 2 | +SET search_path = 'public'; |
| 3 | +CREATE SCHEMA pathman; |
| 4 | +CREATE EXTENSION pg_pathman SCHEMA pathman; |
| 5 | +CREATE SCHEMA test; |
| 6 | +CREATE TABLE test.range_rel ( |
| 7 | +idSERIAL PRIMARY KEY, |
| 8 | +dtDATE NOT NULL |
| 9 | +); |
| 10 | +CREATE TABLE test.r2 (LIKE test.range_rel); |
| 11 | +ALTER TABLE test.range_rel ATTACH PARTITION test.r2 |
| 12 | +FOR VALUES FROM ('2015-05-01') TO ('2015-06-01'); |
| 13 | +ERROR: table "range_rel" is not partitioned |
| 14 | +INSERT INTO test.range_rel (dt) |
| 15 | +SELECT g FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) AS g; |
| 16 | +SELECT pathman.create_range_partitions('test.range_rel', 'dt', |
| 17 | +'2015-01-01'::DATE, '1 month'::INTERVAL); |
| 18 | + create_range_partitions |
| 19 | +------------------------- |
| 20 | + 4 |
| 21 | +(1 row) |
| 22 | + |
| 23 | +SELECT * FROM pathman.pathman_partition_list; |
| 24 | + parent | partition | parttype | expr | range_min | range_max |
| 25 | +----------------+------------------+----------+------+------------+------------ |
| 26 | + test.range_rel | test.range_rel_1 | 2 | dt | 01-01-2015 | 02-01-2015 |
| 27 | + test.range_rel | test.range_rel_2 | 2 | dt | 02-01-2015 | 03-01-2015 |
| 28 | + test.range_rel | test.range_rel_3 | 2 | dt | 03-01-2015 | 04-01-2015 |
| 29 | + test.range_rel | test.range_rel_4 | 2 | dt | 04-01-2015 | 05-01-2015 |
| 30 | +(4 rows) |
| 31 | + |
| 32 | +ALTER TABLE test.range_rel ATTACH PARTITION test.r2 |
| 33 | +FOR VALUES IN ('2015-05-01', '2015-06-01'); |
| 34 | +ERROR: pg_pathman only supports queries for range partitions |
| 35 | +ALTER TABLE test.range_rel ATTACH PARTITION test.r2 |
| 36 | +FOR VALUES FROM ('2014-05-01') TO ('2015-06-01'); |
| 37 | +ERROR: specified range [05-01-2014, 06-01-2015) overlaps with existing partitions |
| 38 | +ALTER TABLE test.range_rel ATTACH PARTITION test.r2 |
| 39 | +FOR VALUES FROM ('2015-05-01') TO ('2015-06-01'); |
| 40 | +SELECT * FROM pathman.pathman_partition_list; |
| 41 | + parent | partition | parttype | expr | range_min | range_max |
| 42 | +----------------+------------------+----------+------+------------+------------ |
| 43 | + test.range_rel | test.range_rel_1 | 2 | dt | 01-01-2015 | 02-01-2015 |
| 44 | + test.range_rel | test.range_rel_2 | 2 | dt | 02-01-2015 | 03-01-2015 |
| 45 | + test.range_rel | test.range_rel_3 | 2 | dt | 03-01-2015 | 04-01-2015 |
| 46 | + test.range_rel | test.range_rel_4 | 2 | dt | 04-01-2015 | 05-01-2015 |
| 47 | + test.range_rel | test.r2 | 2 | dt | 05-01-2015 | 06-01-2015 |
| 48 | +(5 rows) |
| 49 | + |
| 50 | +\d+ test.r2; |
| 51 | + Table "test.r2" |
| 52 | + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| 53 | +--------+---------+-----------+----------+---------+---------+--------------+------------- |
| 54 | + id | integer | | not null | | plain | | |
| 55 | + dt | date | | not null | | plain | | |
| 56 | +Check constraints: |
| 57 | + "pathman_r2_check" CHECK (dt >= '05-01-2015'::date AND dt < '06-01-2015'::date) |
| 58 | +Inherits: test.range_rel |
| 59 | + |
| 60 | +ALTER TABLE test.range_rel DETACH PARTITION test.r2; |
| 61 | +SELECT * FROM pathman.pathman_partition_list; |
| 62 | + parent | partition | parttype | expr | range_min | range_max |
| 63 | +----------------+------------------+----------+------+------------+------------ |
| 64 | + test.range_rel | test.range_rel_1 | 2 | dt | 01-01-2015 | 02-01-2015 |
| 65 | + test.range_rel | test.range_rel_2 | 2 | dt | 02-01-2015 | 03-01-2015 |
| 66 | + test.range_rel | test.range_rel_3 | 2 | dt | 03-01-2015 | 04-01-2015 |
| 67 | + test.range_rel | test.range_rel_4 | 2 | dt | 04-01-2015 | 05-01-2015 |
| 68 | +(4 rows) |
| 69 | + |
| 70 | +\d+ test.r2; |
| 71 | + Table "test.r2" |
| 72 | + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| 73 | +--------+---------+-----------+----------+---------+---------+--------------+------------- |
| 74 | + id | integer | | not null | | plain | | |
| 75 | + dt | date | | not null | | plain | | |
| 76 | + |
| 77 | +CREATE TABLE test.r4 PARTITION OF test.range_rel |
| 78 | +FOR VALUES IN ('2015-05-01', '2015-06-01'); |
| 79 | +ERROR: pg_pathman only supports queries for range partitions |
| 80 | +CREATE TABLE test.r4 PARTITION OF test.range_rel |
| 81 | +FOR VALUES FROM ('2014-05-01') TO ('2015-06-01'); |
| 82 | +ERROR: specified range [05-01-2014, 06-01-2015) overlaps with existing partitions |
| 83 | +CREATE TABLE test.r4 PARTITION OF test.range_rel |
| 84 | +FOR VALUES FROM ('2015-06-01') TO ('2016-01-01'); |
| 85 | +\d+ test.r4; |
| 86 | + Table "test.r4" |
| 87 | + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| 88 | +--------+---------+-----------+----------+--------------------------------------------+---------+--------------+------------- |
| 89 | + id | integer | | not null | nextval('test.range_rel_id_seq'::regclass) | plain | | |
| 90 | + dt | date | | not null | | plain | | |
| 91 | +Indexes: |
| 92 | + "r4_pkey" PRIMARY KEY, btree (id) |
| 93 | +Check constraints: |
| 94 | + "pathman_r4_check" CHECK (dt >= '06-01-2015'::date AND dt < '01-01-2016'::date) |
| 95 | +Inherits: test.range_rel |
| 96 | + |
| 97 | +DROP SCHEMA test CASCADE; |
| 98 | +NOTICE: drop cascades to 8 other objects |
| 99 | +DROP EXTENSION pg_pathman CASCADE; |
| 100 | +DROP SCHEMA pathman CASCADE; |