postgres_fdw extension not interpreting right data types

Question

I'm trying to gather some experience with ETL processes and for this reason I'm looking for ways to transfer data from one place to the other. Right now, I want to get a better hang of the postgres_fdw extension, because, if I understood it correctly, it allows me to import a whole schema with a single command.

IMPORT FOREIGN SCHEMA "public"
    FROM SERVER dvdrental into "public";

I already followed the whole documentation with installing and using the fwd extension and manual import from other tables worked fine. However, when I try this "bulk import", for one particular table I get the following error:

ERROR:  type "public.year" does not exist
LINE 5:   release_year public.year OPTIONS (column_name 'release_yea...
                       ^
QUERY:  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 public.year 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 public.mpaa_rating 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');
CONTEXT:  importing foreign table "film"
SQL state: 42704

When I look at line 5, I get why postgres is throwing the error. It's trying to create the column release_year with the type public.year. However, that's not a valid type (like INTEGER) and therefore I get the error.

Still, how can I fix it? I mean, how can I override the data type that postgres is trying to set for this specific column?

@a_horse_with_no_name Thanks! That did it for me. I had to also create the type mpaa_rating used by the film table, but other than this it worked fine. I'd politely ask you to write a more general response, so that I can mark it as an answer! — CelioxF, Nov 11 '21 at 10:37

score 3 · Answer 1 · 2021-11-11T10:58:02.750

The table film uses a domain for the year column and an enum for the rating column.

IMPORT FOREIGN SCHEMA only imports tables and views, so it doesn't import domains, enums and other things.

You need to create them manually before importing the schema.

CREATE TYPE public.mpaa_rating AS ENUM (
    'G',
    'PG',
    'PG-13',
    'R',
    'NC-17'
);

CREATE DOMAIN public.year AS integer
    CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));

postgres_fdw extension not interpreting right data types

1 Answers1