Help with error date_trunc() function.

Hello everyone.

I would need help to know what could be happening to cause the error
when using the /date_trunc/ function.

I have the following table partitioned by month_year (MM_YYYY)

/postgres=# select tablename from pg_tables where tablename like 'test%';
      tablename
----------------------
 test_mensual
 test_mensual_01_2022
 test_mensual_02_2024
 test_mensual_03_2022
 test_mensual_04_2024
 test_mensual_11_2024
(6 rows)
/

From the name of table partitions, I want to extract the MM_YYYY and
with the following query, I want to count the number of monthly
partitions that are less than a certain date...

/   SELECT count(1)
   FROM (
               SELECT relname,to_date(right
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15
month') as fecha_max
               FROM pg_catalog.pg_inherits
                     INNER JOIN pg_catalog.pg_class ON
(pg_inherits.inhrelid = pg_class.oid)
                     INNER JOIN pg_catalog.pg_namespace ON
(pg_class.relnamespace = pg_namespace.oid)
                  WHERE inhparent = 'test_mensual'::regclass
                  ORDER BY 2
      ) as parts
               WHERE
               date_trunc ('month',fecha_part) < date_trunc
('month',fecha_max)
/

When I run the above query on a PG 12.6 the following error occurs:

postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

/
postgres=#    SELECT count(1)
postgres-#    FROM (
postgres(#                SELECT relname,to_date(right
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15
month') as fecha_max
postgres(#                FROM pg_catalog.pg_inherits
postgres(#                      INNER JOIN pg_catalog.pg_class ON
(pg_inherits.inhrelid = pg_class.oid)
postgres(#                      INNER JOIN pg_catalog.pg_namespace ON
(pg_class.relnamespace = pg_namespace.oid)
postgres(#                   WHERE inhparent = 'test_mensual'::regclass
postgres(#                   ORDER BY 2
postgres(#       ) as parts
postgres-#                WHERE
postgres-#                date_trunc ('month',fecha_part) < date_trunc
('month',fecha_max)  ;
ERROR:  invalid value "cc" for "MM"
DETAIL:  Value must be an integer./

However, if I run the same query on a PG 15, it runs without a problem.

/postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
/

/
/

/postgres=#    SELECT count(1)
   FROM (
               SELECT relname,to_date(right
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15
month') as fecha_max
               FROM pg_catalog.pg_inherits
                     INNER JOIN pg_catalog.pg_class ON
(pg_inherits.inhrelid = pg_class.oid)
                     INNER JOIN pg_catalog.pg_namespace ON
(pg_class.relnamespace = pg_namespace.oid)
                  WHERE inhparent = 'test_mensual'::regclass
                  ORDER BY 2
      ) as parts
               WHERE
               date_trunc ('month',fecha_part) < date_trunc
('month',fecha_max)  ;
 count
-------
     2
(1 row)
/

/postgres=#    SELECT *
   FROM (
               SELECT relname,to_date(right
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15
month') as fecha_max
               FROM pg_catalog.pg_inherits
                     INNER JOIN pg_catalog.pg_class ON
(pg_inherits.inhrelid = pg_class.oid)
                     INNER JOIN pg_catalog.pg_namespace ON
(pg_class.relnamespace = pg_namespace.oid)
                  WHERE inhparent = 'test_mensual'::regclass
                  ORDER BY 2
      ) as parts
               WHERE
               date_trunc ('month',fecha_part) < date_trunc
('month',fecha_max)  ;
       relname        | fecha_part |      fecha_max
----------------------+------------+---------------------
 test_mensual_01_2022 | 2022-01-01 | 2023-01-05 00:00:00
 test_mensual_03_2022 | 2022-03-01 | 2023-01-05 00:00:00
(2 rows)
/

The two postgres instances (PG.12 and PG.15) are running on the same
test server.

/[postgres(at)multipgsrv1 ~]$ uname -a
Linux multipgsrv1 3.10.0-1160.95.1.el7.x86_64 #1 SMP Mon Jul 24 13:59:37
UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
[postgres(at)multipgsrv1 ~]$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)/

I think the problem may be when executing the function "date_trunc
('month',fecha_part)" which is performing an incorrect transformation,
because if I execute the query without the date_trunc. Finishes without
problem.

/postgres=# SELECT *
   FROM (
               SELECT relname,to_date(right
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15
month')::date as fecha_max
               FROM pg_catalog.pg_inherits
                     INNER JOIN pg_catalog.pg_class ON
(pg_inherits.inhrelid = pg_class.oid)
                     INNER JOIN pg_catalog.pg_namespace ON
(pg_class.relnamespace = pg_namespace.oid)
                  WHERE inhparent = 'test_mensual'::regclass
                  ORDER BY 2
      ) as parts
               WHERE 1=1;
       relname        | fecha_part | fecha_max
----------------------+------------+------------
 test_mensual_01_2022 | 2022-01-01 | 2023-01-05
 test_mensual_03_2022 | 2022-03-01 | 2023-01-05
 test_mensual_02_2024 | 2024-02-01 | 2023-01-05
 test_mensual_04_2024 | 2024-04-01 | 2023-01-05
(4 rows)/

Can you help me find out what the problem and why this is happening?

Thank you so much.

Gretting1

--

Miguel Ángel Prada