Conversion¶
Identifiers¶
PostgreSQL Migrator lowers identifiers, except mixed case identifiers. You can disable identifier lowering using Convert.PreserveCase configuration.
Data types¶
PostgreSQL Migrator implements column type conversion using the following mapping:
| Oracle | PostgreSQL |
|---|---|
NUMBER |
numeric |
NUMBER(p < 5, 0) |
smallint |
NUMBER(5 <= p < 10, 0) |
int |
NUMBER(10 <= p < 20, 0) |
bigint |
FLOAT(p < 64), BINARY_FLOAT |
real |
FLOAT(p < 127), BINARY_DOUBLE |
double precision |
TIMESTAMP WITH LOCAL TIME ZONE |
timestamp with timezone |
DATE |
timestamp |
NCHAR |
CHAR |
NVARCHAR, VARCHAR2, NVARCHAR2 |
varchar |
RAW(16), RAW(32) |
uuid |
RAW, BLOB, LONG RAW |
bytea |
LONG, CLOB, NCLOB |
text |
XMLTYPE |
xml |
If a type is not in above tables, PostgreSQL Migrator simply lowers type name and preserve type parameters (length, precision, scale, etc.) and options.
| MySQL | PostgreSQL |
|---|---|
tinyint(1) |
boolean |
decimal, decimal unsigned |
numeric |
year, year(n), smallint(n), tinyint, tinyint(n), tinyint unsigned, tinyint(n) unsigned |
smallint |
smallint unsigned, smallint(n) unsigned |
int |
mediumint, mediumint(n), mediumint unsigned, mediumint(n) unsigned |
int |
int(n) |
int |
int unsigned, int(n) unsigned |
bigint |
bigint(n) |
bigint |
bigint unsigned, bigint(n) unsigned |
numeric(20, 0) |
double, double unsigned, binary_double |
double precision |
float, float unsigned, binary_float |
real |
datetime |
timestamp |
enum |
text |
set |
text[] |
binary, varbinary, tinyblob, mediumblob, longblob |
bytea |
tinytext, mediumtext, longtext |
text |
You can override a mapping using Convert.DataTypes or define a type for a specific column using Convert.Rules.Type.
Indexes and constraints¶
PostgreSQL Migrator renames indexes and constraints following PostgreSQL convention, when the name has been generated by the source system.
Generated name detection is based on following rules:
- indexes that are marked as generated in
ALL_INDEXES - constraints that are marked as generated in
ALL_CONSTRAINTS
- index that shares the same name of its table
- index that starts with
_ibfk_prefix - all primary keys that are named
PRIMARY - unique key that shares the same name of the first column
- foreign key that starts with the name of its table with an
_ibfk_suffix - check that starts with the name of its table with an
_chek_suffix
- index that shares the same name of its table
- foreign key that contains numbers only
- check that contains
CONSTRAINT_and numbers
You can force entire index renaming using Convert.RenameIndexes, and constraint renaming using Convert.RenameConstraints. This avoids namespace conflicts.