Clarity

Expand all | Collapse all

Clarity SaaS Transition:Compare Oracle and PostgreSQL

  • 1.  Clarity SaaS Transition:Compare Oracle and PostgreSQL

    Broadcom Employee
    Posted 04-27-2020 11:09 AM

    Welcome to the Clarity PPM SaaS Transition Blog series. This blog series provides customers updates regarding the Clarity PPM SaaS transition to the Google Cloud Platform™ (GCP).  In the first six blogs, Broadcom provided updates on various aspects of the migration including the overview, pre-transition activities, the actual transition process, and the various authentication methods available in GCP. 


    As Broadcom is making progress with customer transition to the new GCP based infrastructure, customers are asking us to share some tips on differences between Oracle and PostgreSQL. In this post, Broadcom will review common data type differences between Oracle and PostgreSQL. Should you wish to implement additional customizations, this information will help you better understand the differences between Oracle and PostgreSQL databases. 


    We will start by reviewing certain data types that function differently between PostgreSQL and Oracle. We will also review some common functions that operate differently in PostgreSQL and Oracle. We will also review transactions that function differently in PostgreSQL and Oracle. Finally, we will review some miscellaneous differences between  Oracle and PostgreSQL. 


    Note: This blog is not a tutorial on how to write a query in PostgreSQL. We recommend customers refer to online tutorials, such as Tutorials Point, for detailed syntax.

    Data Types -  PostgreSQL vs. Oracle

     

    Note: In SaaS, we do not allow custom table creation, however, the information below is for reference for your report generation.

     

    Oracle Datatype

    Purpose

    PostgreSQL Datatype

    Difference

    CHAR(n)




    VARCHAR2(n)






    Stores character strings where length is specified when you create the table




    Stores variable-length character strings

    CHAR(n)




    VARCHAR(n)

    In Oracle "n" refers to the number of bytes while in PostgreSQL it refers to the number of characters

    NUMBER(n,m)





    NUMBER(4)




    NUMBER(9)



    NUMBER(18)


    NUMBER(n)

    Stores fixed and floating-point numbers. 

    NUMERIC(n,m)





    SMALLINT




    INT



    BIGINT


    NUMERIC(n)

    While the NUMBER data type can be converted to NUMERIC, you will experience better performance if you use the SMALLINT, INT, and BIGINT data types.





    NUMBER(n) in Oracle can be substituted with NUMERIC(n) in PostgreSQL when "n" is greater than equal to 19. 

    DATE

    Stores point-in-time values in a table

    TIMESTAMP(0)

    While the PostgreSQL also has a date datatype, it only returns the date. Hence, it is recommended to use the TIMESTAMP(0) datatype if you need both date and time. 

    CLOB

    Stores and manipulate large blocks of unstructured data such as text, images, audio files, and video files

    TEXT

    In PostgreSQL, the TEXT data type can store up to 1GB of text.

    BLOB RAW(n)

    Stores up to 128 TB of unstructured binary data in the database

    BYTEA


    Large object

    In PostgreSQL, the BYTEA datatype has a limit of 1 GB. You can use the Large object datatype for data greater than 1 GB.

     

    Functions - PostgreSQL vs. Oracle

     

    This section helps you understand common key functions that operate differently in Oracle and PostgreSQL. 

     

    SYSDATE Function

     

    PostgreSQL does not have one single function that maps to the SYSDATE function in Oracle. You can use the following methods in PostgreSQL to obtain date and time. 

     

    • The SQL start time can be determined by using the Statement_timestamp() function.
    • The transaction start time can be determined by using the Transaction_timestamp() function or the now() function. 
    • The clock_timestamp() function specifies the time when the function is implemented. 



    TO_DATE Function

     

    The TO_DATE function in PostgreSQL returns the year, month, and day while it returns the year, month, day, hour, time, and second in Oracle. 



    SUBSTR Function

     

    This function behaves differently in Oracle and PostgreSQL. The SUBSTR function executes without an error in PostgreSQL but returns a different value. 

    Other Differences between PostgreSQL and Oracle

     

    DUAL Table

     

    In PostgreSQL, the From clause is not mandatory for every SELECT statement hence the DUAL table is not necessary. 

     

    • Oracle with Dual
    • select 'my value' value_col, 678 my_id from dual

     

    • Postgres without Dual

    select 'my value' value_col, 678 my_id

     

    Different Behaviour of Empty String and NULL Values 

     

    • In PostgreSQL, the concatenation of a null value and string value results in null whereas you get a string value in Oracle. 

     

    • The syntax of a sequence in Oracle is slightly different from PostgreSQL.

      • Oracle: Sequence_name.nextval

      • PostgreSQL: Nextval('sequence_name') 
      • For example: Select nextval('s_work_orders') into work_order_id;

     

    The DELETE statement in PostgreSQL does not work without a FROM clause.

      • PostgreSQL example: DELETE  FROM ppa_MatrixValues WHERE MatrixKey = PAC_MNT_MATRIX_DELETE_SP.iMatrixKey;

     

    Additional noticeable differences

     

    • Oracle uses the (+) operator for left and right joins. PostgreSQL does not use the (+) operator. 

     

    • PostgreSQL uses WITH RECURSIVE to support hierarchical queries while Oracle uses START WITH...CONNECT BY.

     

    • PostgreSQL uses the keyword except, while Oracle uses minus

     

    • The use of count(1) is a performance enhancement that applies only to older versions of Oracle. Use count(*) for PostgreSQL. 



    This concludes this blog post comparing Oracle and PostgreSQL. Thank you for being a part of the Clarity PPM community. Please write to clarityppm.saas@broadcom.com in case you have any specific questions for us. The next blog will be published on May 4th.



    #ca_clarity_ppm #gcp #clarityppmsaas



    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------


  • 2.  RE: Clarity SaaS Transition:Compare Oracle and PostgreSQL

    Former Employee
    Posted 04-27-2020 11:36 AM
    Thank you for the update Suman!

    ------------------------------
    Chris Hackett
    Community Manager, Broadcom Enterprise Software Division
    Broadcom Inc.
    ------------------------------