Re: Issue with partitioning - legitimate insert fails with no partition message

a bit more info:

this is on PostgreSQL 14

It seems that the range for partitions is inclusive of the starting
value and exclusive of the ending value, maybe this is documented and I
missed it?

I assume this because this insert also fails, using the ending value of
the second partition:

insert into localdata2
values (5000000, now(), 21, 55, 'description goes here');
ERROR:  no partition of relation "localdata2" found for row
DETAIL:  Partition key of the failing row contains (local_id) = (5000000).

On 9/30/22 09:16, Sbob wrote:
>
> All;
>
>
> I created a partitioned table (using declarative partitioning) and I
> am seeing an error inserting a row, the db claims there is no
> partition for my insert but as far as I can thell there is.
>
>
> Here is my setup:
>
>
> CREATE TABLE localdata2 (
>            local_id    bigserial ,
>            logts       timestamp without time zone,
>            mintemp     int,
>            maxtemp     int,
>            description     text
>            ) PARTITION BY RANGE (local_id) ;
>
> CREATE TABLE remote1_data1 PARTITION OF localdata2
>                FOR VALUES FROM ('1') TO ('500000') ;
>
> CREATE TABLE remote2_data1 PARTITION OF localdata2
>                FOR VALUES FROM ('500001') TO ('5000000') ;
>
>
> \d+ localdata2
>                                                                 Partitioned
> table "public.localdata2"
>   Column    |            Type             | Collation | Nullable |
>                   Default                    | Storage  | Compression
> | Stats target | Description
> -------------+-----------------------------+-----------+----------+----------------------------------------------+----------+-------------+--------------+-------------
>
> local_id    | bigint                      |           | not null |
> nextval('localdata2_local_id_seq'::regclass) | plain    |
>             |              |
> logts       | timestamp without time zone |           |          |
>                                              | plain    |
>             |              |
> mintemp     | integer                     |           |          |
>                                              | plain    |
>             |              |
> maxtemp     | integer                     |           |          |
>                                              | plain    |
>             |              |
> description | text                        |           |          |
>                                              | extended |
>             |              |
> Partition key: RANGE (local_id)
> Partitions: remote1_data1 FOR VALUES FROM ('1') TO ('500000'),
>            remote2_data1 FOR VALUES FROM ('500001') TO ('5000000')
>
>
>
>
> insert into localdata2
> values (500000, now(), 21, 55, 'description goes here');
> ERROR:  no partition of relation "localdata2" found for row
> DETAIL:  Partition key of the failing row contains (local_id) = (500000).
>
>
> The remote1_data1 partition should allow the value 500000 for the
> local_id but it is failing...
>
>
> Thoughts?
>
>
> Thanks in advance
>
>
>
>
>
>