postgres_fdw导入外部schema时自定义类型不存在的解决方法咨询

你遇到的报错是源库存在本地未定义的自定义数据类型导致的，可根据你的使用场景选择以下三种方案解决：

方案1：本地预先创建同源自定义类型（最推荐，适配性最高） #

postgres_fdw导入外部schema时会严格匹配源端类型定义，提前在本地创建和源端完全一致的自定义类型即可正常导入：

• 首先在源端PostgreSQL实例执行命令查询类型定义：
  查year类型：SELECT pg_get_typedef('public.year'::regtype);
  查mpaa_rating类型：SELECT pg_get_typedef('public.mpaa_rating'::regtype);
• 拿到定义后在本地数据库执行创建，以dvdrental示例库的标准定义为例：

  -- 创建year域类型
  CREATE DOMAIN public.year AS integer
    CHECK (VALUE >= 1901 AND VALUE <= 2155);
  
  -- 创建mpaa_rating枚举类型
  CREATE TYPE public.mpaa_rating AS ENUM (
      'G',
      'PG',
      'PG-13',
      'R',
      'NC-17'
  );
  

注意：该方案下类型定义和源端完全一致，不会出现隐式转换导致的查询性能问题或者数据异常，是官方推荐的最优解
创建完成后重新执行原导入命令即可。

方案2：配置postgres_fdw类型映射，跳过自定义类型创建 #

适合不想在本地维护源端自定义类型的场景，要求PostgreSQL 12及以上版本支持：

• 先为你的外部服务器创建类型映射，将源端自定义类型映射到本地内置类型：

  -- 源端public.year映射到本地integer类型
  CREATE TYPE MAPPING FOR public.year
    SERVER dvdrental
    OPTIONS (
      FOREIGN_TYPE_NAME 'public.year',
      LOCAL_TYPE_NAME 'integer'
    );
  
  -- 源端public.mpaa_rating映射到本地text类型
  CREATE TYPE MAPPING FOR public.mpaa_rating
    SERVER dvdrental
    OPTIONS (
      FOREIGN_TYPE_NAME 'public.mpaa_rating',
      LOCAL_TYPE_NAME 'text'
    );
  

• 导入时开启类型映射参数：

  IMPORT FOREIGN SCHEMA "public"
    FROM SERVER dvdrental into "public"
    OPTIONS (import_column_type 'true');
  

注意：类型映射后查询会做隐式类型转换，如果数据不符合本地类型约束可能会抛出异常，适合临时查询场景使用

方案3：低版本PostgreSQL兼容方案 #

如果你的PostgreSQL版本低于12，不支持类型映射功能，可分两步操作：

• 导入时先排除报错的film表：

  IMPORT FOREIGN SCHEMA "public"
    FROM SERVER dvdrental into "public"
    EXCEPT (film);
  

• 手动创建film外部表，自定义列类型：

  CREATE FOREIGN TABLE film (
    film_id integer OPTIONS (column_name 'film_id') NOT NULL,
    title character varying(255) OPTIONS (column_name 'title') COLLATE pg_catalog."default" NOT NULL,
    description text OPTIONS (column_name 'description') COLLATE pg_catalog."default",
    release_year integer OPTIONS (column_name 'release_year'),
    language_id smallint OPTIONS (column_name 'language_id') NOT NULL,
    rental_duration smallint OPTIONS (column_name 'rental_duration') NOT NULL,
    rental_rate numeric(4,2) OPTIONS (column_name 'rental_rate') NOT NULL,
    length smallint OPTIONS (column_name 'length'),
    replacement_cost numeric(5,2) OPTIONS (column_name 'replacement_cost') NOT NULL,
    rating text OPTIONS (column_name 'rating'),
    last_update timestamp without time zone OPTIONS (column_name 'last_update') NOT NULL,
    special_features text[] OPTIONS (column_name 'special_features') COLLATE pg_catalog."default",
    fulltext tsvector OPTIONS (column_name 'fulltext') NOT NULL
  ) SERVER dvdrental
  OPTIONS (schema_name 'public', table_name 'film');
  

内容的提问来源于stack exchange，提问作者CelioxF