Invalid check constraint conversion to PostgreSQL format while exporting schema from Oracle

[priyanshi-yb]

I have one check constraint in Oracle which is validating a timestamp column with a proper format, and after applying that exported schema from Oracle in PG. I am getting a check constraint violation while loading data and on trying to understand the issue I discovered that the format that is exported in that check constraint is not appropriate of that check constraint upon fixing it to valid syntax it worked. Example in Oracle -

CREATE TABLE test_format (id INT, t TIMESTAMP);
ALTER TABLE test_format ADD CONSTRAINT ch CHECK (t = TO_TIMESTAMP('2022-01-01 11:21:22.212112', 'YYYY-MM-DD HH24:MI:SS.FF'));

Exported schema

CREATE TABLE test_format (
    id numeric(38),
    t timestamp
) ;
ALTER TABLE test_format ADD CONSTRAINT ch CHECK (t = TO_TIMESTAMP('2022-01-01 11:21:22.212112','YYYY-MM-DD HH24:MI:SS.FF'));

After applying these DDLs on PostgreSQL and trying to insert data, it is failing with check constraint violation error

postgres=> CREATE TABLE test_format (
postgres(> id numeric(38),
postgres(> t timestamp
postgres(> ) ;
CREATE TABLE
postgres=> ALTER TABLE test_format ADD CONSTRAINT ch CHECK (t = TO_TIMESTAMP('2022-01-01 11:21:22.212112','YYYY-MM-DD HH24:MI:SS.FF'));
ALTER TABLE
postgres=> 
postgres=> insert into test_Format values(1, '2022-01-01 11:21:22.212112');
ERROR:  new row for relation "test_format" violates check constraint "ch"
DETAIL:  Failing row contains (1, 2022-01-01 11:21:22.212112).

But upon fixing the format in constraint from YYYY-MM-DD HH24:MI:SS.FF to YYYY-MM-DD HH24:MI:SS.US, inserts worked fine-

postgres=> ALTER TABLE test_format ADD CONSTRAINT ch CHECK (t = TO_TIMESTAMP('2022-01-01 11:21:22.212112','YYYY-MM-DD HH24:MI:SS.us'));
ALTER TABLE
postgres=> insert into test_Format values(1, '2022-01-01 11:21:22.212112');
INSERT 0 1

Can someone please take a look at this issue and let me know if I am missing something or if there is a bug?

CC: @darold

[darold]

The format replacement was limited to the TO_TIMESTAMP_TZ function, not TO_TIMESTAMP , commit 898f6ac fixes this issue.