GREENPLUM: CREATE TABLE SYNTAX EXAMPLES

Create a table named rank in the schema named baby and distribute the data using the columns rank, gender, and year:

CREATE TABLE baby.rank (id int, rank int, year smallint, gender char(1), count int ) DISTRIBUTED BY (rank, gender, year);

Create table films and table distributors (the primary key will be used as the Greenplum distribution key by default):

CREATE TABLE films (code char(5) CONSTRAINT firstkey PRIMARY KEY,title varchar(40) NOT NULL,did integer NOT NULL,date_prod date,kind varchar(10),len interval hour to minute);

CREATE TABLE distributors (did integer PRIMARY KEY DEFAULT nextval('serial'),name varchar(40) NOT NULL CHECK (name <> ''));

Create a gzip-compressed, append-only table:

CREATE TABLE sales (txn_id int, qty int, date date) WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (txn_id);

Create a three level partitioned table using subpartition templates and default partitions at each level:

CREATE TABLE sales (id int, year int, month int, day int, region text)DISTRIBUTED BY (id)PARTITION BY RANGE (year) SUBPARTITION BY RANGE (month) SUBPARTITION TEMPLATE ( START (1) END (13) EVERY (1), DEFAULT SUBPARTITION other_months ) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION asia VALUES ('asia'), DEFAULT SUBPARTITION other_regions)( START (2002) END (2010) EVERY (1), DEFAULT PARTITION outlying_years);