1. Creating partition table with "Create table" command.


CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
[ PARTITION BY [ HASH | LIST | RANGE ] (column_name)  
 [
  PARTITIONS num_hash_partitions | 
  list_patition [,...] |
  range_partition [,...] 
 ]
]

where list_partition is:
[Partition_name] VALUES 
 [ (const_expression[,...]) | DEFAULT]

Database will generate partition name, if it is not specified.

where range_partition is:
[Partition_name] 
  [
   ([START const_expression] END const_expression) | 
   DEFAULT
  ]
Database will generate partition name, if it is not specified.
Start key word is optional. Default values can be stored in a 'default' partition.


where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | 
  NULL | 
  UNIQUE [ USING INDEX TABLESPACE tablespace ] |
  PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
  CHECK (expression) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
  PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]



2. Adding/Splitting/Updating/dropping a partition to an existing table with ALTER TABLE command.


ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema

ALTER TABLE name
    ADD PARITION [list_partition | range_partition]


ALTER TABLE name
    SPLIT PARITION [Partition_name] AT 
    VALUES const_expression [,...] 
    [INTO (Partition_name1, Partition_name2)]
The  into clause will allow users to provide names of newly created partitions after the split.
For list partitioning, Partition_name2 will be created with the list of specified const_expressions, and
Partition_name will be renamed to Partition_name1 after excluding specified values from its list.
For range partition, a const_expression will indicate the split point. Partition_name2 will be formed with 
max_value and split_point as the range, and Partition_name1 will be formed with min_value and split_point
range.



ALTER TABLE name
    UPDATE PARTITION 
    [ 
     PARTITIONS TO num_hash_partitions  | 
     [Partition_name | VALUES (const_expression[,...])] TO VALUES (const_expression[,...]) | 
     [Partition_name | START (const_expression) END (const_expression) ] TO START (const_expression) END (const_expression)
    ]
   

To change partition key values for list and range partitioned tables, the user has to either Specify partition name
of partition list/range that identifies the partition to be  updated.

ALTER TABLE name
    DROP PARITION 
    Partition_name | 
    FOR VALUES (const_expression [,...]) |
    FOR ([START const_expression] END const_expression)


ALTER TABLE table_name
    RENAME Partition_name to Partition_name


where action is one of:

    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column TYPE type [ USING expression ]
    ALTER [ COLUMN ] column SET DEFAULT expression
    ALTER [ COLUMN ] column DROP DEFAULT
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint
    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    OWNER TO new_owner
    SET TABLESPACE new_tablespace


Things identified in WIP patch for Autopartition by Nikhil S.

1. CREATE TABLE statement has been modified to add Range and List partitions.
2. Full constraints need to be specified for creating partitions.
3. This implementation does not make use of any catalog tables to store information about partitions, constraints.
3. “PARTITION BY HASH” is not implemented.
4. Functions like to_date() can be used in the constraints.