Comparison of database features¶
To assess the feasibility of your migration, look at the tables that follow to understand how the elements you want to migrate are handled in an Oracle-to-EDB Postgres Advanced Server migration.
Tables legend¶
The tables use these words or symbols to denote compatibility:
Yes/No — Denotes whether the feature or characteristic is supported in the database.
✓ — The feature operates in a manner compatible with Oracle, allowing you to continue using or migrate your existing Oracle skills, program code, or data.
Tables and partitioning¶
The range of constructs in the database and how much flexibility DBAs have in organizing these structures can affect performance as well as maintenance and other operational requirements. The ability to partition a database improves performance, for example. Organizing data into distinct structures and distributing them across the infrastructure also improves manageability, availability, and load balancing. Materialized views allow DBAs to replace slow, resource-intensive runtime queries, complex joins, or lengthy scans of data with simple, faster reads from prejoined, presorted, and stored results.
Oracle Enterprise |
EDB Postgres Advanced Server |
Notes |
|---|---|---|
Temporary tables |
Partial |
Global temporary tables aren’t supported in Postgres. |
Views |
Yes |
|
Materialized views |
Yes |
|
Partitioning |
Yes ✓ |
|
Partition by range |
Yes ✓ |
|
Partition by hash |
Yes ✓ |
|
Partition by list |
Yes ✓ |
|
Subpartitioning |
Yes ✓ |
|
Interval partitioning |
Yes ✓ |
|
Partitioned indexes |
No |
|
ANSI constraints |
Yes |
|
Tablespaces |
Yes |
In Oracle and Postgres, tablespaces are used to organize and specify where tables, indexes, and other database files are stored. However, unlike Oracle, in Postgres, tablespaces are directories in which automatically generated table, index, and other database files are stored. |
Index organized tables |
No |
In Postgres, you can cluster a table by an index, providing similar performance boosts when reading data from a presorted structure. |
Sequences |
Yes ✓ |
|
Data types¶
Data types provide ways for a DBMS to define, implement, and use information in the system and put constraints on how data is interpreted by the database when multiple data types are in use. EDB Postgres Advanced Server has strong compatibility with Oracle data types and is highly extensible, allowing it to quickly support new and emerging data types and workloads as they become popular.
Data types |
Oracle Enterprise |
EDB Postgres Advanced Server |
|---|---|---|
Type system |
Static + dynamic (through ANYDATA) |
Static integer NUMBER NUMBER ✓, DEC , NUMERIC, SMALLINT (16-bit), INT, BINARY_INTEGER, PLS_INTEGER, INTEGER (32-bit), BIGINT (64 bit) |
Floating point |
BINARY_FLOAT, BINARY_DOUBLE |
BINARY_FLOAT ✓, BINARY_DOUBLE ✓, FLOAT, REAL (32-bit), DOUBLE PRECISION (64-bit) |
Decimal |
NUMBER |
NUMBER ✓, DEC, DECIMAL, NUMERIC |
String |
CHAR, VARCHAR2, CLOB, NCLOB, NVARCHAR2, NCHAR, LONG (deprecated) |
CHAR ✓, VARCHAR ✓, CLOB ✓, NCLOB ✓, NVARCHAR2 ✓, NCHAR, CHARACTER, TEXT, CHAR, VARYING, CHARACTER VARYING, VARCHAR |
Binary |
BLOB, RAW, LONG RAW (deprecated), BFILE |
BLOB ✓, RAW ✓, LONG RAW ✓, BYTEA (no compatible type for BFILE) |
Date/time |
DATE, TIMESTAMP (with/without TIMEZONE), INTERVAL |
DATE ✓ TIMESTAMP (with/without TIMEZONE), INTERVAL ✓, TIME (with/without TIMEZONE) |
Boolean |
Not Available |
BOOLEAN |
ROWID |
ROWID |
ROWID |
XMLTYPE |
XMLTYPE |
XMLTYPE |
Key-value |
Requires NSWLDB which is a separate database program |
Yes, is integrated into the core database |
JSON |
Use VARCHAR2, CLOB, and BLOB with is_json check constraint. |
JSON and fast binary JSONB with 58 JSON operators, functions and relational json converters |
Spatial / Geospatial |
Yes |
Yes |
Other |
IMAGE, AUDIO, VIDEO, DICOM |
ENUM, POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE, CIDR, INET, MACADDR, BIT, UUID, XML, arrays, composites, ranges, custom |
Data domains |
Yes |
Yes |
Indexes¶
To provide optimal performance for the wide range of supported data types and new workloads using those data types, the database must also support a wide variety of indexes. Postgres is somewhat unique in this regard, especially its GiST index, which allows for easy development of specialized indexes for new data types.
Indexes |
Oracle Enterprise |
EDB Postgres Advanced Server |
Notes |
|---|---|---|---|
B-Tree |
Yes |
Yes |
|
Hash |
Yes |
Yes |
|
Expressions |
Yes |
Yes |
|
Partial |
Yes |
Yes |
|
Reverse |
Yes |
No |
In Postgres, a functional index can be used to reverse the order of a field. |
Bitmap |
Yes |
No |
Use of BRIN index might be suitable in some use cases. |
Block Range Index |
Yes |
Yes |
|
GiST <br/> Easy creation of specialized indexes |
No |
Yes |
|
GIN <br/> Custom inverted indexes |
No |
No |
|
K-nearest-neighbor |
Yes |
No |
Available in Oracle using the package DMBS_DATA_MINING and Spatial option. |
Full-text search |
Yes |
Yes |
|
Spatial |
Yes |
Yes |
Available in Postgres using free PostGIS extension. |
SQL capabilities¶
EDB Postgres Advanced Server strongly conforms to the ANSI-SQL:2008 standard. It also has transactional DDL, which supports backing out even large changes to DDL, such as table creation. While you can’t recover from an add/drop on a database or tablespace, all other catalog operations are reversible. This feature is often used for protection when doing complicated work like schema upgrades. If you put all such changes into a transaction block, you can make sure they all apply atomically or not at all. This lowers the possibility that the database will be corrupted by a typographic or other such error in the schema change, which is particularly important when you’re modifying multiple related tables where a mistake might destroy the relational key.
SQL capabilities |
Oracle Enterprise |
EDB Postgres Advanced Server |
|---|---|---|
Union |
Yes |
Yes ✓ |
Intersect |
Yes |
Yes ✓ |
Except |
Yes |
Yes ✓ |
Inner Joins |
Yes |
Yes ✓ |
Outer Joins |
Yes |
Yes ✓ |
Inner Selects |
Yes |
Yes ✓ |
Merge Joins |
Yes |
Yes ✓ |
Common Table Expressions |
Yes |
Yes |
Windowing Functions |
Yes |
Yes |
Parallel Query |
Yes |
Yes |
Query Hints |
Yes |
Yes ✓ |
Transactional DDL |
No |
Yes |
Alter Session |
Yes |
Yes |
Dynamic SQL |
Yes |
Yes |
SQL extensions¶
Oracle has a number of SQL extensions that are popular with Oracle users. While not standard to the SQL language, they provide utility and convenience to DBAs and developers. EDB Postgres Advanced Server supports the ones that EDB customers ask for most often.
Oracle Enterprise extension |
EDB Postgres Advanced Server |
|---|---|
DUAL |
Yes ✓ |
DECODE |
Yes ✓ |
ROWNUM |
Yes ✓ |
SYSDATE |
Yes ✓ |
SYSTIMESTAMP |
Yes ✓ |
NVL, NVL2 |
Yes ✓ |
(+) Syntax for Outer Joins |
Yes ✓ |
