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.
Stores character strings where length is specified when you create the table
Stores variable-length character strings
In Oracle "n" refers to the number of bytes while in PostgreSQL it refers to the number of characters
Stores fixed and floating-point numbers.
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.
Stores point-in-time values in a table
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.
Stores and manipulate large blocks of unstructured data such as text, images, audio files, and video files
In PostgreSQL, the TEXT data type can store up to 1GB of text.
Stores up to 128 TB of unstructured binary data in the database
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.
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 TO_DATE function in PostgreSQL returns the year, month, and day while it returns the year, month, day, hour, time, and second in Oracle.
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
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
Different Behaviour of Empty String and NULL Values
The DELETE statement in PostgreSQL does not work without a FROM clause.
Additional noticeable differences
This concludes this blog post comparing Oracle and PostgreSQL. Thank you for being a part of the Clarity PPM community. Please write to firstname.lastname@example.org in case you have any specific questions for us. The next blog will be published on May 4th.
#ca_clarity_ppm #gcp #clarityppmsaas