[ad_1]
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)');
[ad_2]