PostgreSQL raises HV00R when a foreign-data wrapper cannot locate a referenced table on the remote server. This typically occurs when querying a foreign table that has been dropped, renamed, or never existed on the remote side. Verifying the remote table existence and updating the foreign table definition resolves this error.
HV00R is a SQL/MED error code that PostgreSQL uses when a foreign-data wrapper (FDW) cannot find a table referenced in a foreign table query. This error occurs during query planning when PostgreSQL attempts to validate that the remote table exists before sending queries across the wire. Foreign-data wrappers allow PostgreSQL to query data from external sources as if they were local tables. When you create a foreign table in PostgreSQL, it points to a table on a remote server. If that remote table is dropped, renamed, or otherwise becomes inaccessible, any query against the foreign table will fail with HV00R. This error serves as an early validation check, preventing PostgreSQL from sending queries to non-existent remote tables and providing clear feedback about the schema mismatch.
Check the local foreign table definition to see what remote table it references.
-- Show detailed foreign table information:
\d+ schema_name.foreign_table_name
-- Query system catalogs for FDW table mapping:
SELECT ft.ftoptions, s.srvname
FROM pg_foreign_table ft
JOIN pg_foreign_server s ON ft.ftserver = s.oid
WHERE ft.ftrelid = 'schema_name.foreign_table_name'::regclass;This reveals the server name and options that define which remote table this foreign table points to.
Connect directly to the remote server (using the same credentials as the FDW) to verify the table exists.
-- For postgres_fdw connecting to another PostgreSQL instance:
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'remote_schema'
AND tablename = 'remote_table';
-- Check for case sensitivity issues:
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname ILIKE 'remote_schema'
AND tablename ILIKE 'remote_table';
-- For other FDWs, use appropriate remote catalog queries
-- For MySQL FDW:
SHOW TABLES FROM remote_database LIKE 'remote_table';If the table doesn't exist, you've found the root cause.
If the remote table was renamed or moved, update the foreign table definition to point to the correct table.
-- Drop and recreate the foreign table with correct remote table name:
DROP FOREIGN TABLE IF EXISTS schema_name.foreign_table_name;
CREATE FOREIGN TABLE schema_name.foreign_table_name (
-- column definitions
)
SERVER remote_server
OPTIONS (table_name 'correct_remote_table');
-- Or use IMPORT FOREIGN SCHEMA to recreate multiple tables:
IMPORT FOREIGN SCHEMA remote_schema
LIMIT TO (correct_remote_table)
FROM SERVER remote_server
INTO schema_name;Make sure the column definitions match the remote table structure.
Check that the FDW user has permission to access the remote table.
-- Test the FDW connection directly:
SELECT * FROM dblink(
'dbname=remote_db host=remote_host user=fdw_user',
'SELECT 1'
) AS t(test int);
-- For postgres_fdw, check user mapping:
SELECT * FROM pg_user_mappings
WHERE srvname = 'remote_server';
-- Ensure the remote user has SELECT permission on the table:
-- On remote server:
GRANT SELECT ON remote_schema.remote_table TO fdw_user;Connection or permission issues can sometimes manifest as table not found errors.
If the remote table exists but PostgreSQL still reports it as missing, refresh the FDW metadata cache.
-- For postgres_fdw, refresh the foreign table cache:
ALTER FOREIGN TABLE schema_name.foreign_table_name
OPTIONS (ADD refresh 'true');
-- Or disconnect and reconnect to force cache refresh:
-- Restart the PostgreSQL service or
-- Execute for each foreign table:
NOTIFY pg_fdw_refresh_cache;
-- Check if there are any pending schema changes:
SELECT * FROM pg_foreign_table
WHERE ftrelid = 'schema_name.foreign_table_name'::regclass;Some FDWs cache table metadata which can become stale.
HV00R corresponds to SQL/MED error fdw_table_not_found, which any SQL/MED-compliant FDW can raise when a referenced remote table cannot be located. This error typically occurs before any query execution, during the planning phase when PostgreSQL validates foreign table metadata.
Different FDWs may have different behaviors: some check table existence on every query, while others cache this information. The postgres_fdw extension, for example, caches foreign table metadata and may need explicit refresh when remote schemas change.
When troubleshooting HV00R, consider not only table existence but also schema/table name case sensitivity, search path settings on the remote server, and any schema name mapping options specified in the foreign table definition.
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