How to fix HV007: fdw_invalid_column_name in PostgreSQL

-- For postgres_fdw, use information_schema
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'remote_table';

-- Or use the remote server's native tools
-- For Oracle: SELECT column_name FROM user_tab_columns WHERE table_name = 'REMOTE_TABLE';
-- For MySQL: SHOW COLUMNS FROM remote_table;

-- Inspect the foreign table structure
\d+ foreign_schema.remote_table

-- Or query the information schema
SELECT column_name, foreign_column_name
FROM information_schema.foreign_table_columns
WHERE foreign_table_schema = 'foreign_schema'
  AND foreign_table_name = 'remote_table';

-- For a single column mismatch, add the column_name option
ALTER FOREIGN TABLE foreign_schema.remote_table
  ALTER COLUMN local_column_name OPTIONS (column_name 'RemoteColumnName');

-- If the entire table definition is wrong, drop and recreate it
DROP FOREIGN TABLE IF EXISTS foreign_schema.remote_table CASCADE;

CREATE FOREIGN TABLE foreign_schema.remote_table (
    local_name_1 integer OPTIONS (column_name 'remote_id'),
    local_name_2 text OPTIONS (column_name 'RemoteName'),
    local_name_3 timestamp OPTIONS (column_name 'CREATED_AT')
) SERVER remote_server
  OPTIONS (schema_name 'public', table_name 'actual_remote_table');

-- Drop the incorrect foreign table
DROP FOREIGN TABLE IF EXISTS foreign_schema.remote_table CASCADE;

-- Import the schema from the remote server
IMPORT FOREIGN SCHEMA public
  LIMIT TO (actual_remote_table_name)
  FROM SERVER remote_server
  INTO foreign_schema;

-- The imported table will have column names that exactly match the remote server

CREATE FOREIGN TABLE foreign_schema.remote_table (
    "UserID" integer OPTIONS (column_name 'UserID'),
    "FirstName" text OPTIONS (column_name 'FirstName')
) SERVER remote_server;

-- Test basic SELECT
SELECT * FROM foreign_schema.remote_table LIMIT 1;

-- Test specific columns
SELECT local_name_1, local_name_2 FROM foreign_schema.remote_table;

-- If using postgres_fdw, check the EXPLAIN plan
EXPLAIN (VERBOSE) SELECT * FROM foreign_schema.remote_table WHERE local_name_1 = 42;