Question 303960 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Issue with Postgres FDW Extension: Error importing foreign schema for join queries

I am encountering an error while attempting to use the Postgres FDW extension to import a foreign schema from the same database server for performing join queries. Below, I have provided a snapshot of my pgAdmin for better clarity, along with a sample table creation script:

-- Create the enumerated type
CREATE TYPE UserType AS ENUM ('INTERNAL', 'EXTERNAL');

-- Create the User table using the enumerated type
CREATE TABLE "users" (
    id          SERIAL PRIMARY KEY,
    type        UserType,
    created_at  TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

To import the above database and its public schema into the "akil" database, I am using the following code:

CREATE SCHEMA temp_0_schema;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER temp_0_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'temp_0', host 'localhost', port '5432');

CREATE USER MAPPING FOR CURRENT_USER
SERVER temp_0_server
OPTIONS (user 'akil', password '');

IMPORT FOREIGN SCHEMA public
FROM SERVER temp_0_server
INTO temp_0_schema;

However, I am encountering the following error:

ERROR:  type "public.usertype" does not exist
LINE 3:   type public.usertype OPTIONS (column_name 'type'),
               ^
QUERY:  CREATE FOREIGN TABLE users (
  id integer OPTIONS (column_name 'id') NOT NULL,
  type public.usertype OPTIONS (column_name 'type'),
  created_at timestamp without time zone OPTIONS (column_name 'created_at')
) SERVER temp_0_server
OPTIONS (schema_name 'public', table_name 'users');
CONTEXT:  importing foreign table "users" 

SQL state: 42704

I am having difficulty understanding the nature of the error and would appreciate any insights into what might be causing this issue.

enter image description here

Tags:

2

Answers


  1. 0

    The error means that you forgot to create usertype in the local database. Without that, how should the foreign table be defined? See what the documentation has to say:

    It is generally recommended that the columns of a foreign table be declared with exactly the same data types, and collations if applicable, as the referenced columns of the remote table. Although postgres_fdw is currently rather forgiving about performing data type conversions at need, surprising semantic anomalies may arise when types or collations do not match, due to the remote server interpreting query conditions differently from the local server.

    Login or Signup to reply.
  2. 0

    It’s further down in the doc:

    If the remote tables to be imported have columns of user-defined data types, the local server must have compatible types of the same names.

    Which means you need to set up matching user-defined types separately, as the extension doesn’t handle that. Check the definition of public.usertype on your foreign server and define it the same one locally: in psql client dT meta-command displays the type definition, while dt can tell you what types a given table is defined with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search