Choosing a correct datatype mapping while migrating from Oracle to PostgreSQL is very important to avoid migration failures. Especially when we have date and time involved, it is very important to understand the behavior in both Oracle and PostgreSQL. We have documented about DATE datatype in Oracle vs SQL Server vs PostgreSQL in our previous article. In this article, I am going to discuss about avoiding constraint violations while migrating from Oracle to PostgreSQL when DATE data type is involved.
**DATE datatype in PostgreSQL does not store Time whereas Oracle stores both DATE and TIME**
If you would have gone through our previous article, you should have seen that the Oracle's DATE datatype stores time along with date whereas Postgres DATE datatype stores only the DATE. In Oracle, the default NLS_DATE_FORMAT is DD-MM-RR and many of the customers may leave it as default. When you query this data, you may only be seeing the DATE but not TIME due to NLS_DATA_FORMAT setting.
Since it displays only DATE part of the data in Oracle, customers may choose DATE as the target data type in Postgres, during the process of migration from Oracle to PostgreSQL. This could be the beginning of surprising errors.
Composite Key involving an Oracle DATE column
Through the following example, let us see if we get any error when there exists a Composite Key involving an Oracle DATE column with same DATE but different TIME inserted to a column with DATE data type.
CREATE TABLE DATE_TEST(A VARCHAR(10), D DATE, CONSTRAINT PK PRIMARY KEY (A,D));We shall now insert some records to this Oracle table along with both DATE and TIME for the column with DATE data type. Please note the second and third insert statements have same values for the first column and also the date part of the date column but with some difference in the time part of the date column.
INSERT INTO DATE_TEST VALUES('AA',TO_DATE('06-08-23 18:10:24', 'DD-MM-RR HH24:MI:SS'));
INSERT INTO DATE_TEST VALUES('JJ',TO_DATE('15-09-23 18:15:35', 'DD-MM-RR HH24:MI:SS'));
INSERT INTO DATE_TEST VALUES('JJ',TO_DATE('15-09-23 18:20:43', 'DD-MM-RR HH24:MI:SS'));The above insert statements get successfully inserted with no constraint violation errors. Upon querying the data from this Oracle table, we would only see the DATE by default. This is because the default NLS_DATE_FORMAT is DD-MM-RR. It appears like duplicate data but it is not as it also contains time.
SELECT D FROM DATE_TEST; ---------- 06-08-23 15-09-23 15-09-23
When we try fetching data using TO_CHAR function and provide a custom date/time format as following, we also notice the TIME along with the DATE.
SELECT TO_CHAR(D, 'DD-MM-RR HH24:MI:SS') FROM DATE_TEST; --------------------- 06-08-23 18:10:24 15-09-23 18:15:35 15-09-23 18:20:43
Migrating DATE in Oracle to DATE in PostgreSQL
Let us now migrate the table to PostgreSQL and use DATE datatype in Postgres as the alternative for DATE datatype in Oracle.
CREATE TABLE _MIGOPSTEST.DATE_TEST(A TEXT, D DATE, PRIMARY KEY (A,D));Let us try inserting the same data inserted to the Oracle table to the newly created Postgres table. If you notice the following insert statement, we are also inserting TIME along with DATE into a column with DATE data type in Postgres. The first row gets inserted with no errors.
INSERT INTO _MIGOPSTEST.DATE_TEST VALUES('AA',TO_DATE('06-08-23 18:10:24', 'DD-MM-YY HH24:MI:SS'));Now the question is, did it also store the time along with the date ? The answer is NO. As seen in the following, when we now select the inserted data, we only see the DATE but not the TIME that we inserted.
SELECT D FROM _MIGOPSTEST.DATE_TEST; ----------- 2023-08-06
Let us use TO_CHAR() function and try fetching the TIME and see if it got inserted.
SELECT TO_CHAR(D, 'DD-MM-YY HH24:MI:SS') FROM _MIGOPSTEST.DATE_TEST; ----------------- 06-08-23 00:00:00
It is clear that the TIME that we attempted to insert is not truly inserted. This is because when we insert date and time data into DATE datatype column, postgres simply ignores the time part and inserts only the date. And when we used TO_CHAR() function to fetch time data, the time is shown as 00:00:00. The time value : 00:00:00 is not being stored in the table, but the TO_CHAR() function has returned default time value as there is no time data available in this column.
Constraint violations with PostgreSQL DATE data type
To understand about possible constraint violations in detail, we shall insert the next 2 rows with same DATE but with different TIME values.
INSERT INTO _MIGOPSTEST.DATE_TEST VALUES('JJ',TO_DATE('15-09-23 18:15:35', 'DD-MM-YY HH24:MI:SS'));
INSERT 0 1
INSERT INTO _MIGOPSTEST.DATE_TEST VALUES('JJ',TO_DATE('15-09-23 18:20:43', 'DD-MM-YY HH24:MI:SS'));
ERROR: duplicate key value violates unique constraint "date_test_pkey"
DETAIL: Key (a, d)=(JJ, 2023-09-15) already exists.
SQL state: 23505As there are two dates with same value, and the time part is being ignored, and the unique constraint is being violated. Previously, in our Oracle example, when there are same dates being inserted, as the time is different, there was no problem. But in the case of Postgres, we encountered an issue with constraint violation as the time is ignored by Postgres with DATE datatype. This issue will be same even when a single Oracle DATE datatype is involved in a primary/unique key constraint and we try to migrate it to Postgres DATE datatype.
Better alternative for Oracle DATE data type in PostgreSQL
A better alternative to provide same behavior as Oracle DATE data type in PostgreSQL is timestamp(0). Through the following example, let us ALTER the Postgres table and change the datatype from DATE to TIMESTAMP(0). We shall then attempt same insert statements keeping the same constraint as earlier.
TRUNCATE _MIGOPSTEST.DATE_TEST;ALTER TABLE _MIGOPSTEST.DATE_TEST ALTER COLUMN D TYPE TIMESTAMP(0);INSERT INTO _MIGOPSTEST.DATE_TEST VALUES('AA',TO_TIMESTAMP('06-08-23 18:10:24', 'DD-MM-YY HH24:MI:SS')); INSERT 0 1 INSERT INTO _MIGOPSTEST.DATE_TEST VALUES('JJ',TO_TIMESTAMP('15-09-23 18:15:35', 'DD-MM-YY HH24:MI:SS')); INSERT 0 1 INSERT INTO _MIGOPSTEST.DATE_TEST VALUES('JJ',TO_TIMESTAMP('15-09-23 18:20:43', 'DD-MM-YY HH24:MI:SS')); INSERT 0 1
With no errors in the above statements, we can see that the values are successfully inserted into Postgres table.
SELECT D FROM _MIGOPSTEST.DATE_TEST; --------------------- 2023-08-06 18:10:24 2023-09-15 18:15:35 2023-09-15 18:20:43
Conclusion
When we migrate data from Oracle to PostgreSQL, if the DATE in Oracle mapped to TIMESTAMP(0), we see that both the DATE and TIME from Oracle is inserted into PostgreSQL. When we migrate to TIMESTAMP(0), we may need to modify some application code. But this also saves us from losing time data while migration. Because, if we migrate from Oracle DATE to Postgres DATE, we will lose all the TIME data from these columns along with possible constraint violations. We have similarly blogged about Row and Column Level Security in Oracle vs PostgreSQL and about handling trailing zeroes with Numeric data types. For similar interesting information, please subscribe to our newsletters. If you are looking for support on optimizing your PostgreSQL databases or migrating your databases to PostgreSQL, contact us today.
