Re: Getting NOT NULL constraint from pg_attribute

Thanks for the response. Really appreciate it!

Regards,
Ivy

2018-08-20 10:40 GMT-07:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Monday, August 20, 2018, Wu Ivy <ivywuyzl(at)gmail(dot)com> wrote:
>>
>> Thanks for the quick respond.
>> Why are SELECT query never marked nullable? For nullable columns, when I
>> call SPI_getvalue(), the result (in char*) is NULL. I don’t think I’m too
>> clear on the definition of *attnotnull*. Can you give me a example in
>> which the tupleTable is can be marked nullable?
>> Also, is there any other ways to get nullability of each column while
>> getting the data from SPI_cursor_fetch? The only way I can think is to call
>> another separate command to query the table schema, but it will be in a
>> separate transaction in that case.
>>
>
> Basically the nullability property is used by the planner for optimization
> during the joining of physical tables. As soon as you try outputting
> columns the ability to enforce not null goes away because of, in
> particular, outer joins. While some changes could maybe be made the
> cost-benefit to do so doesn't seem favorable.
>
> David J.
>
>