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 | - 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. |
| 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 ✓ | - 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 ALTER TABLE ... ADD CONSTRAINT. This primary index enforces uniqueness in each partition but not across the entire partition hierarchy. In other words, you can have the same value repeated for the primary index column in two or more partitions. |
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 | 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 Easy creation of specialized indexes | No | Yes | |
| GIN 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 ✓ |
Could this page be better? Report a problem or suggest an addition!