The DATE Data Type in Oracle vs. PostgreSQL

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. In this article, we are going to discuss about DATE datatype in Oracle and PostgreSQL, and avoiding constraint violations while migrating from Oracle to PostgreSQL when DATE data type is involved.

DATE datatype in Oracle

A key thing to remember is that Oracles DATE datatype stores time along with the date, whereas the PostgreSQL DATE datatype stores only the date. In Oracle, the default NLS_DATE_FORMAT may be 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 the DATE part of the data in Oracle, customers may mistakenly 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.

By the way, HexaRocket automatically handles this scenario, when using HexaRocket to convert Schema, migrate data and also during CDC for replication from Oracle to PostgreSQL. HexaRocket is an end-to-end database migration tool created by the creators of Ora2Pg. See differences between Ora2Pg vs HexaRocket here.

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. To make it more interesting, consider inserting the same DATE but with different TIME to the column with the 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 components for the column with the 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-25 18:10:24', 'DD-MM-RR HH24:MI:SS'));
INSERT INTO DATE_TEST VALUES('JJ',TO_DATE('15-09-25 18:15:35', 'DD-MM-RR HH24:MI:SS'));
INSERT INTO DATE_TEST VALUES('JJ',TO_DATE('15-09-25 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 in this case. It appears like duplicate data but it is not, because it also contains a different time in both the rows for this column.

    SELECT D FROM DATE_TEST;
-------------------------------------
06-08-25
15-09-25
15-09-25

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-25 18:10:24
15-09-25 18:15:35
15-09-25 18:20:43

Migrating DATE in Oracle to DATE in PostgreSQL

Let us now migrate this table to PostgreSQL and use DATE datatype in Postgres as the alternative for DATE datatype in Oracle.

    -- In PostgreSQL

CREATE TABLE _HEXATEST.DATE_TEST (
    A text,
    D date,
    PRIMARY KEY (A, D)
);

Let us try inserting the same data that was previously 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.

    -- In PostgreSQL
INSERT INTO _HEXATEST.DATE_TEST VALUES('AA',TO_DATE('06-08-25 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 block, when we select the inserted data, we only see the DATE but not the TIME that we inserted.

    -- In PostgreSQL

SELECT D FROM _HEXATEST.DATE_TEST;
-------------------------------------
2025-08-06

Let us use TO_CHAR() function and try fetching the TIME as well, and see if it actually got inserted.

    -- In PostgreSQL

SELECT TO_CHAR(D, 'DD-MM-YY HH24:MI:SS') FROM _HEXATEST.DATE_TEST;
-------------------------------------
06-08-25 00:00:00

It is clear from the above output 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 the 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.

    -- In PostgreSQL

INSERT INTO _HEXATEST.DATE_TEST VALUES('JJ',TO_DATE('15-09-25 18:15:35', 'DD-MM-YY HH24:MI:SS'));
INSERT 0 1

INSERT INTO _HEXATEST.DATE_TEST VALUES('JJ',TO_DATE('15-09-25 18:20:43', 'DD-MM-YY HH24:MI:SS'));
ERROR: duplicate key value violates unique constraint "date_test_pkey"
DETAIL: Key (a, d)=(JJ, 2025-09-15) already exists.
SQL state: 23505

As expected, there are two dates with same value, and as the time part is being ignored, 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 there exists a primary key on single column of Oracle DATE datatype. This will also result in a primary/unique key constraint when 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 the same insert statements and also retain the same constraint as earlier.

    -- In PostgreSQL

TRUNCATE _HEXATEST.DATE_TEST;

ALTER TABLE _HEXATEST.DATE_TEST ALTER COLUMN D TYPE TIMESTAMP(0);

INSERT INTO _HEXATEST.DATE_TEST VALUES('AA',TO_TIMESTAMP('06-08-25 18:10:24', 'DD-MM-YY HH24:MI:SS'));
INSERT 0 1
INSERT INTO _HEXATEST.DATE_TEST VALUES('JJ',TO_TIMESTAMP('15-09-25 18:15:35', 'DD-MM-YY HH24:MI:SS'));
INSERT 0 1
INSERT INTO _HEXATEST.DATE_TEST VALUES('JJ',TO_TIMESTAMP('15-09-25 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 the Postgres table.

    SELECT D FROM _HEXATEST.DATE_TEST;
-------------------------------------
2025-08-06 18:10:24
2025-09-15 18:15:35
2025-09-15 18:20:43

Conclusion

When we migrate data from Oracle to PostgreSQL, if the DATE in Oracle is mapped to TIMESTAMP(0) in PostgreSQL, we see that both the DATE and TIME from Oracle is inserted to 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.

Subscribe to our Newsletters and Stay tuned for more interesting topics.

If you need expert support migrating legacy or complex Oracle, SQL Server, Sybase, MySQL, MariaDB databases to PostgreSQL or distributed databases, we’re here to help. HexaCluster provides end-to-end migration and modernization services, including application migration and modernization, database migration, and PostgreSQL consulting such as performance tuning, health audits, managed DBA services, and 24/7/265 support. To start a conversation or explore how we can support your migration journey, please contact us at connect@hexacluster.ai