Notes on the following documentation.
Converting from Oracle to Amazon RDS for PostgreSQL or Amazon Aurora (PostgreSQL) - AWS Schema Conversion Tool https://docs.aws.amazon.com/ja_jp/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.ToPostgreSQL.html#CHAP_Source.Oracle.ToPostgreSQL.PG10Partitioning
> The following are some known issues with the conversion of partitions to PostgreSQL version 10.
> Only non-NULL columns can be used as partition column.
> DEFAULT cannot be used as a partition value.
For example, suppose you have a range-partitioned table like this, using ORDER_DATE as the partition key.
drop table TAB_RANGE_PART;
CREATE TABLE TAB_RANGE_PART (
ORDER_ID NUMBER primary key,
ORDER_DATE DATE,
BOOK_NO VARCHAR(20) NOT NULL,
BOOK_TYPE VARCHAR(20) NOT NULL,
BOOK_CNT NUMBER NOT NULL,
REMARKS VARCHAR2(40))
LOGGING
PCTFREE 20
PARTITION BY RANGE (ORDER_DATE) (
PARTITION TAB_RANGE_PART01 VALUES LESS THAN ( TO_DATE('20160101','YYYYMMDD')),
PARTITION TAB_RANGE_PART02 VALUES LESS THAN ( TO_DATE('20170101','YYYYMMDD')),
PARTITION TAB_RANGE_PART03 VALUES LESS THAN ( TO_DATE('20180101','YYYYMMDD')),
PARTITION TAB_RANGE_PART04 VALUES LESS THAN ( TO_DATE('20190101','YYYYMMDD')),
PARTITION TAB_RANGE_PART05 VALUES LESS THAN ( MAXVALUE ))
ENABLE ROW MOVEMENT;
The above DDL does not set any constraint on ORDER_DATE, so there is a possibility that NULL values can be inserted into ORDER_DATE. In this case, when converting with SCT, it appears that no error occurs, but the table is not created as a partition table. This falls under the following restriction:
> Only non-NULL columns can be used as partition column.
This is what happens:
Need to be careful about the partition key of existing tables. Adding a NOT NULL constraint to the partition key ORDER_DATE results in this:
drop table TAB_RANGE_PART;
CREATE TABLE TAB_RANGE_PART (
ORDER_ID NUMBER primary key,
ORDER_DATE DATE NOT NULL,
BOOK_NO VARCHAR(20) NOT NULL,
BOOK_TYPE VARCHAR(20) NOT NULL,
BOOK_CNT NUMBER NOT NULL,
REMARKS VARCHAR2(40))
LOGGING
PCTFREE 20
PARTITION BY RANGE (ORDER_DATE) (
PARTITION TAB_RANGE_PART01 VALUES LESS THAN ( TO_DATE('20160101','YYYYMMDD')),
PARTITION TAB_RANGE_PART02 VALUES LESS THAN ( TO_DATE('20170101','YYYYMMDD')),
PARTITION TAB_RANGE_PART03 VALUES LESS THAN ( TO_DATE('20180101','YYYYMMDD')),
PARTITION TAB_RANGE_PART04 VALUES LESS THAN ( TO_DATE('20190101','YYYYMMDD')),
PARTITION TAB_RANGE_PART05 VALUES LESS THAN ( MAXVALUE ))
ENABLE ROW MOVEMENT;