Re: partitioning

On Tue, 24 Oct 2023 at 10:39, Torsten Förtsch <tfoertsch123(at)gmail(dot)com> wrote:
> Then I added this constraint to the small table:
>
> ALTER TABLE original_small_table
> ADD CONSTRAINT partition_boundaries
> CHECK((false, '-infinity')<=(is_sold, purchase_time)
> AND (is_sold, purchase_time)<(false, 'infinity'))
> NOT VALID;
>
> And validated it.
>
> When this is being attached as a partition, I still can see the process is reading the entire table.

I'm not seeing any handling of RowCompareExpr in predtest.c, so it
appears your row comparisons can't be used to prove that the CHECK
constraint implies the partition constraint.

> What am I missing? What should the check constraint look like in my case to match the partition constraint?

The following should work:

ALTER TABLE original_small_table ADD CONSTRAINT partition_boundaries
CHECK(not is_sold and purchase_time >= '-infinity' and purchase_time <
'infinity');

David