Re: foregin table insert error
От | Adrian Klaver |
---|---|
Тема | Re: foregin table insert error |
Дата | |
Msg-id | 548DD96E.7060906@aklaver.com обсуждение исходный текст |
Ответ на | Re: foregin table insert error (Ed Rahn <edsrahn@gmail.com>) |
Список | pgsql-sql |
On 12/14/2014 08:49 AM, Ed Rahn wrote: > 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) Hmm, the NOT NULL constraint is being checked before the trigger is run. Maybe change the function to look for a dummy value, say -1 and then use that in your INSERT: insert into remote_cache (entry_id, name_id) values(-1, 2,1); > > thanks > Ed > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-sql по дате отправления: