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.
- select 'my value' value_col, 678 my_id from 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
------------------------------