Re: Getting NOT NULL constraint from pg_attribute

On Monday, August 20, 2018, Wu Ivy <ivywuyzl@gmail.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.