table partition and column default
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
Fujii-san,
On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <masao.fujii@gmail.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:
/messages/by-id/578398af46350effe7111895a4856b87b02e000e.camel@2ndquadrant.com
Thanks,
Amit
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> wrote:
Fujii-san,
On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <masao.fujii@gmail.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:/messages/by-id/578398af46350effe7111895a4856b87b02e000e.camel@2ndquadrant.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
Attachments:
default_in_partition.patchapplication/octet-stream; name=default_in_partition.patchDownload+2-0
On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <masao.fujii@gmail.com> wrote:
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> 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:/messages/by-id/578398af46350effe7111895a4856b87b02e000e.camel@2ndquadrant.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
On Wed, Dec 25, 2019 at 5:47 PM Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <masao.fujii@gmail.com> wrote:
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> 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:/messages/by-id/578398af46350effe7111895a4856b87b02e000e.camel@2ndquadrant.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
Fuji-san,
On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <masao.fujii@gmail.com> wrote:
On Wed, Dec 25, 2019 at 5:47 PM Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <masao.fujii@gmail.com> wrote:
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> 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:/messages/by-id/578398af46350effe7111895a4856b87b02e000e.camel@2ndquadrant.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?
On Thu, Dec 26, 2019 at 6:21 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <masao.fujii@gmail.com> 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
On 2020/02/04 13:56, Amit Langote wrote:
On Thu, Dec 26, 2019 at 6:21 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <masao.fujii@gmail.com> 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