Re: foregin table insert error
От | Ed Rahn |
---|---|
Тема | Re: foregin table insert error |
Дата | |
Msg-id | 548DBFB3.1090304@gmail.com обсуждение исходный текст |
Ответ на | Re: foregin table insert error (Adrian Klaver <adrian.klaver@aklaver.com>) |
Ответы |
Re: foregin table insert error
Re: foregin table insert error |
Список | pgsql-sql |
On 12/14/2014 09:30 AM, Adrian Klaver wrote: > On 12/14/2014 01:13 AM, Ed Rahn wrote: >> Hi, >> I have a foreign table that I'm getting an insert error on: >> >> horsedata=# insert into remote_cache (entry_id, name_id) values(2,1); >> ERROR: null value in column "id" violates not-null constraint >> DETAIL: Failing row contains (null, 1, 2, null). >> CONTEXT: Remote SQL command: INSERT INTO public.cache(id, name_id, >> entry_id, value) VALUES ($1, $2, $3, $4) >> >> >> Here is the remote table client side: >> horsedata=# \d remote_cache >> Foreign table "public.remote_cache" >> Column | Type | Modifiers | FDW Options >> ----------+---------+-----------+------------- >> id | integer | | >> name_id | integer | | >> entry_id | integer | | >> value | integer | | >> Server: home >> FDW Options: (table_name 'cache') >> >> >> And here's cache server side: >> horsedata=# \d cache; >> Table "public.cache" >> Column | Type | Modifiers >> ----------+------------------+---------------------------------------------------- >> >> >> id | integer | not null default >> nextval('cache_id_seq'::regclass) >> name_id | integer | >> entry_id | integer | >> value | double precision | >> Indexes: >> "cache_pkey" PRIMARY KEY, btree (id) >> "cache_name_id_entry_id_key" UNIQUE CONSTRAINT, btree (name_id, >> entry_id) >> "ix_cache_entry_id" btree (entry_id) >> "ix_cache_name_id" btree (name_id) >> >> >> Any suggestions? > > Yes, see here: > > http://www.postgresql.org/message-id/CA+mi_8bfkaFPNPPx6_W_T_0J9OEMSfXQKCDZo=OMJpWWcCKtoA@mail.gmail.com > > I tried something similar using -1 as well as the above: On server I set: create function inc_id_cache() returns trigger as $inc$ begin if NEW.id = NULL then NEW.id := nextval('cache_id_seq'); end if; return NEW; end; $inc$ language plpgsql; create trigger inc before insert on cache for each row execute procedure inc_id_cache(); Now in both cases I get: horsedata=# insert into remote_cache(name_id, entry_id) values (1, 8); ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, 1, 8, null). CONTEXT: Remote SQL command: INSERT INTO public.cache(id, name_id, entry_id, value) VALUES ($1, $2, $3, $4) thanks Ed
В списке pgsql-sql по дате отправления: