Message-ID: <548DBFB3.1090304@gmail.com>
Date: Sun, 14 Dec 2014 11:49:55 -0500
From: Ed Rahn <edsrahn@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.3.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: foregin table insert error
References: <548D54C9.3040005@gmail.com> <548D9F19.9080405@aklaver.com>
In-Reply-To: <548D9F19.9080405@aklaver.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql