1

I'm using PostgreSQL version 13.2 and I need to do an insert into a foreign table (Postgres also), that has a sequence generated id, returning the generated id from the foreign table.

I've added the "postgres_fdw" extension, created the server and the user mapping.

When I do:

INSERT INTO public.remote_users (name) VALUES ('username') RETURNING id;

Using the following foreign table definition:

CREATE FOREIGN TABLE public.remote_users
(
    id bigint,
    name character varying(20)
)
SERVER remote
OPTIONS (schema_name 'public', table_name 'users');

I get an error saying that id can't be null (because the fdw builds the remote insert statement using the 'id' column and it has a non-null constraint).

ERROR: null value in column "id" of relation "users" violates not-null constraint DETAIL: Failing row contains (null, username). CONTEXT: remote SQL command: INSERT INTO public.users(id, name) VALUES ($1, $2) RETURNING id SQL state: 23502

Using this foreign table definition:

CREATE FOREIGN TABLE public.remote_users
(
    name character varying(20)
)
SERVER remote
OPTIONS (schema_name 'public', table_name 'users');

I get an error saying that "column "id" does not exist".

Is there any way to make the INSERT INTO public.remote_users (name) VALUES ('username') RETURNING id; statement work?

João Ferreira
  • 191
  • 1
  • 10
  • I think you will have to include the ID in your foreign table definition –  Mar 11 '21 at 12:45

2 Answers2

3

I found a workaround (although it isn't the solution I desired).

Creating a before insert trigger on the remote table to check for null id and replacing by the sequence value, I can use the insert (using the foreign table definition specifying the id column).

More explicitly...

On remote:

CREATE FUNCTION public.users_insert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
  IF (new.id IS NULL) THEN
     new.id = nextval('public.users_id_seq'::regclass);
  END IF;
  RETURN new;
END;
$BODY$;

CREATE TRIGGER insert_tr
    BEFORE INSERT
    ON public.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.users_insert();
João Ferreira
  • 191
  • 1
  • 10
  • `ALTER TABLE [TableName] ADD DEFAULT nextval('public.users_id_seq') FOR [column_name].` Default value isn't necessary a constant. – Martial P May 21 '21 at 06:56
0

Try this query to create table

CREATE FOREIGN TABLE public.remote_users
(
    id serial,
    name character varying(20)
)
SERVER remote
OPTIONS (schema_name 'public', table_name 'users');
Indunil Udayangana
  • 41
  • 1
  • 3
  • Thanks for the opinion @IndunilUdayangana but it doesn't work because it'll create a sequence in the local database and use this sequence instead of the sequence in the remote database. – João Ferreira Mar 11 '21 at 14:36