databasepostgresqloracle

Result Differences for Select Query between Postgres and Oracle


I'm currently in the process of migrating some data from oracle to postgres. I was doing some basic comparisons between row counts and I'm founding a problem in one of the queries. First up, all the data has been successfully migrated from Oracle to Postgres where the row counts of all tables match. After that, I was generating some json files which were being generated on some select statements and this is where I found differences between the json files generated using Oracle vs the ones generated using Postgres.

The below is a Postgres query which generates one of the json files

SELECT * FROM some_table WHERE recurring_end_dt >= CURRENT_DATE

And the below is the query from Oracle

SELECT * FROM some_table WHERE recurring_end_dt >= sysdate

Just so you know, the number of rows in that table for both the databases is the same. The null counts in that column for both databases is also same.

The row count for postgres is 14700 while that for Oracle is 14400. Since there are 300 extra rows in Postgres, I tried to understand what the value for the recurring end_date is for those 300 rows. I found that they match exactly with the value in Oracle. For example, the value in Postgres is 2023-12-07 while that in Oracle for the same primary key is 07-DEC-23. Yet, the row from Oracle doesn't get displayed as part of the result of the query even when they should. So is that a problem in Oracle or Postgres since technically Postgres is returning the correct query


Solution

  • In Oracle, a DATE is a binary data-type that consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS contains those components and it is NEVER stored in any particular human-readable format.

    For example, the value in Postgres is 2023-12-07 while that in Oracle for the same primary key is 07-DEC-23.

    No, it is not. The value in Oracle is displayed by the client application you are using as 07-DEC-23 but the value is not 07-DEC-23, it will be a binary value with both date and time components and the time component is not being displayed.

    Your client application is probably using the default NLS_DATE_FORMAT session parameter to format how it displays dates; you can change the NLS_DATE_FORMAT using:

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
    

    Then you will see that SYSDATE will have a time component and so will the dates in your table (if you have not set explicitly them then they will default to midnight).


    So is that a problem in Oracle or Postgres since technically Postgres is returning the correct query

    It is not a problem with either, your queries are asking different things.

    • In PostgreSQL, you are comparing dates only.
    • In Oracle, you are comparing dates and times so you are not going to get the values from midnight today until before the current instant; which means that if your data in the table has all the time components at midnight then all of today's rows will be excluded.

    If you want values from today at midnight (and not from the current instant) then use:

    SELECT * FROM some_table WHERE recurring_end_dt >= TRUNC(sysdate)