Reputation: 191
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?
Upvotes: 1
Views: 2208
Reputation: 78
In my case when encountering this error, I had imported the schema from the remote database into my local database. When trying to insert a record, it too raised a constraint error about the primary key. I'm using pgAdmin4, so my solution was to find the foreign table schema I had imported into my local database, right click it to select properties, and deleted the primary key column under the 'Columns' tab. That fixed my issue and I can insert records into the foreign table now from my local database.
Upvotes: 0
Reputation: 41
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');
Upvotes: 0
Reputation: 191
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();
Upvotes: 3