-- foreign database
CREATE TABLE foreign_table (id BIGSERIAL);
-- local database
CREATE TABLE local_table (id BIGSERIAL, remote_id BIGSERIAL);
CREATE FOREIGN TABLE local_foreign_table (
id BIGINT
) SERVER aserver
OPTIONS (
query '
SELECT
id
FROM
foreign_table
');
-- local query
SELECT
local.id,
local.remote_id,
foreign.id
FROM
local_table as local,
local_foreign_table as foreign
WHERE
local.remote_id = local_foreign_table.id
Problem: for several rows, JOIN operation only applies for the first one row in resultset. If we cast boths IDs (local.id and foreign.id) to Integer (instead of BigInt), all results are correct, the match occurs in all resultset.
-- foreign database CREATE TABLE foreign_table (id BIGSERIAL);
-- local database CREATE TABLE local_table (id BIGSERIAL, remote_id BIGSERIAL);
CREATE FOREIGN TABLE local_foreign_table ( id BIGINT ) SERVER aserver OPTIONS ( query ' SELECT id FROM foreign_table ');
-- local query SELECT local.id, local.remote_id, foreign.id FROM local_table as local, local_foreign_table as foreign WHERE local.remote_id = local_foreign_table.id
Problem: for several rows, JOIN operation only applies for the first one row in resultset. If we cast boths IDs (local.id and foreign.id) to Integer (instead of BigInt), all results are correct, the match occurs in all resultset.