Re: table partition and column default

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,
Amit