Re: NOT NULL constraints on range partition key columns

On Tue, May 16, 2017 at 3:26 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/05/16 4:29, Robert Haas wrote:
>> On Mon, May 15, 2017 at 9:12 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>> Can't we allow NULL to get inserted into the partition (leaf
>>> partition) if the user uses the partition name in Insert statement?
>>
>> That would be terrible behavior - the behavior of tuple routing should
>> match the enforced constraints.
>>

Right and that makes sense.

>>> For root partitions, I think for now giving an error is okay, but once
>>> we have default partitions (Rahila's patch), we can route NULLS to
>>> default partition.
>>
>> Yeah, that's exactly why I think we should make the change Amit is
>> proposing here. If we don't, then we won't be able to accept NULL
>> values even after we have the default partitioning stuff.
>
> Attached is a patch for consideration. There are 2 actually, but maybe
> they should be committed together if we decide do go with this.
>

After your changes in get_qual_for_range(), below comment in that
function needs an update and I feel it is better to update the
function header as well.

/*
* A range-partitioned table does not allow partition keys to be null. For
* simple columns, their NOT NULL constraint suffices for the enforcement
* of non-nullability. But for the expression keys, which are still
* nullable, we must emit a IS NOT NULL expression. Collect them in
* result first.
*/

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com