Postgres vs Oralce

Postgres and Oracle are among the most popular and widely used RDBMS. Both systems have similar features and different features that make them unique to any environment or business setting.  In this series I will cover both systems but will not go in depth into their Administrative side

Similarities 
  • Both systems supports JDBC connection protocols which can be utilized to connect to java based applications like Oracle JDeveloper.
  • For data manipulation, To_Char, To_Number, To_Date & To_Timestamp , concat (|| ||),  Upper and Initcap which are utilized in Oracle based database can also be used in Postgres.

  • Sequence is utilized in both Databases to generate unique identifiers for primary key based columns. With Oracle 12c, identity column was introduced as an alternate for sequence.
  • B tree  based indexes are utilized in both database systems for Primary key columns utilized for unique columns.

Difference

There quite difference with Oracle PLSQL Syntax and Posgtes PL/pgSQL Syntax.

  • Oracle  and Postgres has three types of Triggers which are before, after and instead  but their syntax  are  different.

Oracle 

SQL>   create or replace trigger CountryCodesAudit_del_trigg
  2    after delete on countrycode
  3     for each row
  4     DECLARE
  5        OCCD              INTEGER;
  6        OCCN              Varchar2(60);
  7        OCAB              Varchar2(12);
  8    ACT                char(20);
  9     begin
 10       SELECT :OLD.Country_Code INTO OCCD FROM DUAL;
 11   SELECT :OLD.Country_Name INTO OCCN FROM DUAL;
 12   SELECT :OLD.Country_Abbrv INTO OCAB FROM DUAL;
 13   SELECT 'Delete' INTO ACT FROM DUAL;
 14 
 15      INSERT INTO CountryCodesAudit(old_countrycode, old_countryname,old_countryabbrv, Action_Type )
 16           SELECT OCCD, OCCN, OCAB, ACT  FROM DUAL;
 17 

 18     end CountryCodesAudit_del_trigg;

PostgreSQL 



  • Oracle and Postgres both allows Not Null constraints but only Oracle allows Not Null constraints to be named. 

 Oracle 

SQL> Alter table countrycode
  2    modify  Country_Abbrv constraint country_coabbrv_nn  not null;

Postgres 

  • TableSpace can be created in both database systems to store data which makes it easier to perform various administration jobs. In Oracle a temporary tablespace is needed  to sort data and the owner of the tablespace is assigned after it has been created  as illustrated below.


Oracle
SQL> create tablespace  HowardRetail
  2  datafile'C:\Oracle12c\oradata\Uwa\Retail' size 1G Online;
Tablespace created.

SQL> create temporary tablespace HowardRetail_Temp
  2  tempfile 'C:\Oracle12c\oradata\Uwa\Retail_Temp' SIZE 80M  autoextend on;
Tablespace created.
SQL>
SQL> create user HowardDBA
  2  Identified by howard
  3  default  tablespace HowardRetail;
User created.

  • Oracle has stored procedures and  user defined functions. Postgres has user defined function


Oracle Stored procedures


Postgres User-defined  Functions


  


  • In posgres multiple database allows multiple database per instance while Oracle allows one database per instance but with additional tablespace which can be allocated to multiple applications.
  • Postgres utilizes schema and Exclusion constraints.

Comments

  1. James ZicrovJune 3, 2019 at 8:13 AM

    Thank you so much for preparing such a detailed analysis and comparison about Postgres and Oracle.

    SSIS PostgreSql Read

    ReplyDelete

Post a Comment

Popular posts from this blog

ETL ( Automation vs Manual )

ETL applications like Talend, SSIS and Pentaho can simplify ETL or Data migration by eliminating or reducing the amount of SQL coding needed to complete the process. However same functions performed by ETL applications can be performed by scripting using various dialect of SQL and programming language like C# or Java. The downside of scripting manually without an ETL application is the need of connecting to data source and destination source. However most vendors like Microsoft SQL Server provides developers the option through linked server using SQL Server Management Studio or Oracle MySQL Workbench for MySQL.  Depending on the application a third programming language is need as interface like Java or C++ when performing the process from a developmental application. This will allow both the source and destination sources to be linked.  Another problem with the manual process is the complexity of SQL coding needed which needs to be altered constantly and the length of the co...
Read more

Data Migration

Migrating data  can be a challenging  task depending on the size of the data, platform and systems involved with the migration process. The age or the nature of data can also add to the complexity of the migration process. Legacy data or old data needs to be re-validated and cleaned to prevent any major issues when the data has been migrated just like you have with Data Warehouse system. ETL applications like Talend , Pentaho and Microsoft SSIS can be utilized in this process. The structure of the data plays a major role in determining the schema and the layer of the data.Texted based systems and RDBMS have different properties that needs to be examined and changed. The final properties to be examined is the platform. Migrating data from SQL Server 2008 to SQL Server 2012 is much easier than migrating from SQL Server 2008 to Oracle 12c or 11g due to different datatypes, objects , schema and properties. In some cases the underlying objects like ...
Read more

SSIS, SSAS & Stored Procedure

Image
In the following series SSIS would be utilized to extract data from Stored Procedure which contain query that is linked with SSAS MDX query via linked Server. Talend requires additional components while SSIS has fewer components but the output is the same. First process in extracting the query through SSAS via Linked Server is to create a linked server. Linked Server Second process is to create the stored procedure to extract the data. Stored Procedure definition with the MDX and regular SQL qeury Third process is to create an SSIS package. In the Control Flow component you need  two task which the Execute SQL Task and Data Flow task.  The fourth step is to create the database connection to the source and destination systems. In this case only one database system is being utilized. Another big difference between SSIS and Talend is that Talend  works well with JDBC but JDBC is allowed in SSIS rather .NET, OLED, ODBC etc which makes t...
Read more