|
| 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 | +INSERT INTO test.range_rel (dt) |
| 11 | +SELECT g FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) AS g; |
| 12 | +SELECT pathman.create_range_partitions('test.range_rel', 'dt', |
| 13 | +'2015-01-01'::DATE, '1 month'::INTERVAL); |
| 14 | + create_range_partitions |
| 15 | +------------------------- |
| 16 | + 4 |
| 17 | +(1 row) |
| 18 | + |
| 19 | +SELECT * FROM pathman.pathman_partition_list; |
| 20 | + parent | partition | parttype | expr | range_min | range_max |
| 21 | +----------------+------------------+----------+------+------------+------------ |
| 22 | + test.range_rel | test.range_rel_1 | 2 | dt | 01-01-2015 | 02-01-2015 |
| 23 | + test.range_rel | test.range_rel_2 | 2 | dt | 02-01-2015 | 03-01-2015 |
| 24 | + test.range_rel | test.range_rel_3 | 2 | dt | 03-01-2015 | 04-01-2015 |
| 25 | + test.range_rel | test.range_rel_4 | 2 | dt | 04-01-2015 | 05-01-2015 |
| 26 | +(4 rows) |
| 27 | + |
| 28 | +CREATE TABLE test.r2 (LIKE test.range_rel); |
| 29 | +ALTER TABLE test.range_rel ATTACH PARTITION test.r2 |
| 30 | +FOR VALUES FROM ('2015-05-01') TO ('2015-06-01'); |
| 31 | +SELECT * FROM pathman.pathman_partition_list; |
| 32 | + parent | partition | parttype | expr | range_min | range_max |
| 33 | +----------------+------------------+----------+------+------------+------------ |
| 34 | + test.range_rel | test.range_rel_1 | 2 | dt | 01-01-2015 | 02-01-2015 |
| 35 | + test.range_rel | test.range_rel_2 | 2 | dt | 02-01-2015 | 03-01-2015 |
| 36 | + test.range_rel | test.range_rel_3 | 2 | dt | 03-01-2015 | 04-01-2015 |
| 37 | + test.range_rel | test.range_rel_4 | 2 | dt | 04-01-2015 | 05-01-2015 |
| 38 | + test.range_rel | test.r2 | 2 | dt | 05-01-2015 | 06-01-2015 |
| 39 | +(5 rows) |
| 40 | + |
| 41 | +\d+ test.r2; |
| 42 | + Table "test.r2" |
| 43 | + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| 44 | +--------+---------+-----------+----------+---------+---------+--------------+------------- |
| 45 | + id | integer | | not null | | plain | | |
| 46 | + dt | date | | not null | | plain | | |
| 47 | +Check constraints: |
| 48 | + "pathman_r2_check" CHECK (dt >= '05-01-2015'::date AND dt < '06-01-2015'::date) |
| 49 | +Inherits: test.range_rel |
| 50 | + |
| 51 | +ALTER TABLE test.range_rel DETACH PARTITION test.r2; |
| 52 | +NOTICE: trigger "range_rel_upd_trig" for relation "test.r2" does not exist, skipping |
| 53 | +SELECT * FROM pathman.pathman_partition_list; |
| 54 | + parent | partition | parttype | expr | range_min | range_max |
| 55 | +----------------+------------------+----------+------+------------+------------ |
| 56 | + test.range_rel | test.range_rel_1 | 2 | dt | 01-01-2015 | 02-01-2015 |
| 57 | + test.range_rel | test.range_rel_2 | 2 | dt | 02-01-2015 | 03-01-2015 |
| 58 | + test.range_rel | test.range_rel_3 | 2 | dt | 03-01-2015 | 04-01-2015 |
| 59 | + test.range_rel | test.range_rel_4 | 2 | dt | 04-01-2015 | 05-01-2015 |
| 60 | +(4 rows) |
| 61 | + |
| 62 | +\d+ test.r2; |
| 63 | + Table "test.r2" |
| 64 | + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| 65 | +--------+---------+-----------+----------+---------+---------+--------------+------------- |
| 66 | + id | integer | | not null | | plain | | |
| 67 | + dt | date | | not null | | plain | | |
| 68 | + |
| 69 | +DROP SCHEMA test CASCADE; |
| 70 | +NOTICE: drop cascades to 7 other objects |
| 71 | +DROP EXTENSION pg_pathman CASCADE; |
| 72 | +DROP SCHEMA pathman CASCADE; |