How to create table partition in Oracle?

What is table partition in PostgreSQL?

Oracle supports 4 types of built-in partitioning types:

Range Partitioning: The data rows are distributed to partitions based on column values falling within a given range.
List Partitioning: The table is partitioned by explicitly listing which key values appear in each partition.
Interval partitioning: It’s similar to range partitioning but it can create new partitions automatically.
Hash Partitioning: Rows are evenly distributed across all partitions using a hash value of a partitioning key.

How to create table partition in Oracle?

Here is the syntax to use the PARTITION BY clause to create table with partitions in PostgreSQL.

1. To create a list partitioned table

CREATE TABLE city (
  id int4 NOT NULL PRIMARY KEY,
  name varchar(30) NOT NULL,
  state varchar(20),
  population int4,
)
PARTITION BY LIST (state) ( 
PARTITION city_east VALUES ('Virginia', 'Vermont'), 
PARTITION city_west VALUES ('California', Arizona'), 
PARTITION city_north VALUES ('Illinois', Indiana'), 
PARTITION city_south VALUES ('Texas', 'Florida'));

2. To create a range partitioned table

CREATE TABLE city (
  id int4 NOT NULL PRIMARY KEY,
  name varchar(30) NOT NULL,
  state varchar(20),
  population int4,
)
PARTITION BY RANGE (id) ( 
  PARTITION id_20 VALUES LESS THAN (20),
  PARTITION id_30 VALUES LESS THAN (30),
  PARTITION id_40 VALUES LESS THAN (40),
  PARTITION id_max VALUES LESS THAN (MAXVALUE));

3. To create an interval partitioned table

CREATE TABLE city (
  id int4 NOT NULL PRIMARY KEY,
  name varchar(30) NOT NULL,
  state varchar(20),
  population int4,
)
PARTITION BY RANGE (id) interval(1000) ( 
  PARTITION city_0 VALUES LESS THAN (0));

4. To create a hash partitioned table

CREATE TABLE city (
  id int4 NOT NULL PRIMARY KEY,
  name varchar(30) NOT NULL,
  state varchar(20),
  population int4,
)
PARTITION BY HASH (id) 
PARTITIONS 4;

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.