table partition and column default

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

table partition and column default

Fujii Masao-2
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.

Regards,

--
Fujii Masao


Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Amit Langote
Fujii-san,

On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Fujii Masao-2
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <[hidden email]> wrote:

>
> Fujii-san,
>
> On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <[hidden email]> 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

default_in_partition.patch (944 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Amit Langote
On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <[hidden email]> wrote:

> On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <[hidden email]> wrote:
> > 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?

Agreed.

> Patch attached.

Thanks for creating the patch, looks good to me.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Fujii Masao-2
On Wed, Dec 25, 2019 at 5:47 PM Amit Langote <[hidden email]> wrote:

>
> On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <[hidden email]> wrote:
> > On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <[hidden email]> wrote:
> > > 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?
>
> Agreed.
>
> > Patch attached.
>
> Thanks for creating the patch, looks good to me.

Thanks for reviewing the patch. Committed!

Regards,

--
Fujii Masao


Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Julien Rouhaud
Fuji-san,

On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <[hidden email]> wrote:

>
> On Wed, Dec 25, 2019 at 5:47 PM Amit Langote <[hidden email]> wrote:
> >
> > On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <[hidden email]> wrote:
> > > On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <[hidden email]> wrote:
> > > > 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?
> >
> > Agreed.
> >
> > > Patch attached.
> >
> > Thanks for creating the patch, looks good to me.
>
> Thanks for reviewing the patch. Committed!

I saw that you only pushed it on master, shouldn't we backpatch it
down to pg10 as this is the declarative partitioning behavior since
the beginning?


Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Amit Langote
On Thu, Dec 26, 2019 at 6:21 PM Julien Rouhaud <[hidden email]> wrote:
> On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <[hidden email]> wrote:
> > Thanks for reviewing the patch. Committed!
>
> I saw that you only pushed it on master, shouldn't we backpatch it
> down to pg10 as this is the declarative partitioning behavior since
> the beginning?

I had meant to reply to this but somehow forgot.

I agree that it might be a good idea to back-patch this down to PG 10.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: table partition and column default

Fujii Masao-4


On 2020/02/04 13:56, Amit Langote wrote:

> On Thu, Dec 26, 2019 at 6:21 PM Julien Rouhaud <[hidden email]> wrote:
>> On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <[hidden email]> wrote:
>>> Thanks for reviewing the patch. Committed!
>>
>> I saw that you only pushed it on master, shouldn't we backpatch it
>> down to pg10 as this is the declarative partitioning behavior since
>> the beginning?
>
> I had meant to reply to this but somehow forgot.
>
> I agree that it might be a good idea to back-patch this down to PG 10.

Back-patched to v10. Thanks Julien and Amit for pointing out this!

Regards,

--
Fujii Masao
NTT DATA CORPORATION
Advanced Platform Technology Group
Research and Development Headquarters