Oracle Database and PostgreSQL Core Database Features Comparison

Data Types

Numeric Types:

• Oracle: `NUMBER`, `INTEGER`, `FLOAT`
• PostgreSQL: `NUMERIC`, `DECIMAL`, `INTEGER`, `FLOAT`

Character Types:

• Oracle: `VARCHAR2`, `CHAR`, `CLOB`
• PostgreSQL: `VARCHAR`, `CHAR`, `TEXT`

Date/Time Types:

• Oracle: `DATE`, `TIMESTAMP`, `INTERVAL`
• PostgreSQL: `DATE`, `TIMESTAMP`, `INTERVAL`

Binary Types:

• Oracle: `BLOB`, `RAW`
• PostgreSQL: `BYTEA`

Boolean:

• Oracle: `NUMBER(1)`
• PostgreSQL: `BOOLEAN`

Spatial Types:

• Oracle: `SDO_GEOMETRY`
• PostgreSQL: `PostGIS Geometry`

XML Types:

• Oracle: `XMLTYPE`
• PostgreSQL: `XML`

SQL Functions and Syntax

String Functions:

• Oracle: `CONCAT(string1, string2)`
• PostgreSQL: `string1 || string2`

Character Types:

• Oracle: `SUBSTR(string, start, length)`
• PostgreSQL: `SUBSTRING(string FROM start FOR length)`

Date Functions:

• Oracle: `SYSDATE`
• PostgreSQL: `CURRENT_DATE`
• Oracle: `ADD_MONTHS(date, n)`

• PostgreSQL: `date + interval ‘n months’`

Mathematical Functions:

• Oracle: `ROUND(number, decimals)`
• PostgreSQL: `ROUND(number, decimals)`
• Oracle: `TRUNC(number, decimals)`
• PostgreSQL: `TRUNC(number, decimals)`

Procedural Languages

Stored Procedures and Functions:

• Oracle: PL/SQL
• PostgreSQL: PL/pgSQL

Sequence Generation

Sequences:

• Oracle: `CREATE SEQUENCE seq_name START WITH 1`
• PostgreSQL: `CREATE SEQUENCE seq_name START WITH 1`
• Oracle: `NEXTVAL(seq_name)`
• PostgreSQL: `NEXTVAL(‘seq_name’)`

Indexes and Constraints

Primary Key:

• Oracle: `ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column)`
• PostgreSQL: `ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column)`

Unique Constraint:

• Oracle: `ALTER TABLE table_name ADD CONSTRAINT unique_name UNIQUE (column)`
• PostgreSQL: `ALTER TABLE table_name ADD CONSTRAINT unique_name UNIQUE (column)`

Indexes:

• Oracle: `CREATE INDEX index_name ON table_name (column)`
• PostgreSQL: `CREATE INDEX index_name ON table_name (column)`

Transaction Control

Begin Transaction:

• Oracle: `BEGIN`
• PostgreSQL: `BEGIN`

Commit Transaction:

• Oracle: `COMMIT`
• PostgreSQL: `COMMIT`

Rollback Transaction:

• Oracle: `ROLLBACK`
• PostgreSQL: `ROLLBACK`

User and Role Management

Create User:

• Oracle: `CREATE USER username IDENTIFIED BY password`
• PostgreSQL: `CREATE USER username WITH PASSWORD ‘password’`

Grant Privileges:

• Oracle: `GRANT privilege TO user`
• PostgreSQL: `GRANT privilege TO user`

Database Links:

• Oracle: `CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING ‘tns_name’`
• PostgreSQL: Use Foreign Data Wrapper (FDW) with `CREATE SERVER` and `CREATE USER MAPPING`

Views

Create View:

• Oracle: `CREATE VIEW view_name AS SELECT * FROM table_name`
• PostgreSQL: `CREATE VIEW view_name AS SELECT * FROM table_name`

Partitions

Table Partitioning:

• Oracle: `CREATE TABLE table_name (…) PARTITION BY RANGE (column)`
• PostgreSQL: `CREATE TABLE table_name (…) PARTITION BY RANGE (column)`

PL/SQL and PL/pgSQL

Stored Procedures and Functions:

Oracle PL/SQL:

```
CREATE OR REPLACE PROCEDURE proc_name IS
BEGIN
 - pl-sql procedure code
END;
```

PostgreSQL PL/pgSQL:

``pl-pgsql
CREATE OR REPLACE FUNCTION func_name() RETURNS void AS $$
BEGIN
 - pl/pgSQL procedure code
END;
```

JSON Support

JSON:

• Oracle: `JSON_OBJECT`, `JSON_ARRAY`
• PostgreSQL: `json`, `jsonb` data types with rich support for JSON functions like `json_build_object`, `json_agg`

Oracle Database vs PostgreSQL: External Tools and Functionalities Comparison

High Availability and Replication

Oracle Data Guard:

• Oracle Data Guard provides high availability, data protection, and disaster recovery for enterprise data.

PostgreSQL Equivalent:

• Streaming Replication: Built-in streaming replication for high availability and disaster recovery.
• Repmgr: Manages and monitors PostgreSQL streaming replication, offering automatic failover capabilities.

Backup and Recovery

Oracle RMAN (Recovery Manager):

• Provides backup and recovery solutions, including full, incremental, and differential backups.

PostgreSQL Equivalent:

• pg_dump: Utility for backing up databases in various formats.
• pgBackRest: Robust backup and restore tool supporting full, incremental, differential backups, and point-in-time recovery.
• Barman (Backup and Recovery Manager): Backup and recovery management tool with advanced features such as remote backup and disaster recovery.

Monitoring and Management

Oracle Enterprise Manager:

• Comprehensive management tool for Oracle environments.

PostgreSQL Equivalent:

• pgAdmin: Feature-rich management and administration tool.
• PgBouncer: Lightweight connection pooler.
• Prometheus and Grafana: Advanced monitoring and alerting with powerful visualization.
• Zabbix: Open-source monitoring system.

Data Integration and ETL

Oracle GoldenGate:

• Real-time data integration and replication solution.

PostgreSQL Equivalent:

• Bucardo: Asynchronous multi-master replication system.
• Debezium: Change data capture platform for real-time data streaming.
• Apache Kafka with Kafka Connect: Real-time data streaming and integration.

Data Warehousing and Analytics

Oracle Exadata/Oracle Database Appliance:

• Combined software and hardware platform for high performance in data warehousing and OLTP.

PostgreSQL Equivalent:

• Amazon Redshift: PostgreSQL-compatible managed data warehouse service.
• Greenplum: Open-source data warehouse software built on PostgreSQL.
• Citus: Extension that transforms PostgreSQL into a distributed database.

Full-Text Search

Oracle Text:

• Full-text search and retrieval capabilities.

PostgreSQL Equivalent:

• Full-Text Search: Built-in capabilities with support for indexes and querying.
• PGroonga: Extension for full-text search using Groonga.

Data Encryption

Oracle Advanced Security:

• Provides data encryption and other security features.

PostgreSQL Equivalent:

• pgcrypto: Extension providing cryptographic functions.
• SSL/TLS: Support for encrypting data in transit.