NUMBER |
NUMBER(p) becomes smallint, integer, or bigint by precision, and numeric(p,0) beyond 18 digits. NUMBER(p,s) becomes numeric(p,s); a bare NUMBER becomes numeric. |
Every digit is kept. A key declared as a bare NUMBER arrives as numeric; change it to bigint in the mapping if the application expects an integer. |
VARCHAR2, NVARCHAR2, CHAR, CLOB, LONG |
varchar(n) and char(n), with the length counted in characters, including VARCHAR2(n BYTE). CLOB, NCLOB, and LONG become text. |
Check sorting and comparisons that relied on the Oracle collation. |
DATE, TIMESTAMP |
timestamp. An Oracle DATE carries a time of day, so it does not become a PostgreSQL date. |
Queries that compare a DATE column with a plain date still match only midnight values, as they did in Oracle. |
TIMESTAMP WITH TIME ZONE, WITH LOCAL TIME ZONE |
timestamptz, written in UTC. The instant is the same whatever time zone the PostgreSQL server runs in. |
PostgreSQL stores the instant, not the original offset or zone name. Keep the offset in its own column if the application reads it. |
BLOB, RAW, LONG RAW, INTERVAL, floating point |
Binary types become bytea, both INTERVAL types interval, BINARY_FLOAT real, FLOAT and BINARY_DOUBLE double precision. |
Values arrive byte for byte; nothing to adjust for most applications. |
| Identity columns, defaults |
An identity column becomes integer or bigint with a sequence, by precision, keeping its values. Constant defaults are kept; SYSDATE becomes the current time. |
The next insert gets a key above the copied ones. Keys filled by a trigger and a sequence are copied as values; plan their generation separately. |
| Upper-case names |
Unquoted Oracle names, stored in upper case, arrive in lower case. A name created in mixed case keeps it. |
Queries can use unquoted lower-case names in PostgreSQL. Mixed-case names must stay quoted. |
