Comparison of database featuresSuggest edits
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.
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.
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.|
|Materialized views||Yes||- Postgres doesn't provide an automatic refresh feature. Triggers can be used as part of a Postgres solution. |
- Postgres doesn't provide an incremental refresh option, only full refresh.
|Partition by range||Yes ✓|
|Partition by hash||Yes ✓|
|Partition by list||Yes ✓|
|Interval partitioning||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 ✓||- In Postgres, some keyword options aren't available. |
- Some difference in maximum sequence value.
|Global indexes||No||Global indexes on partitioned tables are not available in Postgres. An index isn't inherited when you define a primary key on a partitioned table that doesn't include partition key columns. However, all partitions defined in CREATE TABLE have an independent primary index on the column. You can re-create the primary key on all newly added partitions by using |
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||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)|
|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|
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|
|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|
Easy creation of specialized indexes
Custom inverted indexes
|K-nearest-neighbor||Yes||No||Available in Oracle using the package DMBS_DATA_MINING and Spatial option.|
|Spatial||Yes||Yes||Available in Postgres using free PostGIS extension.|
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|
|Inner Joins||Yes||Yes ✓|
|Outer Joins||Yes||Yes ✓|
|Inner Selects||Yes||Yes ✓|
|Merge Joins||Yes||Yes ✓|
|Common Table Expressions||Yes||Yes|
|Query Hints||Yes||Yes ✓|
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|
|NVL, NVL2||Yes ✓|
|(+) Syntax for Outer Joins||Yes ✓|