Re: Getting NOT NULL constraint from pg_attribute

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.