How to fix HV004: fdw_invalid_data_type in PostgreSQL

Connect to your PostgreSQL database and describe the foreign table to see all column names and their declared data types.

-- Display the foreign table structure:
\d+ foreign_schema.foreign_table_name

-- Or query the information schema:
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'foreign_schema'
  AND table_name = 'foreign_table_name'
ORDER BY ordinal_position;

Note the exact data type of each column, especially those that might be problematic (date types, timestamps, numeric types, text-to-number mappings).

Connect directly to the remote database and retrieve the actual column definitions. This is the source of truth for what the FDW must convert.

For Oracle:

CONNECT TO oracle_instance;
DESC remote_table_name;
-- Or:
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = 'REMOTE_TABLE_NAME'
ORDER BY column_id;

For MySQL:

DESC database_name.remote_table_name;
SHOW FULL COLUMNS FROM database_name.remote_table_name;

For PostgreSQL remotes via postgres_fdw:

-- On the remote server:
\d remote_table_name

Compare the remote data types with the types declared in your local foreign table definition.

Cross-reference your remote types with the FDW documentation to identify which types are not supported or require special handling.

Common problematic mappings:
- oracle_fdw: Oracle NUMBER without scale → PostgreSQL numeric; Oracle DATE → PostgreSQL timestamp; Oracle CLOB → not supported locally
- postgres_fdw: Generally forgiving but prefers exact type matches; collation mismatches can cause issues
- mysql_fdw: MySQL ENUM not directly mappable; JSON columns need special handling

Consult the FDW documentation or the conversion table in the handler code to see the exact type mappings supported.

Drop and recreate the foreign table columns using data types that the FDW actually supports. Most commonly, this means changing abstract or incompatible remote types to text or varchar locally and handling conversion in application logic.

-- Drop the foreign table if it has problematic columns:
DROP FOREIGN TABLE IF EXISTS foreign_schema.foreign_table_name;

-- Recreate with compatible types:
CREATE FOREIGN TABLE foreign_schema.foreign_table_name (
  id integer,
  name text,  -- Instead of oracle VARCHAR2, use PostgreSQL text
  created_date text,  -- Store Oracle DATE as text, convert in app
  amount numeric,  -- Oracle NUMBER → PostgreSQL numeric
  PRIMARY KEY (id)
)
SERVER remote_server_name
OPTIONS (
  schema_name 'public',
  table_name 'remote_table_name'
);

For oracle_fdw specifically, if a column type cannot be mapped, you can declare a "dummy" column with a compatible type and simply not query it:

-- Skip unsupported columns; they will return NULL if queried:
CREATE FOREIGN TABLE foreign_schema.foreign_table_name (
  id integer,
  name text,
  unsupported_clob text  -- Marked as text but will not be used
)
SERVER oracle_server
OPTIONS (
  schema_name 'SCHEMA',
  table_name 'REMOTE_TABLE'
);

Run simple SELECT queries to confirm the foreign table now works without HV004 errors.

-- Try a simple SELECT:
SELECT * FROM foreign_schema.foreign_table_name LIMIT 1;

-- Test with a WHERE clause:
SELECT * FROM foreign_schema.foreign_table_name WHERE id = 1;

-- Test an INSERT if your setup allows writes:
INSERT INTO foreign_schema.foreign_table_name (id, name) VALUES (999, 'Test');

If queries succeed without HV004, the type mapping is now correct. If you still see HV004 on a different column, repeat steps 2 and 4 for that column.

If many columns had type issues, it may be simpler to drop the entire foreign table and re-run IMPORT FOREIGN SCHEMA. However, note that IMPORT FOREIGN SCHEMA will use the FDW's default type mapping, which might not resolve all compatibility issues.

-- Drop the foreign table:
DROP FOREIGN TABLE IF EXISTS foreign_schema.foreign_table_name;

-- Re-import:
IMPORT FOREIGN SCHEMA public
  LIMIT TO (foreign_table_name)
  FROM SERVER remote_server_name
  INTO foreign_schema;

If the re-imported table still has HV004 issues, it means the FDW cannot automatically convert those types, and you must manually adjust the column definitions as shown in Step 4.