Strange query behavior in PostgreSQL

Question

Database is PostgreSQL15.

I am writing a query that will return a list of table partitions I need to trim. My partitions are named as such: table_YYYYDOY, where YYYY is the year and DOY is the Day of the Year. For example, the partition for today will be named mytable_2023283.

In the query, I would like to filter all partitions which are older than 2 days so I can remove them. I get the list of tables and then I join that list to select the ones which are partitions of my main table. The sample set up is included in the db-fiddle at this link:

https://www.db-fiddle.com/#&togetherjs=QLQyTaPQkO

The query is as follows:

select  t.table_schema as schema_name,
        t.table_name,
        right(t.table_name, 7)::int8 as partition_postfix,
        (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8 as threshold,
        right(t.table_name, 7)::int8 - (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8 as diff
    from information_schema.tables t
    join pg_inherits i on i.inhrelid = (t.table_schema || '.' || t.table_name)::regclass
    where t.table_schema = 'public'
--      and right(t.table_name, 7)::int8 < (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8;

Everything seems to be fine - but when I try and include any form of that calculation in the WHERE clause the query fails with Query Error: error: invalid input syntax for type bigint: "ref_sys".

It seems the system tables which are not partitioned are included in the list as soon as there's a WHERE. I tried putting this in a CTE and operating on the "diff" field after - that does not work either.

Can anyone explain to me what is going on here?

Answer 1

PostgreSQL takes the liberty of evaluating WHERE conditions before it joins the tables. To prevent that, use a materialized CTE:

WITH cte AS MATERIALIZED (
   SELECT ...
   FROM information_schema.tables t
      JOIN pg_inherits i ON ...
)
SELECT ...
FROM cte
WHERE ...

Then PostgreSQL will evaluate the join before the WHERE condition, since a materialized CTE is an optimizer barrier.