From: | Fujii Masao <masao(dot)fujii(at)gmail(dot)com> |
---|---|
To: | Amit Langote <amitlangote09(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: table partition and column default |
Date: | 2019-12-25 08:40:16 |
Message-ID: | CAHGQGwGFmtPJn+AUCMMLTP26aXG8FxzXMAJfGOqfvTA-5MxQ1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> Fujii-san,
>
> On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > As the document explains, column defaults can be specified separately for
> > each partition. But I found that INSERT via the partitioned table ignores
> > that default. Is this expected behavior or bug?
> >
> > CREATE TABLE test (i INT, j INT) PARTITION BY RANGE (i);
> > CREATE TABLE test1 PARTITION OF test (j DEFAULT 99) FOR VALUES FROM (1) TO (10);
> > INSERT INTO test VALUES (1, DEFAULT);
> > INSERT INTO test1 VALUES (2, DEFAULT);
> > SELECT * FROM test;
> > i | j
> > ---+--------
> > 1 | (null)
> > 2 | 99
> > (2 rows)
> >
> > In the above example, INSERT accessing directly to the partition uses
> > the default, but INSERT via the partitioned table not.
>
> This is as of now expected.
>
> IIRC, there was some discussion about implementing a feature whereby
> partition's default will used for an attribute if it's null even after
> considering the parent table's default, that is, when no default value
> is defined in the parent. The details are at toward the end of this
> thread:
>
> https://www.postgresql.org/message-id/flat/578398af46350effe7111895a4856b87b02e000e.camel%402ndquadrant.com
Thanks for pointing that thread!
As you mentioned in that thread, I also think that this current
behavior (maybe restriction) should be documented.
What about adding the note like "a partition's default value is
not applied when inserting a tuple through a partitioned table."
into the document?
Patch attached.
Regards,
--
Fujii Masao
Attachment | Content-Type | Size |
---|---|---|
default_in_partition.patch | application/octet-stream | 689 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2019-12-25 08:47:38 | Re: table partition and column default |
Previous Message | Kyotaro Horiguchi | 2019-12-25 08:26:30 | Re: [HACKERS] WAL logging problem in 9.4.3? |