Postgres - Oracle data type conversion

923 Views Asked by At

We have a foreign table that is connecting to Oracle. In Oracle, the columns are:

ticker:     VARCHAR2(5)
article_id: NUMBER

In Postgres, we have tried to create the article_id as INTEGER and NUMERIC, but every time we try and query we get this error:

 column "article_id" of foreign table "latest_article_id" cannot be converted to or from Oracle data type

How can we create this foreign table so we can query it? The article_id is a number, so is there additional commands we must use?

We are on Postgres 10.10.

CREATE FOREIGN TABLE latest_article_id
  (ticker         VARCHAR,
   article_id     NUMERIC)
 SERVER usercomm
 OPTIONS ( table '(SELECT article_id, ticker
                   FROM (SELECT a.article_id, t.ticker,
                                ROW_NUMBER() OVER (PARTITION BY t.ticker 
ORDER BY a.publish_date DESC NULLS LAST) AS rnum
                         FROM tickers t, article_tickers at, articles a
                         WHERE t.ticker_id = at.ticker_id
                           AND at.article_id = a.article_id
                           AND a.status_id = 6
                           AND a.pull_flag = ''Y'')
                   WHERE rnum = 1)');
0

There are 0 best solutions below