PostgreSQL raises HV007 when a foreign-data wrapper encounters a column name that does not exist on the remote data source. This typically occurs due to case sensitivity mismatches, incorrect column_name mappings, or remote table schema changes. Verifying column names and updating the foreign table definition to match the remote schema resolves the error.
HV007 is part of the SQL/MED (SQL Management of External Data) error class that PostgreSQL uses when a Foreign Data Wrapper (FDW) cannot find a column name on the remote server. Foreign tables are proxies to external data sources. When PostgreSQL sends a query to fetch data, it references columns by the names defined in the local foreign table. If those names do not exist on the remote server—because of case sensitivity issues, typos, incorrect column_name option mappings, or schema drift—the FDW raises HV007 before attempting the remote operation. Unlike HV005 (fdw_column_name_not_found), which occurs when a column is completely missing, HV007 indicates that the *name* provided is invalid or does not match any column on the remote side. This distinction helps you diagnose whether the column exists but is named differently, or if it truly does not exist.
Connect directly to the remote server and list the columns to see their exact names, including case and special characters.
-- 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;Note the exact spelling and case of each column name. This is critical because some databases (like Oracle) fold unquoted identifiers to uppercase, while others (like PostgreSQL) fold to lowercase.
Examine the column names declared in your local foreign table and compare them with the remote server's actual column names.
-- 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';Look for discrepancies in:
- Spelling (typos or abbreviated names)
- Case (uppercase vs lowercase)
- Special characters (underscores vs hyphens, spaces, etc.)
- The fdw_column_name option if specified
If the remote column names differ from your local foreign table definition, use ALTER FOREIGN TABLE to add or update the fdw_column_name option, which tells the FDW the exact name to use on the remote side.
-- 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');The column_name option explicitly maps the local column to the remote column, bypassing any case or naming convention differences.
Many FDWs support IMPORT FOREIGN SCHEMA, which automatically creates foreign table definitions by introspecting the remote server. This is the safest way to avoid HV007.
-- 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 serverIMPORT FOREIGN SCHEMA queries the remote server's catalog and builds the foreign table definition automatically, ensuring that column names always match. If the remote schema changes in the future, you can re-import to sync again.
If the remote server treats column names as case-sensitive, quote the column names in the foreign table definition to preserve case.
CREATE FOREIGN TABLE foreign_schema.remote_table (
"UserID" integer OPTIONS (column_name 'UserID'),
"FirstName" text OPTIONS (column_name 'FirstName')
) SERVER remote_server;PostgreSQL folds unquoted identifiers to lowercase by default, so explicitly quoted identifiers preserve their case. This is especially important when the remote server (Oracle, SQL Server, MySQL in certain modes) expects mixed-case or uppercase names.
After updating the foreign table definition, test with a SELECT query to confirm the HV007 error is resolved.
-- 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;If the query succeeds without HV007, the column names are now correctly mapped.
HV007 corresponds to the SQL/MED error code fdw_invalid_column_name. Different FDW implementations may raise this error under slightly different conditions:
postgres_fdw: Uses case-insensitive matching and automatically folds unquoted identifiers to lowercase. To preserve mixed-case remote column names, use the column_name option or quote identifiers in the CREATE FOREIGN TABLE statement.
Oracle FDW: Often encounters HV007 because Oracle folds unquoted identifiers to uppercase, while PostgreSQL folds to lowercase. Always use the column_name option or IMPORT FOREIGN SCHEMA to map correctly.
MySQL/MariaDB: Default case sensitivity depends on the underlying filesystem. If the remote table has mixed-case column names, explicitly specify them using the column_name option.
Caching: Some FDW implementations cache metadata about remote tables. If you change a remote table's column names but the FDW returns an old error, try closing and reopening the connection to the foreign server.
Validation Order: PostgreSQL validates column names during query planning, before sending anything to the remote server. This prevents HV007 errors from generating unnecessary network traffic to the remote server.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL