Re: Postgres CTE issues

I changed the not null constraint but I dont insert the FK id (it is null)

drop table C;

DROP TABLE

s=> create table C (

"id" SERIAL PRIMARY KEY,

name varchar(40)

, b_id integer references B(id)DEFERRABLE INITIALLY DEFERRED);

CREATE TABLE

s=> insert into A (var1, var2) values ('Hello1', 'World1');

INSERT 0 1

s=> select * from C;

1 | World1 |

On Tue, May 26, 2015 at 10:56 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
>
> On Tue, May 26, 2015 at 10:40 AM, Shekar Tippur <ctippur(at)gmail(dot)com> wrote:
>
>> Marc,
>>
>> I have changed the table C:
>>
>> create table C (
>>
>> "id" SERIAL PRIMARY KEY,
>>
>> name varchar(40)
>>
>> , b_id integer references B(id) DEFERRABLE INITIALLY DEFERRED NOT NULL);
>>
>>
>> I still get the same error:
>>
>> insert into A (var1, var2) values ('Hello1', 'World1');
>>
>> ERROR: null value in column "b_id" violates not-null constraint
>>
>> DETAIL: Failing row contains (2, World1, null).
>>
>> CONTEXT: SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2,
>> b_id)"
>>
>> PL/pgSQL function fn_test() line 17 at SQL statement
>>
>
> ​Because you cannot defer a NOT NULL constraint.
>
> ​http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html
>
> David J.
>
>