Skip to Main Content
  • Questions

  • Insert date from Oracle to PostgreSQL

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, malik.

Asked: February 05, 2018 - 11:37 am UTC

Last updated: February 05, 2018 - 3:45 pm UTC

Version: 11.0.4

Viewed 1000+ times

You Asked

I try to insert date form oracle to postgresql

my database date format is dd/mm/yyyy
postgresql date format yyyy/mm/dd

insert into "public"."test"@PG_LINK
select 'test' name, TO_DATE (TO_CHAR(SU.DATE_OF_BIRTH, 'yyyy/mm/dd'),'yyyy/mm/dd') test_date
from MOH.HEAC_STUDENT_DATA su


but I got this error

ORA-02070: database PG_LINK does not support in this context

and Chris said...

Oracle Database stores dates in its own internal format. Whether it's shown as dd/mm/yyyy or yyyy-mm-dd is purely a client-side setting:

alter session set nls_date_format = 'dd/mon/yyyy';
select sysdate from dual;

SYSDATE       
05/feb/2018 

alter session set nls_date_format = 'yyyy-mm-dd';
select sysdate from dual;

SYSDATE      
2018-02-05


So there's no need for the to_char/to_date conversion. Remove these from your SQL!

That said, Oracle Database dates include the time. PostgreSQL dates exclude the time. So you may still have conversion issues.

If this doesn't help check you've got the ODBC Gateway configured correctly:

https://docs.oracle.com/cd/E11882_01/gateways.112/e12013/toc.htm

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.