Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
Hi Team,
Please consider this schema of range partitions in MySQL.
Source schema:
CREATE TABLE salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
PRIMARY KEY (emp_no, from_date)
);
ALTER TABLE salaries
partition by range (to_days(from_date))
(
partition p01 values less than (to_days('1985-01-01')),
partition p02 values less than (to_days('1986-01-01'))
);
Exported schema:
CREATE TABLE salaries (
emp_no bigint NOT NULL,
salary bigint NOT NULL,
from_date timestamp NOT NULL,
to_date timestamp NOT NULL,
PRIMARY KEY (emp_no,from_date)
) PARTITION BY RANGE (((from_date)::date - '0001-01-01bc')::integer) ;
CREATE TABLE salaries_p01 PARTITION OF salaries
FOR VALUES FROM (MINVALUE) TO (725007);
CREATE TABLE salaries_p02 PARTITION OF salaries
FOR VALUES FROM (725007) TO (725372);
This throws an error in PG
ERROR: syntax error at or near "::"
LINE 7: ...ION BY RANGE (((from_date)::date - '0001-01-01bc')::integer)...
Also I wanted your opinion on something, there's a characteristic of PG wherein we can't have primary/unique keys on a partitioned table if the partition key is an expression like in the above example.
What should be our workaround in such a case?
Should the tool itself remove the primary/unique keys, or the user should do it manually?
Hi Team, Please consider this schema of range partitions in MySQL.
Source schema:
Exported schema:
This throws an error in PG
Also I wanted your opinion on something, there's a characteristic of PG wherein we can't have primary/unique keys on a partitioned table if the partition key is an expression like in the above example. What should be our workaround in such a case? Should the tool itself remove the primary/unique keys, or the user should do it manually?
Thank You for your help and insights.