| set client_min_messages = WARNING; |
| set timezone to '+07:00'; |
| DROP SCHEMA IF EXISTS partition_ddl2 CASCADE; |
| CREATE SCHEMA partition_ddl2; |
| set search_path to partition_ddl2; |
| CREATE TABLE rank ( |
| id int, |
| rank int, |
| year smallint, |
| gender char(1), |
| count int ) |
| DISTRIBUTED BY (id, gender, year); |
| create table rank2 (LIKE rank) |
| PARTITION BY LIST (gender) |
| SUBPARTITION BY RANGE (year) |
| SUBPARTITION TEMPLATE (start ('2000') end ('2006') every (interval '1')) |
| (PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M')); |
| ERROR: operator does not exist: smallint pg_catalog.+ interval |
| HINT: No operator matches the given name and argument types. You might need to add explicit type casts. |
| drop table rank; |
| create table ggg (a char(1), b char(2), d char(3)) |
| distributed by (a) |
| partition by LIST (b) |
| ( partition aa values ('a', 'b', 'c', 'd'), |
| partition bb values ('e', 'f', NULL) ); |
| insert into ggg values ('a','e','111'); |
| insert into ggg values ('b',NULL,'111'); |
| -- order 1,2 |
| select * from ggg order by 1,2; |
| a | b | d |
| ---+----+----- |
| a | e | 111 |
| b | | 111 |
| (2 rows) |
| |
| -- order 1,2 |
| select * from ggg_1_prt_bb order by 1,2; |
| a | b | d |
| ---+----+----- |
| a | e | 111 |
| b | | 111 |
| (2 rows) |
| |
| drop table ggg; |
| -- QA-877 |
| -- Johnny Soedomo |
| -- Updated test case to use default tablespace. We now support partition and tablespace |
| create table ggg (a char(1), b char(2), d char(3)) |
| distributed by (a) |
| partition by LIST (b) |
| ( partition aa values ('a', 'b', 'c', 'd') tablespace pg_default); |
| drop table ggg; |
| create table mpp3137_region |
| ( |
| R_REGIONKEY INTEGER, |
| R_NAME CHAR(25), |
| R_COMMENT VARCHAR(152), |
| primary key (r_regionkey, r_name, r_comment) |
| ) |
| partition by list (r_regionkey) |
| subpartition by list (r_name) |
| subpartition by list (r_comment) subpartition template ( |
| values('ges. thinly even pinto beans ca'), |
| values('uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl','lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to','hs use ironic, even requests. s'), |
| values('ly final courts cajole furiously final excuse'), |
| values(null) |
| ) |
| ( |
| partition p1 values('4','1','3','0','2')(subpartition sp1 values('MIDDLE EAST','AMERICA'),subpartition sp2 values('AFRICA','EUROPE','ASIA')) |
| ); |
| select * from pg_partition_tree('mpp3137_region'); |
| relid | parentrelid | isleaf | level |
| -------------------------------------------+-----------------------------------+--------+------- |
| mpp3137_region | | f | 0 |
| mpp3137_region_1_prt_p1 | mpp3137_region | f | 1 |
| mpp3137_region_1_prt_p1_2_prt_sp1 | mpp3137_region_1_prt_p1 | f | 2 |
| mpp3137_region_1_prt_p1_2_prt_sp2 | mpp3137_region_1_prt_p1 | f | 2 |
| mpp3137_region_1_prt_p1_2_prt_sp1_3_prt_1 | mpp3137_region_1_prt_p1_2_prt_sp1 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp1_3_prt_2 | mpp3137_region_1_prt_p1_2_prt_sp1 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp1_3_prt_3 | mpp3137_region_1_prt_p1_2_prt_sp1 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp1_3_prt_4 | mpp3137_region_1_prt_p1_2_prt_sp1 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp2_3_prt_1 | mpp3137_region_1_prt_p1_2_prt_sp2 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp2_3_prt_2 | mpp3137_region_1_prt_p1_2_prt_sp2 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp2_3_prt_3 | mpp3137_region_1_prt_p1_2_prt_sp2 | t | 3 |
| mpp3137_region_1_prt_p1_2_prt_sp2_3_prt_4 | mpp3137_region_1_prt_p1_2_prt_sp2 | t | 3 |
| (12 rows) |
| |
| COPY mpp3137_region from STDIN delimiter '|'; |
| select count(*) from mpp3137_region; |
| count |
| ------- |
| 5 |
| (1 row) |
| |
| select * from mpp3137_region order by r_regionkey; |
| r_regionkey | r_name | r_comment |
| -------------+---------------------------+-------------------------------------------------------------------------------------------------------------------- |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |
| 1 | AMERICA | hs use ironic, even requests. s |
| 2 | ASIA | ges. thinly even pinto beans ca |
| 3 | EUROPE | ly final courts cajole furiously final excuse |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl |
| (5 rows) |
| |
| drop table mpp3137_region; |
| drop table if exists mpp3285_lineitem; |
| CREATE TABLE mpp3285_LINEITEM ( |
| L_ORDERKEY INT8, |
| L_PARTKEY INTEGER, |
| L_SUPPKEY INTEGER, |
| L_LINENUMBER integer, |
| L_QUANTITY decimal, |
| L_EXTENDEDPRICE decimal, |
| L_DISCOUNT decimal, |
| L_TAX decimal, |
| L_RETURNFLAG CHAR(1), |
| L_LINESTATUS CHAR(1), |
| L_SHIPDATE date, |
| L_COMMITDATE date, |
| L_RECEIPTDATE date, |
| L_SHIPINSTRUCT CHAR(25), |
| L_SHIPMODE CHAR(10), |
| L_COMMENT VARCHAR(44) |
| ) |
| partition by range (l_commitdate) |
| ( |
| partition p1 start('1992-01-31') end('1998-11-01') every(interval '20 months') |
| ); |
| insert into mpp3285_lineitem values (18182,5794,3295,4,9,15298.11,0.04,0.01,'N','O','1995-07-04'::date,'1995-05-30'::date,'1995-08-03'::date,'DELIVER IN PERSON','RAIL','y special platelets.'); |
| select * from mpp3285_lineitem; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+---------------------------+------------+---------------------- |
| 18182 | 5794 | 3295 | 4 | 9 | 15298.11 | 0.04 | 0.01 | N | O | 07-04-1995 | 05-30-1995 | 08-03-1995 | DELIVER IN PERSON | RAIL | y special platelets. |
| (1 row) |
| |
| drop table mpp3285_lineitem; |
| CREATE TABLE mpp3282_PARTSUPP ( |
| PS_PARTKEY INTEGER, |
| PS_SUPPKEY INTEGER, |
| PS_AVAILQTY integer, |
| PS_SUPPLYCOST decimal, |
| PS_COMMENT VARCHAR(199) |
| ) |
| partition by range (ps_suppkey) |
| subpartition by range (ps_partkey) |
| subpartition by range (ps_supplycost) subpartition template (start('1') end('1001') every(500)) |
| ( |
| partition p1 start('1') end('10001') every(5000) |
| (subpartition sp1 start('1') end('200001') every(66666) |
| ) |
| ); |
| copy mpp3282_partsupp from stdin delimiter '|'; |
| select * from mpp3282_partsupp; |
| ps_partkey | ps_suppkey | ps_availqty | ps_supplycost | ps_comment |
| ------------+------------+-------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 | 2 | 3325 | 771.64 | , even theodolites. regular, final theodolites eat after the carefully pending foxes. furiously regular deposits sleep slyly. carefully bold realms above the ironic dependencies haggle careful |
| (1 row) |
| |
| CREATE TABLE mpp3238_supplier( |
| S_SUPPKEY INTEGER, |
| S_NAME CHAR(25), |
| S_ADDRESS VARCHAR(40), |
| S_NATIONKEY INTEGER, |
| S_PHONE CHAR(15), |
| S_ACCTBAL decimal, |
| S_COMMENT VARCHAR(101) |
| ) |
| partition by range (s_nationkey) |
| ( |
| partition p1 start(0) , |
| partition p2 start(12) end(13), |
| partition p3 end(20) inclusive, |
| partition p4 start(21), |
| partition p5 start(22) end(25) |
| ); |
| insert into mpp3238_supplier values(1,'Supplier#000000001',' N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ',17,'27-918-335-1736',5755.94,'each slyly above the careful'); |
| select * from mpp3238_supplier; |
| s_suppkey | s_name | s_address | s_nationkey | s_phone | s_acctbal | s_comment |
| -----------+---------------------------+-------------------------------------+-------------+-----------------+-----------+------------------------------ |
| 1 | Supplier#000000001 | N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ | 17 | 27-918-335-1736 | 5755.94 | each slyly above the careful |
| (1 row) |
| |
| CREATE TABLE mpp3219_CUSTOMER ( |
| C_CUSTKEY INTEGER, |
| C_NAME VARCHAR(25), |
| C_ADDRESS VARCHAR(40), |
| C_NATIONKEY INTEGER, |
| C_PHONE CHAR(15), |
| C_ACCTBAL decimal, |
| C_MKTSEGMENT CHAR(10), |
| C_COMMENT VARCHAR(117) |
| ) |
| partition by range (c_acctbal) |
| ( |
| partition sp1 start('-999.99') end('9833.01'), |
| partition sp2 end('9905.01'), |
| partition sp3 end('9978.01')); |
| drop table mpp3219_customer; |
| drop table mpp3190_partsupp; |
| ERROR: table "mpp3190_partsupp" does not exist |
| -- This should fail |
| CREATE TABLE mpp3190_PARTSUPP ( |
| PS_PARTKEY INTEGER, |
| PS_SUPPKEY INTEGER, |
| PS_AVAILQTY integer, |
| PS_SUPPLYCOST decimal, |
| PS_COMMENT VARCHAR(199) ) |
| partition by range (ps_supplycost) |
| subpartition by range (ps_suppkey) subpartition template (start(1) end(10000) every(2499)) |
| (partition p1, partition p2, partition p3 ); |
| ERROR: missing boundary specification in partition "p1" of type RANGE |
| LINE 9: (partition p1, partition p2, partition p3 ); |
| ^ |
| drop table if exists mpp3304_customer; |
| create table mpp3304_CUSTOMER ( |
| C_CUSTKEY INTEGER, |
| C_NAME VARCHAR(25), |
| C_ADDRESS VARCHAR(40), |
| C_NATIONKEY INTEGER, |
| C_PHONE CHAR(15), |
| C_ACCTBAL decimal, |
| C_MKTSEGMENT CHAR(10), |
| C_COMMENT VARCHAR(117) |
| ) |
| partition by range (c_custkey) |
| subpartition by range (c_acctbal) |
| subpartition template (start('-999.99') end('10000.99') every(11000) |
| ) |
| subpartition by range (c_nationkey) |
| subpartition template (start('0') end('25') every(5)) |
| ( |
| partition p1 start('1') end('150001') every(50000) |
| ); |
| Alter table mpp3304_customer alter partition for (6) rename partition for ('-999.99') to newname; |
| select * from pg_partition_tree('mpp3304_customer'); |
| relid | parentrelid | isleaf | level |
| ---------------------------------------------------+-------------------------------------------+--------+------- |
| mpp3304_customer | | f | 0 |
| mpp3304_customer_1_prt_p1_1 | mpp3304_customer | f | 1 |
| mpp3304_customer_1_prt_p1_2 | mpp3304_customer | f | 1 |
| mpp3304_customer_1_prt_p1_3 | mpp3304_customer | f | 1 |
| mpp3304_customer_1_prt_p1_1_2_prt_newname | mpp3304_customer_1_prt_p1_1 | f | 2 |
| mpp3304_customer_1_prt_p1_1_2_prt_2 | mpp3304_customer_1_prt_p1_1 | f | 2 |
| mpp3304_customer_1_prt_p1_2_2_prt_1 | mpp3304_customer_1_prt_p1_2 | f | 2 |
| mpp3304_customer_1_prt_p1_2_2_prt_2 | mpp3304_customer_1_prt_p1_2 | f | 2 |
| mpp3304_customer_1_prt_p1_3_2_prt_1 | mpp3304_customer_1_prt_p1_3 | f | 2 |
| mpp3304_customer_1_prt_p1_3_2_prt_2 | mpp3304_customer_1_prt_p1_3 | f | 2 |
| mpp3304_customer_1_prt_p1_1_2_prt_newname_3_prt_1 | mpp3304_customer_1_prt_p1_1_2_prt_newname | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_newname_3_prt_2 | mpp3304_customer_1_prt_p1_1_2_prt_newname | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_newname_3_prt_3 | mpp3304_customer_1_prt_p1_1_2_prt_newname | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_newname_3_prt_4 | mpp3304_customer_1_prt_p1_1_2_prt_newname | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_newname_3_prt_5 | mpp3304_customer_1_prt_p1_1_2_prt_newname | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_2_3_prt_1 | mpp3304_customer_1_prt_p1_1_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_2_3_prt_2 | mpp3304_customer_1_prt_p1_1_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_2_3_prt_3 | mpp3304_customer_1_prt_p1_1_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_2_3_prt_4 | mpp3304_customer_1_prt_p1_1_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_1_2_prt_2_3_prt_5 | mpp3304_customer_1_prt_p1_1_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_1_3_prt_1 | mpp3304_customer_1_prt_p1_2_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_1_3_prt_2 | mpp3304_customer_1_prt_p1_2_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_1_3_prt_3 | mpp3304_customer_1_prt_p1_2_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_1_3_prt_4 | mpp3304_customer_1_prt_p1_2_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_1_3_prt_5 | mpp3304_customer_1_prt_p1_2_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_2_3_prt_1 | mpp3304_customer_1_prt_p1_2_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_2_3_prt_2 | mpp3304_customer_1_prt_p1_2_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_2_3_prt_3 | mpp3304_customer_1_prt_p1_2_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_2_3_prt_4 | mpp3304_customer_1_prt_p1_2_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_2_2_prt_2_3_prt_5 | mpp3304_customer_1_prt_p1_2_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_1_3_prt_1 | mpp3304_customer_1_prt_p1_3_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_1_3_prt_2 | mpp3304_customer_1_prt_p1_3_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_1_3_prt_3 | mpp3304_customer_1_prt_p1_3_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_1_3_prt_4 | mpp3304_customer_1_prt_p1_3_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_1_3_prt_5 | mpp3304_customer_1_prt_p1_3_2_prt_1 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_2_3_prt_1 | mpp3304_customer_1_prt_p1_3_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_2_3_prt_2 | mpp3304_customer_1_prt_p1_3_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_2_3_prt_3 | mpp3304_customer_1_prt_p1_3_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_2_3_prt_4 | mpp3304_customer_1_prt_p1_3_2_prt_2 | t | 3 |
| mpp3304_customer_1_prt_p1_3_2_prt_2_3_prt_5 | mpp3304_customer_1_prt_p1_3_2_prt_2 | t | 3 |
| (40 rows) |
| |
| create table mpp3045_hhh (a char(1), b date, d char(3)) with (appendonly=true) |
| distributed by (a) |
| partition by range (b) |
| (partition aa start (date '2007-01-01') end (date '2008-01-01'), |
| partition bb start (date '2008-01-01') end (date '2009-01-01')); |
| alter table mpp3045_hhh add partition aa; -- missing range |
| ERROR: missing boundary specification in partition "aa" of type RANGE |
| alter table mpp3045_hhh add partition aa start(date '2012-01-01') end (date '2013-01-01'); -- duplicate partition name |
| ERROR: relation "mpp3045_hhh_1_prt_aa" already exists |
| drop table if exists mpp3287_nation; |
| CREATE TABLE mpp3287_NATION ( |
| N_NATIONKEY INTEGER, |
| N_NAME CHAR(25), |
| N_REGIONKEY INTEGER, |
| N_COMMENT VARCHAR(152) |
| ) |
| partition by range (n_nationkey) |
| ( |
| partition p1 start('0') WITH (appendonly=true,checksum=true,blocksize=1998848,compresslevel=4), |
| partition p2 start('11') end('15') inclusive WITH (checksum=false,appendonly=true,blocksize=655360,compresslevel=4), |
| partition p3 start('16') end('19'), partition p4 start('19') WITH (compresslevel=8,appendonly=true,checksum=false,blocksize=884736), |
| partition p5 start('20') |
| ); |
| delete from mpp3287_nation; |
| INSERT INTO mpp3287_nation VALUES (1, 'ARGENTINA ', 1, 'al foxes promise slyly according to the regular accounts. bold requests alon'); |
| INSERT INTO mpp3287_nation VALUES (3, 'CANADA ', 1, 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'); |
| INSERT INTO mpp3287_nation VALUES (5, 'ETHIOPIA ', 0, 'ven packages wake quickly. regu'); |
| INSERT INTO mpp3287_nation VALUES (7, 'GERMANY ', 3, 'l platelets. regular accounts x-ray: unusual, regular acco'); |
| INSERT INTO mpp3287_nation VALUES (9, 'INDONESIA ', 2, ' slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull'); |
| INSERT INTO mpp3287_nation VALUES (11, 'IRAQ ', 4, 'nic deposits boost atop the quickly final requests? quickly regula'); |
| INSERT INTO mpp3287_nation VALUES (13, 'JORDAN ', 4, 'ic deposits are blithely about the carefully regular pa'); |
| INSERT INTO mpp3287_nation VALUES (15, 'MOROCCO ', 0, 'rns. blithely bold courts among the closely regular packages use furiously bold platelets?'); |
| INSERT INTO mpp3287_nation VALUES (17, 'PERU ', 1, 'platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun'); |
| INSERT INTO mpp3287_nation VALUES (19, 'ROMANIA ', 3, 'ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account'); |
| INSERT INTO mpp3287_nation VALUES (21, 'VIETNAM ', 2, 'hely enticingly express accounts. even, final '); |
| INSERT INTO mpp3287_nation VALUES (23, 'UNITED KINGDOM ', 3, 'eans boost carefully special requests. accounts are. carefull'); |
| INSERT INTO mpp3287_nation VALUES (0, 'ALGERIA ', 0, ' haggle. carefully final deposits detect slyly agai'); |
| INSERT INTO mpp3287_nation VALUES (2, 'BRAZIL ', 1, 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '); |
| INSERT INTO mpp3287_nation VALUES (4, 'EGYPT ', 4, 'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d'); |
| INSERT INTO mpp3287_nation VALUES (6, 'FRANCE ', 3, 'refully final requests. regular, ironi'); |
| INSERT INTO mpp3287_nation VALUES (8, 'INDIA ', 2, 'ss excuses cajole slyly across the packages. deposits print aroun'); |
| INSERT INTO mpp3287_nation VALUES (10, 'IRAN ', 4, 'efully alongside of the slyly final dependencies. '); |
| INSERT INTO mpp3287_nation VALUES (12, 'JAPAN ', 2, 'ously. final, express gifts cajole a'); |
| INSERT INTO mpp3287_nation VALUES (14, 'KENYA ', 0, ' pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t'); |
| INSERT INTO mpp3287_nation VALUES (16, 'MOZAMBIQUE ', 0, 's. ironic, unusual asymptotes wake blithely r'); |
| INSERT INTO mpp3287_nation VALUES (18, 'CHINA ', 2, 'c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos'); |
| INSERT INTO mpp3287_nation VALUES (20, 'SAUDI ARABIA ', 4, 'ts. silent requests haggle. closely express packages sleep across the blithely'); |
| INSERT INTO mpp3287_nation VALUES (22, 'RUSSIA ', 3, ' requests against the platelets use never according to the quickly regular pint'); |
| INSERT INTO mpp3287_nation VALUES (24, 'UNITED STATES ', 1, 'y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be'); |
| delete from mpp3287_nation; |
| drop table mpp3287_nation; |
| drop table if exists mpp3283_nation; |
| CREATE TABLE mpp3283_NATION ( |
| N_NATIONKEY INTEGER, |
| N_NAME CHAR(25), |
| N_REGIONKEY INTEGER, |
| N_COMMENT VARCHAR(152) |
| ) |
| partition by range (n_regionkey) |
| ( |
| partition p1 start('0') end('5') exclusive |
| ); |
| -- Data for Name: nation; Type: TABLE DATA; Schema: public; Owner: bmaryada |
| INSERT INTO mpp3283_nation VALUES (1, 'ARGENTINA ', 1, 'al foxes promise slyly according to the regular accounts. bold requests alon'); |
| INSERT INTO mpp3283_nation VALUES (3, 'CANADA ', 1, 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'); |
| INSERT INTO mpp3283_nation VALUES (5, 'ETHIOPIA ', 0, 'ven packages wake quickly. regu'); |
| INSERT INTO mpp3283_nation VALUES (7, 'GERMANY ', 3, 'l platelets. regular accounts x-ray: unusual, regular acco'); |
| INSERT INTO mpp3283_nation VALUES (9, 'INDONESIA ', 2, ' slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull'); |
| INSERT INTO mpp3283_nation VALUES (11, 'IRAQ ', 4, 'nic deposits boost atop the quickly final requests? quickly regula'); |
| INSERT INTO mpp3283_nation VALUES (13, 'JORDAN ', 4, 'ic deposits are blithely about the carefully regular pa'); |
| INSERT INTO mpp3283_nation VALUES (15, 'MOROCCO ', 0, 'rns. blithely bold courts among the closely regular packages use furiously bold platelets?'); |
| INSERT INTO mpp3283_nation VALUES (17, 'PERU ', 1, 'platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun'); |
| INSERT INTO mpp3283_nation VALUES (19, 'ROMANIA ', 3, 'ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account'); |
| INSERT INTO mpp3283_nation VALUES (21, 'VIETNAM ', 2, 'hely enticingly express accounts. even, final '); |
| INSERT INTO mpp3283_nation VALUES (23, 'UNITED KINGDOM ', 3, 'eans boost carefully special requests. accounts are. carefull'); |
| INSERT INTO mpp3283_nation VALUES (0, 'ALGERIA ', 0, ' haggle. carefully final deposits detect slyly agai'); |
| INSERT INTO mpp3283_nation VALUES (2, 'BRAZIL ', 1, 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '); |
| INSERT INTO mpp3283_nation VALUES (4, 'EGYPT ', 4, 'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d'); |
| INSERT INTO mpp3283_nation VALUES (6, 'FRANCE ', 3, 'refully final requests. regular, ironi'); |
| INSERT INTO mpp3283_nation VALUES (8, 'INDIA ', 2, 'ss excuses cajole slyly across the packages. deposits print aroun'); |
| INSERT INTO mpp3283_nation VALUES (10, 'IRAN ', 4, 'efully alongside of the slyly final dependencies. '); |
| INSERT INTO mpp3283_nation VALUES (12, 'JAPAN ', 2, 'ously. final, express gifts cajole a'); |
| INSERT INTO mpp3283_nation VALUES (14, 'KENYA ', 0, ' pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t'); |
| INSERT INTO mpp3283_nation VALUES (16, 'MOZAMBIQUE ', 0, 's. ironic, unusual asymptotes wake blithely r'); |
| INSERT INTO mpp3283_nation VALUES (18, 'CHINA ', 2, 'c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos'); |
| INSERT INTO mpp3283_nation VALUES (20, 'SAUDI ARABIA ', 4, 'ts. silent requests haggle. closely express packages sleep across the blithely'); |
| INSERT INTO mpp3283_nation VALUES (22, 'RUSSIA ', 3, ' requests against the platelets use never according to the quickly regular pint'); |
| INSERT INTO mpp3283_nation VALUES (24, 'UNITED STATES ', 1, 'y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be'); |
| select count(*) from mpp3283_nation; |
| count |
| ------- |
| 25 |
| (1 row) |
| |
| copy mpp3283_nation to '/dev/null' delimiter '|'; |
| drop table mpp3283_nation; |
| -- Check that DROP TABLE still works, after adding a default partition. |
| CREATE TABLE mpp3240(a int, b int, c int) |
| partition by range (a) |
| ( partition aa start (1) end (5) every (1) ); |
| alter table mpp3240 add default partition default_part; |
| drop table mpp3240; |
| -- Check that after dropping a partition, you can create a new partition with |
| -- the same name. |
| CREATE TABLE mpp3259 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| ( partition aa start (0) end (500) every (100), default partition default_part ); |
| alter table mpp3259 drop partition default_part; |
| alter table mpp3259 add default partition default_part; |
| insert into mpp3259 (unique1) values (100001); |
| select * from mpp3259; |
| unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 |
| ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- |
| 100001 | | | | | | | | | | | | | | | |
| (1 row) |
| |
| select * from mpp3259_1_prt_default_part; |
| unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 |
| ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- |
| 100001 | | | | | | | | | | | | | | | |
| (1 row) |
| |
| drop table mpp3259; |
| -- Check that DROP PARTITION for (n) gives sensible error if there is no matching partition. |
| CREATE TABLE mpp3265 ( |
| unique1 int4, |
| unique2 int4 |
| ) partition by range (unique1) |
| ( partition aa start (0) end (500), |
| partition bb start (500) end (1000), |
| partition cc start (1000) end (1500), |
| partition dd start (1500) end (2000), |
| default partition default_part ); |
| alter table mpp3265 drop partition for (100); |
| alter table mpp3265 drop partition for (-5); |
| ERROR: FOR expression matches DEFAULT partition for specified value of relation "mpp3265" |
| HINT: FOR expression may only specify a non-default partition in this context. |
| alter table mpp3265 drop partition for (0); |
| ERROR: FOR expression matches DEFAULT partition for specified value of relation "mpp3265" |
| HINT: FOR expression may only specify a non-default partition in this context. |
| drop table mpp3265; |
| -- Test very deeply nested subpartitioning |
| CREATE TABLE MULTI_PART2(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int) |
| distributed by (a) |
| partition by range (a) |
| subpartition by range (b) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (c) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (d) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (e) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (f) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (g) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (h) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (i) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (j) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (k) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (l) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (m) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (n) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (o) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (p) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (q) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (r) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (s) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (t) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (u) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (v) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (w) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (x) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (y) subpartition template ( start (1) end (2) every (1)) |
| subpartition by range (z) subpartition template ( start (1) end (2) every (1)) |
| ( start (1) end (2) every (1)); |
| alter table multi_part2 rename to m_0000000; |
| select relid,parentrelid,isleaf,level, pg_catalog.pg_get_expr(relpartbound, oid) from pg_partition_tree('m_0000000'), pg_class where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ---------------------------------------------------------------+---------------------------------------------------------------+--------+-------+---------------------------- |
| m_0000000 | | f | 0 | |
| m_0000000_1_prt_1 | m_0000000 | f | 1 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1 | m_0000000_1_prt_1 | f | 2 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1 | m_0000000_1_prt_1_2_prt_1 | f | 3 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1 | f | 4 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1 | f | 5 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1 | f | 6 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_7_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_1 | f | 7 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_8_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_7_prt_1 | f | 8 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_9_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_8_prt_1 | f | 9 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__10_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_9_prt_1 | f | 10 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__11_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__10_prt_1 | f | 11 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__12_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__11_prt_1 | f | 12 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__13_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__12_prt_1 | f | 13 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__14_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__13_prt_1 | f | 14 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__15_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__14_prt_1 | f | 15 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__16_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__15_prt_1 | f | 16 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__17_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__16_prt_1 | f | 17 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__18_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__17_prt_1 | f | 18 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__19_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__18_prt_1 | f | 19 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__20_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__19_prt_1 | f | 20 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__21_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__20_prt_1 | f | 21 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__22_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__21_prt_1 | f | 22 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__23_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__22_prt_1 | f | 23 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__24_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__23_prt_1 | f | 24 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__25_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__24_prt_1 | f | 25 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__26_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__25_prt_1 | t | 26 | FOR VALUES FROM (1) TO (2) |
| (27 rows) |
| |
| alter table m_0000000 rename to multi_part2_0000000; |
| WARNING: renamed 6 relations, skipped 21 child partitions as old parent name is not part of partition name |
| select relid,parentrelid,isleaf,level, pg_catalog.pg_get_expr(relpartbound, oid) from pg_partition_tree('multi_part2_0000000'), pg_class where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ---------------------------------------------------------------+---------------------------------------------------------------+--------+-------+---------------------------- |
| multi_part2_0000000 | | f | 0 | |
| multi_part2_0000000_1_prt_1 | multi_part2_0000000 | f | 1 | FOR VALUES FROM (1) TO (2) |
| multi_part2_0000000_1_prt_1_2_prt_1 | multi_part2_0000000_1_prt_1 | f | 2 | FOR VALUES FROM (1) TO (2) |
| multi_part2_0000000_1_prt_1_2_prt_1_3_prt_1 | multi_part2_0000000_1_prt_1_2_prt_1 | f | 3 | FOR VALUES FROM (1) TO (2) |
| multi_part2_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1 | multi_part2_0000000_1_prt_1_2_prt_1_3_prt_1 | f | 4 | FOR VALUES FROM (1) TO (2) |
| multi_part2_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1 | multi_part2_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1 | f | 5 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_1 | multi_part2_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1 | f | 6 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_7_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_1 | f | 7 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_8_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_7_prt_1 | f | 8 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_9_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_8_prt_1 | f | 9 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__10_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_p_9_prt_1 | f | 10 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__11_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__10_prt_1 | f | 11 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__12_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__11_prt_1 | f | 12 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__13_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__12_prt_1 | f | 13 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__14_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__13_prt_1 | f | 14 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__15_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__14_prt_1 | f | 15 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__16_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__15_prt_1 | f | 16 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__17_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__16_prt_1 | f | 17 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__18_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__17_prt_1 | f | 18 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__19_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__18_prt_1 | f | 19 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__20_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__19_prt_1 | f | 20 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__21_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__20_prt_1 | f | 21 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__22_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__21_prt_1 | f | 22 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__23_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__22_prt_1 | f | 23 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__24_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__23_prt_1 | f | 24 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__25_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__24_prt_1 | f | 25 | FOR VALUES FROM (1) TO (2) |
| m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__26_prt_1 | m_0000000_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6__25_prt_1 | t | 26 | FOR VALUES FROM (1) TO (2) |
| (27 rows) |
| |
| alter table multi_part2_0000000 rename partition for (1) to a123456789a123456789a123456789a123456789a123456789a123456789; |
| ERROR: name "a123456789a123456789a123456789a123456789a123456789a123456789" for child partition is too long |
| drop table multi_part2_0000000; |
| drop TABLE IF EXISTS INT_P1A CASCADE; |
| CREATE TABLE INT_P1A ( num1 INTEGER NOT NULL, |
| num2 INTEGER NOT NULL, |
| average DECIMAL(15,2) NOT NULL, |
| date1 DATE NOT NULL, |
| date2 DATE NOT NULL, |
| message VARCHAR(44) NOT NULL ) |
| DISTRIBUTED BY (num1) |
| PARTITION BY RANGE(date1) |
| ( |
| PARTITION y1992 END('1992-12-31'), |
| PARTITION y1993 END('1993-12-31'), |
| PARTITION y1994 END('1994-12-31'), |
| PARTITION y1995 END('1995-12-31'), |
| PARTITION y1996 START('1996-01-01'), |
| PARTITION y1997 START('1997-01-01') END('1997-12-31') |
| ); |
| drop TABLE IF EXISTS INT_P1B CASCADE; |
| CREATE TABLE INT_P1B ( num1 INTEGER NOT NULL, |
| num2 INTEGER NOT NULL, |
| average DECIMAL(15,2) NOT NULL, |
| date1 DATE NOT NULL, |
| date2 DATE NOT NULL, |
| message VARCHAR(44) NOT NULL ) |
| DISTRIBUTED BY (num1) |
| PARTITION BY RANGE(date1) |
| ( |
| PARTITION y1992 END('1992-12-31'), |
| PARTITION y1993 END('1993-12-31'), |
| PARTITION y1994 END('1994-12-31'), |
| PARTITION y1995, |
| PARTITION y1996 START('1996-01-01'), |
| PARTITION y1997 start('1997-01-01') END('1997-12-31') |
| ); |
| ERROR: missing boundary specification in partition "y1995" of type RANGE |
| LINE 13: PARTITION y1995, |
| ^ |
| DROP TABLE INT_P1A; |
| DROP TABLE INT_P1B; |
| ERROR: table "int_p1b" does not exist |
| CREATE TABLE mpp2564_transactions (obligation_trans_date date, cust_no integer, company_no character(3), obligation_trans_no character varying(25), item_no integer, orig_oblig_trans_no character varying(25), split_ind character(1), qty_sold integer, net_wgt_lbs numeric(10,5), unit_price numeric(10,2), ext_price numeric(10,2), catch_wgt_ind character(1), qty_sold_case_equivalent numeric(10,5), marketing_assoc_id character varying(10), cost_of_goods_sold numeric(10,2), adj_cost_good_sold numeric(10,2), trade_sales_ind character(1), create_date timestamp without time zone) DISTRIBUTED RANDOMLY |
| PARTITION BY RANGE (obligation_trans_date) |
| SUBPARTITION BY LIST (company_no) subpartition template |
| ( subpartition p1 values ('047'), |
| subpartition p2 values ('002'), |
| subpartition p3 values ('056'), |
| subpartition p4 values ('022') |
| ) |
| ( start ('2005-06-01') end ('2006-05-01') every (INTERVAL '1 month') ); |
| \d mpp2564_transactions* |
| Partitioned table "partition_ddl2.mpp2564_transactions" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition key: RANGE (obligation_trans_date) |
| Number of partitions: 11 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('06-01-2005') TO ('07-01-2005') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_10" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('03-01-2006') TO ('04-01-2006') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_10_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_10 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_10_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_10 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_10_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_10 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_10_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_10 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_11" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('04-01-2006') TO ('05-01-2006') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_11_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_11 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_11_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_11 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_11_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_11 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_11_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_11 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_1_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_1 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_1_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_1 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_1_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_1 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_1_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_1 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('07-01-2005') TO ('08-01-2005') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_2_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_2 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_2_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_2 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_2_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_2 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_2_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_2 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('08-01-2005') TO ('09-01-2005') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_3_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_3 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_3_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_3 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_3_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_3 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_3_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_3 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('09-01-2005') TO ('10-01-2005') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_4_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_4 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_4_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_4 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_4_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_4 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_4_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_4 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_5" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('10-01-2005') TO ('11-01-2005') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_5_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_5 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_5_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_5 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_5_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_5 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_5_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_5 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_6" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('11-01-2005') TO ('12-01-2005') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_6_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_6 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_6_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_6 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_6_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_6 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_6_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_6 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_7" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('12-01-2005') TO ('01-01-2006') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_7_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_7 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_7_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_7 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_7_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_7 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_7_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_7 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_8" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('01-01-2006') TO ('02-01-2006') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_8_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_8 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_8_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_8 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_8_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_8 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_8_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_8 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| Partitioned table "partition_ddl2.mpp2564_transactions_1_prt_9" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions FOR VALUES FROM ('02-01-2006') TO ('03-01-2006') |
| Partition key: LIST (company_no) |
| Number of partitions: 4 (Use \d+ to list them.) |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_9_2_prt_p1" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_9 FOR VALUES IN ('047') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_9_2_prt_p2" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_9 FOR VALUES IN ('002') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_9_2_prt_p3" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_9 FOR VALUES IN ('056') |
| Distributed randomly |
| |
| Table "partition_ddl2.mpp2564_transactions_1_prt_9_2_prt_p4" |
| Column | Type | Collation | Nullable | Default |
| --------------------------+-----------------------------+-----------+----------+--------- |
| obligation_trans_date | date | | | |
| cust_no | integer | | | |
| company_no | character(3) | | | |
| obligation_trans_no | character varying(25) | | | |
| item_no | integer | | | |
| orig_oblig_trans_no | character varying(25) | | | |
| split_ind | character(1) | | | |
| qty_sold | integer | | | |
| net_wgt_lbs | numeric(10,5) | | | |
| unit_price | numeric(10,2) | | | |
| ext_price | numeric(10,2) | | | |
| catch_wgt_ind | character(1) | | | |
| qty_sold_case_equivalent | numeric(10,5) | | | |
| marketing_assoc_id | character varying(10) | | | |
| cost_of_goods_sold | numeric(10,2) | | | |
| adj_cost_good_sold | numeric(10,2) | | | |
| trade_sales_ind | character(1) | | | |
| create_date | timestamp without time zone | | | |
| Partition of: mpp2564_transactions_1_prt_9 FOR VALUES IN ('022') |
| Distributed randomly |
| |
| -- Check that DEFAULT can be used as partition's name with ALTER TABLE ADD PARTITION. (MPP-3363) |
| CREATE TABLE mpp3363(a int, b int, c int) |
| partition by range (a) |
| ( partition aa start (1) end (3) every (1) ); |
| alter table mpp3363 add default partition default; |
| ERROR: syntax error at or near "default" |
| LINE 1: alter table mpp3363 add default partition default; |
| ^ |
| drop table mpp3363; |
| -- start_matchsubs |
| -- #Daylight savings for Pacific Time |
| -- m/00:00-0\d/ |
| -- s/00:00-0\d/00:00-0x/g |
| -- end_matchsubs |
| -- 1 level partition |
| CREATE TABLE mpp3059(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int) |
| partition by range (a) |
| ( partition aa start (1) end (5) every (1) ); |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| --------------------+-------------+--------+-------+---------------------------- |
| mpp3059 | | f | 0 | |
| mpp3059_1_prt_aa_1 | mpp3059 | t | 1 | FOR VALUES FROM (1) TO (2) |
| mpp3059_1_prt_aa_2 | mpp3059 | t | 1 | FOR VALUES FROM (2) TO (3) |
| mpp3059_1_prt_aa_3 | mpp3059 | t | 1 | FOR VALUES FROM (3) TO (4) |
| mpp3059_1_prt_aa_4 | mpp3059 | t | 1 | FOR VALUES FROM (4) TO (5) |
| (5 rows) |
| |
| alter table mpp3059 rename to mpp3059_rename; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059_rename') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ---------------------------+----------------+--------+-------+---------------------------- |
| mpp3059_rename | | f | 0 | |
| mpp3059_rename_1_prt_aa_1 | mpp3059_rename | t | 1 | FOR VALUES FROM (1) TO (2) |
| mpp3059_rename_1_prt_aa_2 | mpp3059_rename | t | 1 | FOR VALUES FROM (2) TO (3) |
| mpp3059_rename_1_prt_aa_3 | mpp3059_rename | t | 1 | FOR VALUES FROM (3) TO (4) |
| mpp3059_rename_1_prt_aa_4 | mpp3059_rename | t | 1 | FOR VALUES FROM (4) TO (5) |
| (5 rows) |
| |
| alter table mpp3059_rename rename to mpp3059; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| --------------------+-------------+--------+-------+---------------------------- |
| mpp3059 | | f | 0 | |
| mpp3059_1_prt_aa_1 | mpp3059 | t | 1 | FOR VALUES FROM (1) TO (2) |
| mpp3059_1_prt_aa_2 | mpp3059 | t | 1 | FOR VALUES FROM (2) TO (3) |
| mpp3059_1_prt_aa_3 | mpp3059 | t | 1 | FOR VALUES FROM (3) TO (4) |
| mpp3059_1_prt_aa_4 | mpp3059 | t | 1 | FOR VALUES FROM (4) TO (5) |
| (5 rows) |
| |
| -- 2 level partition |
| CREATE TABLE mpp3059a ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| subpartition by range (unique2) subpartition template ( start (0) end (500) every (100) ) |
| ( start (0) end (500) every (100)); |
| alter table mpp3059a rename to mpp3059a_rename; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059a_rename') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ---------------------------------+-------------------------+--------+-------+-------------------------------- |
| mpp3059a_rename | | f | 0 | |
| mpp3059a_rename_1_prt_1 | mpp3059a_rename | f | 1 | FOR VALUES FROM (0) TO (100) |
| mpp3059a_rename_1_prt_2 | mpp3059a_rename | f | 1 | FOR VALUES FROM (100) TO (200) |
| mpp3059a_rename_1_prt_3 | mpp3059a_rename | f | 1 | FOR VALUES FROM (200) TO (300) |
| mpp3059a_rename_1_prt_4 | mpp3059a_rename | f | 1 | FOR VALUES FROM (300) TO (400) |
| mpp3059a_rename_1_prt_5 | mpp3059a_rename | f | 1 | FOR VALUES FROM (400) TO (500) |
| mpp3059a_rename_1_prt_1_2_prt_1 | mpp3059a_rename_1_prt_1 | t | 2 | FOR VALUES FROM (0) TO (100) |
| mpp3059a_rename_1_prt_1_2_prt_2 | mpp3059a_rename_1_prt_1 | t | 2 | FOR VALUES FROM (100) TO (200) |
| mpp3059a_rename_1_prt_1_2_prt_3 | mpp3059a_rename_1_prt_1 | t | 2 | FOR VALUES FROM (200) TO (300) |
| mpp3059a_rename_1_prt_1_2_prt_4 | mpp3059a_rename_1_prt_1 | t | 2 | FOR VALUES FROM (300) TO (400) |
| mpp3059a_rename_1_prt_1_2_prt_5 | mpp3059a_rename_1_prt_1 | t | 2 | FOR VALUES FROM (400) TO (500) |
| mpp3059a_rename_1_prt_2_2_prt_1 | mpp3059a_rename_1_prt_2 | t | 2 | FOR VALUES FROM (0) TO (100) |
| mpp3059a_rename_1_prt_2_2_prt_2 | mpp3059a_rename_1_prt_2 | t | 2 | FOR VALUES FROM (100) TO (200) |
| mpp3059a_rename_1_prt_2_2_prt_3 | mpp3059a_rename_1_prt_2 | t | 2 | FOR VALUES FROM (200) TO (300) |
| mpp3059a_rename_1_prt_2_2_prt_4 | mpp3059a_rename_1_prt_2 | t | 2 | FOR VALUES FROM (300) TO (400) |
| mpp3059a_rename_1_prt_2_2_prt_5 | mpp3059a_rename_1_prt_2 | t | 2 | FOR VALUES FROM (400) TO (500) |
| mpp3059a_rename_1_prt_3_2_prt_1 | mpp3059a_rename_1_prt_3 | t | 2 | FOR VALUES FROM (0) TO (100) |
| mpp3059a_rename_1_prt_3_2_prt_2 | mpp3059a_rename_1_prt_3 | t | 2 | FOR VALUES FROM (100) TO (200) |
| mpp3059a_rename_1_prt_3_2_prt_3 | mpp3059a_rename_1_prt_3 | t | 2 | FOR VALUES FROM (200) TO (300) |
| mpp3059a_rename_1_prt_3_2_prt_4 | mpp3059a_rename_1_prt_3 | t | 2 | FOR VALUES FROM (300) TO (400) |
| mpp3059a_rename_1_prt_3_2_prt_5 | mpp3059a_rename_1_prt_3 | t | 2 | FOR VALUES FROM (400) TO (500) |
| mpp3059a_rename_1_prt_4_2_prt_1 | mpp3059a_rename_1_prt_4 | t | 2 | FOR VALUES FROM (0) TO (100) |
| mpp3059a_rename_1_prt_4_2_prt_2 | mpp3059a_rename_1_prt_4 | t | 2 | FOR VALUES FROM (100) TO (200) |
| mpp3059a_rename_1_prt_4_2_prt_3 | mpp3059a_rename_1_prt_4 | t | 2 | FOR VALUES FROM (200) TO (300) |
| mpp3059a_rename_1_prt_4_2_prt_4 | mpp3059a_rename_1_prt_4 | t | 2 | FOR VALUES FROM (300) TO (400) |
| mpp3059a_rename_1_prt_4_2_prt_5 | mpp3059a_rename_1_prt_4 | t | 2 | FOR VALUES FROM (400) TO (500) |
| mpp3059a_rename_1_prt_5_2_prt_1 | mpp3059a_rename_1_prt_5 | t | 2 | FOR VALUES FROM (0) TO (100) |
| mpp3059a_rename_1_prt_5_2_prt_2 | mpp3059a_rename_1_prt_5 | t | 2 | FOR VALUES FROM (100) TO (200) |
| mpp3059a_rename_1_prt_5_2_prt_3 | mpp3059a_rename_1_prt_5 | t | 2 | FOR VALUES FROM (200) TO (300) |
| mpp3059a_rename_1_prt_5_2_prt_4 | mpp3059a_rename_1_prt_5 | t | 2 | FOR VALUES FROM (300) TO (400) |
| mpp3059a_rename_1_prt_5_2_prt_5 | mpp3059a_rename_1_prt_5 | t | 2 | FOR VALUES FROM (400) TO (500) |
| (31 rows) |
| |
| create table mpp3216a (id int, rank int, year int, gender char(1), count int) distributed by (id); |
| create table mpp3216 (like mpp3216a) partition by range (year) ( start (2001) end (2006) every (1)); |
| alter table mpp3216 rename to mpp3216_rename; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3216_rename') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ------------------------+----------------+--------+-------+---------------------------------- |
| mpp3216_rename | | f | 0 | |
| mpp3216_rename_1_prt_1 | mpp3216_rename | t | 1 | FOR VALUES FROM (2001) TO (2002) |
| mpp3216_rename_1_prt_2 | mpp3216_rename | t | 1 | FOR VALUES FROM (2002) TO (2003) |
| mpp3216_rename_1_prt_3 | mpp3216_rename | t | 1 | FOR VALUES FROM (2003) TO (2004) |
| mpp3216_rename_1_prt_4 | mpp3216_rename | t | 1 | FOR VALUES FROM (2004) TO (2005) |
| mpp3216_rename_1_prt_5 | mpp3216_rename | t | 1 | FOR VALUES FROM (2005) TO (2006) |
| (6 rows) |
| |
| CREATE TABLE mpp3059b (f1 time(2) with time zone, f2 char(4)) |
| partition by list (f2) |
| ( partition pst values ('PST'), |
| partition est values ('EST') |
| ); |
| alter table mpp3059b rename partition pst to "pacific time"; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059b') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| -------------------------------+-------------+--------+-------+------------------------ |
| mpp3059b | | f | 0 | |
| "mpp3059b_1_prt_pacific time" | mpp3059b | t | 1 | FOR VALUES IN ('PST ') |
| mpp3059b_1_prt_est | mpp3059b | t | 1 | FOR VALUES IN ('EST ') |
| (3 rows) |
| |
| CREATE TABLE mpp3059c (f1 time(2) with time zone, f2 char(4)) |
| partition by list (f2) |
| subpartition by range (f1) |
| subpartition template ( |
| start (time '00:00'), |
| start (time '01:00') |
| ) |
| ( partition pst values ('PST'), |
| partition est values ('EST') |
| ); |
| alter table mpp3059c rename partition pst to pacific; |
| alter table mpp3059c rename partition est to "Eastern Time"; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059c') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ---------------------------------------+-------------------------------+--------+-------+---------------------------------------------------- |
| mpp3059c | | f | 0 | |
| mpp3059c_1_prt_pacific | mpp3059c | f | 1 | FOR VALUES IN ('PST ') |
| "mpp3059c_1_prt_Eastern Time" | mpp3059c | f | 1 | FOR VALUES IN ('EST ') |
| mpp3059c_1_prt_pacific_2_prt_1 | mpp3059c_1_prt_pacific | t | 2 | FOR VALUES FROM ('00:00:00-07') TO ('01:00:00-07') |
| mpp3059c_1_prt_pacific_2_prt_2 | mpp3059c_1_prt_pacific | t | 2 | FOR VALUES FROM ('01:00:00-07') TO (MAXVALUE) |
| "mpp3059c_1_prt_Eastern Time_2_prt_1" | "mpp3059c_1_prt_Eastern Time" | t | 2 | FOR VALUES FROM ('00:00:00-07') TO ('01:00:00-07') |
| "mpp3059c_1_prt_Eastern Time_2_prt_2" | "mpp3059c_1_prt_Eastern Time" | t | 2 | FOR VALUES FROM ('01:00:00-07') TO (MAXVALUE) |
| (7 rows) |
| |
| CREATE TABLE mpp3059d (f1 time(2) with time zone, f2 char(4), f3 varchar(10)) |
| partition by list (f2) |
| subpartition by list (f3) |
| subpartition template ( |
| subpartition male values ('Male','M'), |
| subpartition female values ('Female','F') |
| ) |
| ( partition pst values ('PST'), |
| partition est values ('EST') |
| ); |
| alter table mpp3059d rename partition pst to pacific; |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3059d') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| -------------------------------------+------------------------+--------+-------+------------------------------- |
| mpp3059d | | f | 0 | |
| mpp3059d_1_prt_pacific | mpp3059d | f | 1 | FOR VALUES IN ('PST ') |
| mpp3059d_1_prt_est | mpp3059d | f | 1 | FOR VALUES IN ('EST ') |
| mpp3059d_1_prt_pacific_2_prt_male | mpp3059d_1_prt_pacific | t | 2 | FOR VALUES IN ('Male', 'M') |
| mpp3059d_1_prt_pacific_2_prt_female | mpp3059d_1_prt_pacific | t | 2 | FOR VALUES IN ('Female', 'F') |
| mpp3059d_1_prt_est_2_prt_male | mpp3059d_1_prt_est | t | 2 | FOR VALUES IN ('Male', 'M') |
| mpp3059d_1_prt_est_2_prt_female | mpp3059d_1_prt_est | t | 2 | FOR VALUES IN ('Female', 'F') |
| (7 rows) |
| |
| create table test_khush (a integer, b date, c varchar(30)) |
| partition by range(b) |
| ( start ('2008-01-01') end ('2008-04-01') ); |
| Insert into test_khush values (1, '01-Jan-2008', 'abc'); |
| Insert into test_khush values (2, '01-Feb-2008', 'jhg'); |
| Insert into test_khush values (3, '01-Mar-2008', 'xyz'); |
| alter table test_khush add column d varchar(20); |
| insert into test_khush values (5,'05-Feb-2008','hgjhg','Test'); |
| drop table test_khush; |
| CREATE TABLE mpp3244(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int) |
| partition by range (a) |
| subpartition by range (b) subpartition template ( start (1) end (10) every (1))( partition aa start (1) end (10) every (1) ); |
| CREATE TABLE mpp3244a(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int) |
| partition by range (a) |
| subpartition by range (b) subpartition template ( start (1) end (10) every (1))( partition aa start (1) end (11) every (1) ); |
| insert into mpp3244 (a,b) values (10,1); |
| ERROR: no partition of relation "mpp3244" found for row (seg2 127.0.1.1:7004 pid=1450) |
| DETAIL: Partition key of the failing row contains (a) = (10). |
| insert into mpp3244a (a,b) values (10,1); |
| alter table mpp3244 add partition bb end (11); |
| insert into mpp3244 (a,b) values (10,1); |
| select count(*) from mpp3244; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| select count(*) from mpp3244a; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| CREATE TABLE mpp3256 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| subpartition by range (unique2) subpartition template ( start (0) end (1000) every (100) ) |
| ( start (0) end (1000) every (100)); |
| alter table mpp3256 add default partition default_part; |
| alter table mpp3256 drop partition default_part; |
| alter table mpp3256 add default partition default_part; |
| alter table mpp3256 drop partition default_part; |
| alter table mpp3256 add default partition default_part; |
| alter table mpp3256 drop partition default_part; |
| alter table mpp3256 add default partition default_part; |
| alter table mpp3256 drop partition default_part; |
| alter table mpp3256 add default partition default_part; |
| alter table mpp3256 drop partition default_part; |
| -- Examples from Documentation |
| CREATE TABLE mpp3377_sales (trans_id int, date date, amount decimal(9,2), region text) |
| DISTRIBUTED BY (trans_id) |
| PARTITION BY RANGE (date) |
| SUBPARTITION BY LIST (region) |
| SUBPARTITION TEMPLATE |
| ( SUBPARTITION usa VALUES ('usa'), |
| SUBPARTITION asia VALUES ('asia'), |
| SUBPARTITION europe VALUES ('europe') ) |
| ( START (date '2008-01-01') INCLUSIVE |
| END (date '2009-01-01') EXCLUSIVE |
| EVERY (INTERVAL '1 month') ); |
| -- Note from Jeff: MPP-3377 |
| -- since you already defined a template for your subpartition, you cannot specify it again in ADD (this now matches the behavior of CREATE TABLE). |
| ALTER TABLE mpp3377_sales ADD PARTITION |
| START (date '2009-02-01') INCLUSIVE |
| END (date '2009-03-01') EXCLUSIVE |
| ( SUBPARTITION usa VALUES ('usa'), |
| SUBPARTITION asia VALUES ('asia'), |
| SUBPARTITION europe VALUES ('europe') ); |
| ERROR: subpartition configuration conflicts with subpartition template |
| LINE 4: ( SUBPARTITION usa VALUES ('usa'), |
| ^ |
| -- This is the new way |
| ALTER TABLE mpp3377_sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END (date '2009-03-01') EXCLUSIVE; |
| CREATE TABLE mpp3250 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| subpartition by range (unique2) subpartition template ( start (0) end (1000) every (100) ) |
| ( start (0) end (1000) every (100)); |
| alter table mpp3250 add default partition default_part; |
| copy mpp3250 from '@abs_srcdir@/data/onek.data'; |
| CREATE TABLE mpp3375 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ); |
| copy mpp3375 from '@abs_srcdir@/data/onek.data'; |
| CREATE TABLE mpp3375a ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| subpartition by range (unique2) subpartition template ( start (0) end (1000) every (100) ) |
| ( start (0) end (1000) every (100)); |
| insert into mpp3375a select * from mpp3375; |
| alter table mpp3375a add default partition default_part; |
| alter table mpp3375a drop partition for (0); |
| alter table mpp3375a drop partition default_part; |
| -- Check that a negative number can be used in START, without quotes. |
| CREATE TABLE mpp3241(a int, b int, c int) |
| partition by range (a) |
| ( partition aa start (1) end (5) every (1) ); |
| alter table mpp3241 add partition zz start (-1) end (0); |
| DROP TABLE mpp3241; |
| CREATE TABLE mpp3438 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by list (unique1) |
| subpartition by list (unique2) |
| ( |
| partition aa values (1,2,3,4,5,6,7,8,9,10) (subpartition cc values (1,2,3), subpartition dd values (4,5,6) ), |
| partition bb values (11,12,13,14,15,16,17,18,19,20) (subpartition cc values (1,2,3), subpartition dd values (4,5,6) ) |
| ); |
| -- This should fail |
| alter table mpp3438 add default partition default_part; |
| ERROR: no partitions specified at depth 2 |
| -- This should be the correct way |
| alter table mpp3438 add default partition default_part (default subpartition def2); |
| alter table mpp3438 alter partition aa add default partition def3; |
| \d mpp3438* |
| Partitioned table "partition_ddl2.mpp3438" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition key: LIST (unique1) |
| Number of partitions: 3 (Use \d+ to list them.) |
| Distributed by: (unique1) |
| |
| Partitioned table "partition_ddl2.mpp3438_1_prt_aa" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438 FOR VALUES IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) |
| Partition key: LIST (unique2) |
| Number of partitions: 3 (Use \d+ to list them.) |
| Distributed by: (unique1) |
| |
| Table "partition_ddl2.mpp3438_1_prt_aa_2_prt_cc" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438_1_prt_aa FOR VALUES IN (1, 2, 3) |
| Distributed by: (unique1) |
| |
| Table "partition_ddl2.mpp3438_1_prt_aa_2_prt_dd" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438_1_prt_aa FOR VALUES IN (4, 5, 6) |
| Distributed by: (unique1) |
| |
| Table "partition_ddl2.mpp3438_1_prt_aa_2_prt_def3" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438_1_prt_aa DEFAULT |
| Distributed by: (unique1) |
| |
| Partitioned table "partition_ddl2.mpp3438_1_prt_bb" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438 FOR VALUES IN (11, 12, 13, 14, 15, 16, 17, 18, 19, 20) |
| Partition key: LIST (unique2) |
| Number of partitions: 2 (Use \d+ to list them.) |
| Distributed by: (unique1) |
| |
| Table "partition_ddl2.mpp3438_1_prt_bb_2_prt_cc" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438_1_prt_bb FOR VALUES IN (1, 2, 3) |
| Distributed by: (unique1) |
| |
| Table "partition_ddl2.mpp3438_1_prt_bb_2_prt_dd" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438_1_prt_bb FOR VALUES IN (4, 5, 6) |
| Distributed by: (unique1) |
| |
| Partitioned table "partition_ddl2.mpp3438_1_prt_default_part" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438 DEFAULT |
| Partition key: LIST (unique2) |
| Number of partitions: 1 (Use \d+ to list them.) |
| Distributed by: (unique1) |
| |
| Table "partition_ddl2.mpp3438_1_prt_default_part_2_prt_def2" |
| Column | Type | Collation | Nullable | Default |
| -------------+---------+-----------+----------+--------- |
| unique1 | integer | | | |
| unique2 | integer | | | |
| two | integer | | | |
| four | integer | | | |
| ten | integer | | | |
| twenty | integer | | | |
| hundred | integer | | | |
| thousand | integer | | | |
| twothousand | integer | | | |
| fivethous | integer | | | |
| tenthous | integer | | | |
| odd | integer | | | |
| even | integer | | | |
| stringu1 | name | | | |
| stringu2 | name | | | |
| string4 | name | | | |
| Partition of: mpp3438_1_prt_default_part DEFAULT |
| Distributed by: (unique1) |
| |
| drop table mpp3438; |
| CREATE TABLE mpp3261 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ); |
| copy mpp3261 from '@abs_srcdir@/data/onek.data'; |
| CREATE TABLE mpp3261_part ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| ( partition aa start (0) end (1000) every (100), default partition default_part ); |
| alter table mpp3261_part drop partition for (0); |
| alter table mpp3261_part drop partition for (100); |
| alter table mpp3261_part drop partition for (200); |
| alter table mpp3261_part drop partition for (300); |
| alter table mpp3261_part drop partition for (400); |
| alter table mpp3261_part drop partition for (500); |
| alter table mpp3261_part drop partition for (600); |
| alter table mpp3261_part drop partition for (700); |
| alter table mpp3261_part drop partition for (800); |
| alter table mpp3261_part drop partition for (900); |
| -- Shouldn't take a long time to insert |
| insert into mpp3261_part select * from mpp3261; |
| drop table mpp3261; |
| drop table mpp3261_part; |
| CREATE TABLE mpp3079(q1 int8, q2 int8) |
| partition by range (q1) |
| (start (1) end (10) every (1)); |
| CREATE TABLE mpp3079a(q1 int2, q2 int2) |
| partition by range (q1) |
| (start (1) end (10) every (1)); |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3079') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| -----------------+-------------+--------+-------+--------------------------------- |
| mpp3079 | | f | 0 | |
| mpp3079_1_prt_1 | mpp3079 | t | 1 | FOR VALUES FROM ('1') TO ('2') |
| mpp3079_1_prt_2 | mpp3079 | t | 1 | FOR VALUES FROM ('2') TO ('3') |
| mpp3079_1_prt_3 | mpp3079 | t | 1 | FOR VALUES FROM ('3') TO ('4') |
| mpp3079_1_prt_4 | mpp3079 | t | 1 | FOR VALUES FROM ('4') TO ('5') |
| mpp3079_1_prt_5 | mpp3079 | t | 1 | FOR VALUES FROM ('5') TO ('6') |
| mpp3079_1_prt_6 | mpp3079 | t | 1 | FOR VALUES FROM ('6') TO ('7') |
| mpp3079_1_prt_7 | mpp3079 | t | 1 | FOR VALUES FROM ('7') TO ('8') |
| mpp3079_1_prt_8 | mpp3079 | t | 1 | FOR VALUES FROM ('8') TO ('9') |
| mpp3079_1_prt_9 | mpp3079 | t | 1 | FOR VALUES FROM ('9') TO ('10') |
| (10 rows) |
| |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3079a') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ------------------+-------------+--------+-------+--------------------------------- |
| mpp3079a | | f | 0 | |
| mpp3079a_1_prt_1 | mpp3079a | t | 1 | FOR VALUES FROM ('1') TO ('2') |
| mpp3079a_1_prt_2 | mpp3079a | t | 1 | FOR VALUES FROM ('2') TO ('3') |
| mpp3079a_1_prt_3 | mpp3079a | t | 1 | FOR VALUES FROM ('3') TO ('4') |
| mpp3079a_1_prt_4 | mpp3079a | t | 1 | FOR VALUES FROM ('4') TO ('5') |
| mpp3079a_1_prt_5 | mpp3079a | t | 1 | FOR VALUES FROM ('5') TO ('6') |
| mpp3079a_1_prt_6 | mpp3079a | t | 1 | FOR VALUES FROM ('6') TO ('7') |
| mpp3079a_1_prt_7 | mpp3079a | t | 1 | FOR VALUES FROM ('7') TO ('8') |
| mpp3079a_1_prt_8 | mpp3079a | t | 1 | FOR VALUES FROM ('8') TO ('9') |
| mpp3079a_1_prt_9 | mpp3079a | t | 1 | FOR VALUES FROM ('9') TO ('10') |
| (10 rows) |
| |
| drop table mpp3079; |
| drop table mpp3079a; |
| CREATE TABLE mpp3242(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int) |
| partition by range (a) |
| ( partition aa start (1) end (10) every (1) ); |
| alter table mpp3242 add default partition default_part; |
| insert into mpp3242 values(-1); |
| -- Needs to use ALTER SPLIT instead of ADD as value exists in default |
| -- partition corresponding to new partition. It's not |
| -- automatic. |
| alter table mpp3242 add partition zz start ('-1') end (0); |
| ERROR: updated partition constraint for default partition "mpp3242_1_prt_default_part" would be violated by some row (seg2 127.0.1.1:7004 pid=10757) |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3242') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ----------------------------+-------------+--------+-------+----------------------------- |
| mpp3242 | | f | 0 | |
| mpp3242_1_prt_aa_1 | mpp3242 | t | 1 | FOR VALUES FROM (1) TO (2) |
| mpp3242_1_prt_aa_2 | mpp3242 | t | 1 | FOR VALUES FROM (2) TO (3) |
| mpp3242_1_prt_aa_3 | mpp3242 | t | 1 | FOR VALUES FROM (3) TO (4) |
| mpp3242_1_prt_aa_4 | mpp3242 | t | 1 | FOR VALUES FROM (4) TO (5) |
| mpp3242_1_prt_aa_5 | mpp3242 | t | 1 | FOR VALUES FROM (5) TO (6) |
| mpp3242_1_prt_aa_6 | mpp3242 | t | 1 | FOR VALUES FROM (6) TO (7) |
| mpp3242_1_prt_aa_7 | mpp3242 | t | 1 | FOR VALUES FROM (7) TO (8) |
| mpp3242_1_prt_aa_8 | mpp3242 | t | 1 | FOR VALUES FROM (8) TO (9) |
| mpp3242_1_prt_aa_9 | mpp3242 | t | 1 | FOR VALUES FROM (9) TO (10) |
| mpp3242_1_prt_default_part | mpp3242 | t | 1 | DEFAULT |
| (11 rows) |
| |
| drop table mpp3242; |
| CREATE TABLE mpp3523 (f1 time(2) with time zone) |
| partition by range (f1) |
| ( |
| partition "Los Angeles" start (time with time zone '00:00 PST') end (time with time zone '23:00 PST') EVERY (INTERVAL '1 hour'), |
| partition "New York" start (time with time zone '00:00 EST') end (time with time zone '23:00 EST') EVERY (INTERVAL '1 hour') |
| ); |
| ERROR: partition "mpp3523_1_prt_New York_4" would overlap partition "mpp3523_1_prt_Los Angeles_1" |
| -- Tries to truncate first, but the partition name is still too long, so ERROR |
| alter table mpp3523 rename partition "Los Angeles_1" to "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890"; |
| ERROR: relation "mpp3523" does not exist |
| -- Truncates the table name to mpp3523_0000000000111111111122222222223333333333444444444455555 |
| CREATE TABLE mpp3523_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999 (f1 time(2) with time zone) |
| partition by range (f1) |
| ( |
| partition "Los Angeles" start (time with time zone '00:00 PST') end (time with time zone '23:00 PST') EVERY (INTERVAL '1 hour'), |
| partition "New York" start (time with time zone '00:00 EST') end (time with time zone '23:00 EST') EVERY (INTERVAL '1 hour') |
| ); |
| ERROR: partition "mpp3523_00000000001111111111222222222233333333_1_prt_New York_4" would overlap partition "mpp3523_00000000001111111111222222222233333_1_prt_Los Angeles_1" |
| -- Truncates the table name to mpp3523_0000000000111111111122222222223333333333444444444455555, but partition name is too long, so ERROR |
| alter table mpp3523_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999 rename partition "Los Angeles_1" to "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890"; |
| ERROR: relation "mpp3523_0000000000111111111122222222223333333333444444444455555" does not exist |
| -- Truncates the table name to mpp3523_0000000000111111111122222222223333333333444444444455555, and partition name is safe, so renamed |
| alter table mpp3523_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999 rename partition "Los Angeles_1" to "LA1"; |
| ERROR: relation "mpp3523_0000000000111111111122222222223333333333444444444455555" does not exist |
| -- Use the actual table name |
| alter table mpp3523_0000000000111111111122222222223333333333444444444455555 rename partition "Los Angeles_2" to "LA2"; |
| ERROR: relation "mpp3523_0000000000111111111122222222223333333333444444444455555" does not exist |
| drop table mpp3523; |
| ERROR: table "mpp3523" does not exist |
| -- Truncates the table name to mpp3523_0000000000111111111122222222223333333333444444444455555 |
| drop table mpp3523_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999; |
| ERROR: table "mpp3523_0000000000111111111122222222223333333333444444444455555" does not exist |
| CREATE TABLE mpp3260 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| ( partition aa start (0) end (1000) every (100), default partition default_part ); |
| alter table mpp3260 drop partition for (0); |
| alter table mpp3260 drop partition for (100); |
| alter table mpp3260 drop partition for (200); |
| alter table mpp3260 drop partition for (300); |
| alter table mpp3260 drop partition for (400); |
| alter table mpp3260 drop partition for (500); |
| alter table mpp3260 drop partition for (600); |
| alter table mpp3260 drop partition for (700); |
| alter table mpp3260 drop partition for (800); |
| alter table mpp3260 drop partition for (900); |
| insert into mpp3260 (unique1) values (1); |
| CREATE TABLE mpp3260a ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) partition by range (unique1) |
| subpartition by range (unique2) subpartition template ( start (0) end (1000) every (100) ) |
| ( start (0) end (1000) every (100)); |
| alter table mpp3260a drop partition for (0); |
| alter table mpp3260a drop partition for (100); |
| alter table mpp3260a drop partition for (200); |
| alter table mpp3260a drop partition for (300); |
| alter table mpp3260a drop partition for (400); |
| alter table mpp3260a drop partition for (500); |
| alter table mpp3260a drop partition for (600); |
| alter table mpp3260a drop partition for (700); |
| alter table mpp3260a drop partition for (800); |
| -- Last subpartition, cannot be dropped |
| alter table mpp3260a drop partition for (900); |
| ERROR: cannot drop partition "mpp3260a_1_prt_10" of "mpp3260a" -- only one remains |
| HINT: Use DROP TABLE "mpp3260a" to remove the table and the final partition |
| drop table mpp3260a_1_prt_10; |
| drop table mpp3260; |
| drop table mpp3260a; |
| CREATE TABLE mpp3080_int8(q1 int8, q2 int8) |
| partition by range (q1) |
| (start (1) end (5) every (1)); |
| CREATE TABLE mpp3080_float4 (f1 float4) |
| partition by range (f1) |
| (start (1) end (5) every (1)); |
| CREATE TABLE mpp3080_float8(i INT DEFAULT 1, f1 float8) |
| partition by range (f1) |
| (start (1) end (5) every (1)); |
| CREATE TABLE mpp3080_floatreal(i INT DEFAULT 1, f1 float(24)) |
| partition by range (f1) |
| (start (1) end (5) every (1)); |
| CREATE TABLE mpp3080_floatdouble(i INT DEFAULT 1, f1 float(53)) |
| partition by range (f1) |
| (start (1) end (5) every (1)); |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3080_floatdouble') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| -----------------------------+---------------------+--------+-------+-------------------------------- |
| mpp3080_floatdouble | | f | 0 | |
| mpp3080_floatdouble_1_prt_1 | mpp3080_floatdouble | t | 1 | FOR VALUES FROM ('1') TO ('2') |
| mpp3080_floatdouble_1_prt_2 | mpp3080_floatdouble | t | 1 | FOR VALUES FROM ('2') TO ('3') |
| mpp3080_floatdouble_1_prt_3 | mpp3080_floatdouble | t | 1 | FOR VALUES FROM ('3') TO ('4') |
| mpp3080_floatdouble_1_prt_4 | mpp3080_floatdouble | t | 1 | FOR VALUES FROM ('4') TO ('5') |
| (5 rows) |
| |
| CREATE TABLE mpp3080_numeric (id int4, val numeric(210,10)) |
| partition by range (val) |
| (start (1) end (5) every (1)); |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3080_numeric') as t, pg_class as c where relid = oid; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| -------------------------+-----------------+--------+-------+-------------------------------------------------- |
| mpp3080_numeric | | f | 0 | |
| mpp3080_numeric_1_prt_1 | mpp3080_numeric | t | 1 | FOR VALUES FROM (1.0000000000) TO (2.0000000000) |
| mpp3080_numeric_1_prt_2 | mpp3080_numeric | t | 1 | FOR VALUES FROM (2.0000000000) TO (3.0000000000) |
| mpp3080_numeric_1_prt_3 | mpp3080_numeric | t | 1 | FOR VALUES FROM (3.0000000000) TO (4.0000000000) |
| mpp3080_numeric_1_prt_4 | mpp3080_numeric | t | 1 | FOR VALUES FROM (4.0000000000) TO (5.0000000000) |
| (5 rows) |
| |
| CREATE TABLE mpp3080_numericbig (id int4, val numeric(1000,800)) |
| partition by range (val) |
| (start (1) end (5) every (1)); |
| -- order 1,5 |
| select t.*, pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3080_numericbig') as t, pg_class as c where relid = oid order by 1,5; |
| relid | parentrelid | isleaf | level | pg_get_expr |
| ----------------------------+--------------------+--------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| mpp3080_numericbig | | f | 0 | |
| mpp3080_numericbig_1_prt_1 | mpp3080_numericbig | t | 1 | FOR VALUES FROM (1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) TO (2.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) |
| mpp3080_numericbig_1_prt_2 | mpp3080_numericbig | t | 1 | FOR VALUES FROM (2.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) TO (3.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) |
| mpp3080_numericbig_1_prt_3 | mpp3080_numericbig | t | 1 | FOR VALUES FROM (3.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) TO (4.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) |
| mpp3080_numericbig_1_prt_4 | mpp3080_numericbig | t | 1 | FOR VALUES FROM (4.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) TO (5.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) |
| (5 rows) |
| |
| create temp TABLE temp_hour_range (f1 time(2)) |
| partition by range (f1) |
| ( |
| start (time '09:00') end (time '17:00') EVERY (INTERVAL '1 hour'), |
| default partition default_part |
| ); |
| -- MPP-26829 |
| -- negative test for when SUBPARTITION TEMPLATE shows up before any SUBPARTITION BY |
| CREATE TABLE MPP_26829 |
| (a integer, b integer NOT NULL, c integer) |
| DISTRIBUTED BY (a) |
| PARTITION BY RANGE (b) |
| SUBPARTITION TEMPLATE (START (1) END (12) EVERY (1), DEFAULT SUBPARTITION other_months ) |
| SUBPARTITION BY LIST (c) |
| SUBPARTITION TEMPLATE ( |
| SUBPARTITION p027 VALUES ('027'), |
| SUBPARTITION p141 VALUES ('141'), |
| SUBPARTITION p037 VALUES ('037')); |
| ERROR: syntax error at or near "TEMPLATE" |
| LINE 5: SUBPARTITION TEMPLATE (START (1) END (12) EVERY (1), DEFAULT... |
| ^ |
| -- MPP-26829 |
| -- Add check test on partition exchange |
| create table parttab (a int4, b int4) distributed by (a) partition by range (a) (start (1) end (5) every (2)); |
| create table parttab_x (a int4, b int4) distributed by (b); |
| alter table parttab exchange partition for (1) with table parttab_x; |
| ERROR: distribution policy for "parttab_x" must be the same as that for "parttab" |
| -- This used to work on GPDB 6 and below, but it is now rejected. ALTER |
| -- PARTITION is used to address subpartitions, it is not needed otherwise. |
| create table parttab_y (a int4, b int4) distributed by (a); |
| alter table parttab alter partition for (1) exchange partition for (1) with table parttab_y; |
| ERROR: table "parttab_1_prt_1" is not partitioned |
| -- add constraint using index is forbidden on root and interior partitioned tables |
| create table root_part(a int, b int) |
| partition by range(b) |
| subpartition by list(b) |
| subpartition template (subpartition leaf values (1)) |
| (partition interior start(1) end(2) every(1)); |
| -- disallowed on root partitions |
| create unique index on root_part(a, b); |
| alter table root_part add primary key using index root_part_a_b_idx ; |
| ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables |
| -- disallowed on interior partitions |
| create unique index on root_part_1_prt_interior_1(a, b); |
| alter table root_part_1_prt_interior_1 add primary key using index root_part_1_prt_interior_1_a_b_idx1 ; |
| ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables |
| -- allowed on leaf partitions |
| create unique index on root_part_1_prt_interior_1_2_prt_leaf(a, b); |
| alter table root_part_1_prt_interior_1_2_prt_leaf add primary key using index root_part_1_prt_interior_1_2_prt_leaf_a_b_idx2; |
| drop table root_part; |
| -- The following tests verifies reloptions inheritance for partitioned tables. |
| -- When parent's reloptions are specified, a child partition's reloptions should be set as below: |
| -- 1) If child's reloptions are specified, then the child's reloptions override the parent's reloptions. |
| -- 2) If child's reloptions are not specified, and if parent's and child's table access methods are same, then the parent's reloptions are inherited. |
| -- 3) If child's reloptions are not specified, and if parent's and child's table access methods are different, then the child's reloptions are set to default. |
| -- 4) Note that "appendonly" and "orientation" reloptions are not "real" reloptions and are instead treated as table access method specifications. |
| -- So, if child's WITH clause has only "appendonly" and/or "orientation" specified, they are handled as in (2) and (3) above. |
| -- Test 1: Parent's reloptions are explicitly specified, and parent's AM is explicitly specified as ao_row |
| create table part_ao(a int, b int) with (appendonly=true,compresstype=zlib,compresslevel=5,blocksize=65536) |
| partition by range(b) |
| (partition p1_heap start (0) end (10) with (appendonly=false), |
| partition p2_aoco start (10) end (20) with (appendonly=true, orientation=column), |
| partition p3_new_relopt start (20) end (30) with (compresstype=zstd, compresslevel=3), |
| default partition def); |
| select c.relname, am.amname, c.reloptions from pg_partition_tree('part_ao') as t join pg_class c on (t.relid::oid = c.oid) left join pg_am am on (c.relam = am.oid); |
| relname | amname | reloptions |
| -----------------------------+-----------+----------------------------------------------------- |
| part_ao | ao_row | {compresstype=zlib,compresslevel=5,blocksize=65536} |
| part_ao_1_prt_def | ao_row | {compresstype=zlib,compresslevel=5,blocksize=65536} |
| part_ao_1_prt_p1_heap | heap | |
| part_ao_1_prt_p2_aoco | ao_column | |
| part_ao_1_prt_p3_new_relopt | ao_row | {blocksize=65536,compresstype=zstd,compresslevel=3} |
| (5 rows) |
| |
| -- Test 2: Parent's reloptions are explicitly specified, and parent's AM is explicitly specified as heap |
| create table part_heap1(a int, b int) using heap with (fillfactor=70) partition by range(b) (partition p1 start(0) end(10) with(appendonly=true)); |
| select c.relname, am.amname, c.reloptions from pg_partition_tree('part_heap1') as t join pg_class c on (t.relid::oid = c.oid) left join pg_am am on (c.relam = am.oid); |
| relname | amname | reloptions |
| ---------------------+--------+----------------- |
| part_heap1 | heap | {fillfactor=70} |
| part_heap1_1_prt_p1 | ao_row | |
| (2 rows) |
| |
| -- Test 3: Parent's reloptions are explicitly specified, and parent's AM is implicitly specified as heap |
| create table part_heap2(a int, b int) with (fillfactor=70) partition by range(b) (partition p1 start(0) end(10) with(appendonly=true)); |
| ERROR: unrecognized parameter "fillfactor" |
| select c.relname, am.amname, c.reloptions from pg_partition_tree('part_heap2') as t join pg_class c on (t.relid::oid = c.oid) left join pg_am am on (c.relam = am.oid); |
| ERROR: relation "part_heap2" does not exist |
| LINE 1: ...e, am.amname, c.reloptions from pg_partition_tree('part_heap... |
| ^ |
| -- Test 4: Parent's reloptions are explicitly specified, and parent's AM is implicitly specified as ao_column |
| set default_table_access_method = 'ao_column'; |
| create table part_aoco(a int, b int) with (compresstype=zlib,compresslevel=5,blocksize=65536) |
| partition by range(b) |
| (partition p1_heap start (0) end (10) with (appendonly=false), |
| partition p2_ao start (10) end (20) with (appendonly=true, orientation=row), |
| partition p3_new_relopt start (20) end (30) with (compresstype=zstd, compresslevel=3), |
| default partition def); |
| ERROR: unrecognized parameter "compresstype" |
| select c.relname, am.amname, c.reloptions from pg_partition_tree('part_aoco') as t join pg_class c on (t.relid::oid = c.oid) left join pg_am am on (c.relam = am.oid); |
| ERROR: relation "part_aoco" does not exist |
| LINE 1: ...e, am.amname, c.reloptions from pg_partition_tree('part_aoco... |
| ^ |
| reset default_table_access_method; |
| -- cleanup |
| drop table part_ao; |
| drop table part_heap1; |
| drop table part_heap2; |
| ERROR: table "part_heap2" does not exist |
| drop table part_aoco; |
| ERROR: table "part_aoco" does not exist |
