Re: bugs or my fault

hallo,

from the document, I think the determinant of each row in the partition is
the residual value (partition key divided by the specified modulus)

So I think the value that will come out is not what I have listed.

The value that should come out like below ( as in the mods column )

SELECT *,(id % 3)as mods FROM myschema.customers;

id status arr mods
2 "RECURRING" 20 2
4 "REACTIVATED" 144 1
3 "EXPIRED" 38 0
1 "ACTIVE" 100 1

table cust0
id = 3

table cust1
id = 4,1

table cust2
id = 2

best regards

Yudianto

On Wed, Sep 15, 2021 at 5:16 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:

> On Wed, Sep 15, 2021 at 7:08 PM Yudianto Prasetyo <mr(dot)yudianto(at)gmail(dot)com>
> wrote:
> >
> > sorry I miss the query
> >
> > select * from myschema.cust0;
> >
> > On Wed, Sep 15, 2021 at 1:28 PM Yudianto Prasetyo <mr(dot)yudianto(at)gmail(dot)com>
> wrote:
> >>
> >> Hello,
> >>
> >> can you explain if this is a bug or my fault ?
> >>
> >> I'm making a simple example of hash partition
> >>
> >> CREATE TABLE myschema.customers(
> >> id int,
> >> status text,
> >> arr numeric
> >> ) PARTITION BY HASH(id);
> >>
> >> CREATE TABLE myschema.cust0 PARTITION OF myschema.customers
> >> FOR VALUES WITH (modulus 3, remainder 0);
> >>
> >> CREATE TABLE myschema.cust1 PARTITION OF myschema.customers
> >> FOR VALUES WITH (modulus 3, remainder 1);
> >>
> >> CREATE TABLE myschema.cust2 PARTITION OF myschema.customers
> >> FOR VALUES WITH (modulus 3, remainder 2);
> >>
> >> INSERT INTO myschema.customers VALUES (1,'ACTIVE',100),
> (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
> >>
> >> when I run the query, the result is:
> >>
> >> 2 "RECURRING" 20
> >> 4 "REACTIVATED" 144
> >>
> >> shouldn't be
> >>
> >> 3 "EXPIRED" 38
> >>
> >> i run this in version
> >>
> >> PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit
> >>
> >> Please help me
>
> I could be wrong but IIRC I have been tripped up by this before. The
> docs [1] says "Each partition will hold the rows for which the hash
> value of the partition key divided by the specified modulus will
> produce the specified remainder.". Perhaps you were expecting it to do
> the do modulus on the key, but not on the hash value of the key?
>
> ------
> [1] https://www.postgresql.org/docs/13/ddl-partitioning.html
>
> Kind Regards,
> Peter Smith.
> Fujitsu Australia.
>