DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL HV004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error HV004: fdw invalid data type

The HV004: fdw invalid data type error occurs in PostgreSQL when a Foreign Data Wrapper (FDW) encounters a column data type in a foreign table definition that is incompatible with or cannot be mapped from the remote data source. This typically surfaces during query execution against foreign tables or when creating foreign table definitions that mismatch the actual remote schema. It is common across various FDW implementations including postgres_fdw, oracle_fdw, mysql_fdw, and file_fdw.

Top 3 Causes

1. Mismatched Column Types Between Foreign Table and Remote Source

The most frequent cause is declaring the wrong PostgreSQL type in CREATE FOREIGN TABLE that doesn't align with the actual remote column type.

-- WRONG: Oracle NUMBER(10,2) mapped to INTEGER causes HV004
CREATE FOREIGN TABLE public.foreign_orders (
    order_id    INTEGER,
    amount      INTEGER,       -- should be NUMERIC(10,2)
    order_date  DATE
)
SERVER oracle_server
OPTIONS (schema 'SALES', table 'ORDERS');

-- CORRECT: Use compatible types
CREATE FOREIGN TABLE public.foreign_orders (
    order_id    INTEGER,
    amount      NUMERIC(10, 2),   -- matches Oracle NUMBER(10,2)
    order_date  TIMESTAMP         -- matches Oracle DATE
)
SERVER oracle_server
OPTIONS (schema 'SALES', table 'ORDERS');
Enter fullscreen mode Exit fullscreen mode

2. Using Unsupported PostgreSQL-Specific Types in Foreign Tables

PostgreSQL-native types like JSONB, UUID, ARRAY, or HSTORE are often not supported by third-party FDW drivers, causing HV004 when data retrieval is attempted.

-- WRONG: JSONB not supported by mysql_fdw
CREATE FOREIGN TABLE public.foreign_products (
    product_id   INTEGER,
    attributes   JSONB          -- mysql_fdw cannot map this
)
SERVER mysql_server
OPTIONS (dbname 'inventory', table_name 'products');

-- CORRECT: Use TEXT and cast via a view
CREATE FOREIGN TABLE public.foreign_products (
    product_id   INTEGER,
    attributes   TEXT           -- use TEXT for compatibility
)
SERVER mysql_server
OPTIONS (dbname 'inventory', table_name 'products');

-- Expose JSONB through a view
CREATE OR REPLACE VIEW public.v_products AS
SELECT
    product_id,
    attributes::JSONB AS attributes
FROM public.foreign_products;
Enter fullscreen mode Exit fullscreen mode

3. Remote Schema Changed After Foreign Table Was Created

When the remote database schema changes (column type alteration, column addition/removal), existing foreign tables become stale and trigger HV004 on next query.

-- Check current foreign table column definitions
SELECT
    a.attname       AS column_name,
    t.typname       AS current_type,
    a.attnum        AS column_order
FROM pg_attribute a
JOIN pg_type t      ON t.oid = a.atttypid
WHERE a.attrelid = 'public.foreign_orders'::regclass
  AND a.attnum > 0
ORDER BY a.attnum;

-- Re-import the remote schema to sync types automatically
DROP FOREIGN TABLE IF EXISTS public.foreign_orders CASCADE;

IMPORT FOREIGN SCHEMA sales
    LIMIT TO (orders)
    FROM SERVER oracle_server
    INTO public;

-- Verify the re-imported table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Drop and recreate the foreign table with correct type mappings after consulting the FDW driver's official type compatibility documentation.
  2. Use IMPORT FOREIGN SCHEMA instead of manual CREATE FOREIGN TABLE to let PostgreSQL automatically resolve type mappings from the remote source.
  3. Use TEXT as a safe fallback for any problematic column, then cast to the desired type in a view layer. 
-- Universal diagnostic: list all foreign tables with their column types
SELECT
    fs.srvname             AS server,
    c.relname              AS foreign_table,
    a.attname              AS column_name,
    t.typname              AS data_type
FROM pg_foreign_table ft
JOIN pg_foreign_server fs ON ft.ftserver = fs.oid
JOIN pg_class c           ON ft.ftrelid = c.oid
JOIN pg_attribute a       ON a.attrelid = c.oid AND a.attnum > 0
JOIN pg_type t            ON t.oid = a.atttypid
ORDER BY fs.srvname, c.relname, a.attnum;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always use IMPORT FOREIGN SCHEMA when setting up foreign tables to rely on automatic type resolution rather than manual mapping. Re-run it as part of your deployment pipeline whenever the remote schema changes.
  • Maintain a type compatibility matrix for each FDW in use (e.g., Oracle ↔ PostgreSQL, MySQL ↔ PostgreSQL) and add smoke tests (SELECT 1 FROM foreign_table LIMIT 1) to your CI/CD pipeline to catch HV004 before it reaches production.

Related Errors

  • HV000 – General FDW error; parent class of HV004.
  • HV005 – fdw column name not found; often appears alongside HV004 during schema drift.
  • HV021 – fdw invalid column number; related to column ordering mismatches in foreign tables.
  • 42804 – datatype mismatch; a non-FDW type error that can co-occur with HV004 in complex queries.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)