On Mon, May 15, 2017 at 11:46 AM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > Starting a new thread to discuss the proposal I put forward in [1] to stop > creating explicit NOT NULL constraint on range partition keys that are > simple columns. I said the following: > > On 2017/05/12 11:20, Robert Haas wrote: >> On Thu, May 11, 2017 at 10:15 PM, Amit Langote >> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote: >>> On 2017/05/12 10:42, Robert Haas wrote: >>>> Actually, I think that not supporting nulls for range partitioning may >>>> have been a fairly bad decision. >>> >>> I think the relevant discussion concluded [1] that way, because we >>> couldn't decide which interface to provide for specifying where NULLs are >>> placed or because we decided to think about it later. >> >> Yeah, but I have a feeling that marking the columns NOT NULL is going >> to make it really hard to support that in the future when we get the >> syntax hammered out. If it had only affected the partition >> constraints that'd be different. > > So, adding keycol IS NOT NULL (like we currently do for expressions) in > the implicit partition constraint would be more future-proof than > generating an actual catalogued NOT NULL constraint on the keycol? I now > tend to think it would be better. Directly inserting into a range > partition with a NULL value for a column currently generates a "null value > in column \"%s\" violates not-null constraint" instead of perhaps more > relevant "new row for relation \"%s\" violates partition constraint". > That said, we *do* document the fact that a NOT NULL constraint is added > on range key columns, but we might as well document instead that we don't > currently support routing tuples with NULL values in the partition key > through a range-partitioned table and so NULL values cause error. >
Can't we allow NULL to get inserted into the partition (leaf partition) if the user uses the partition name in Insert statement? 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. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
