[MASSMAIL]Help with error date_trunc() function. - Mailing list pgsql-sql

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@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@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

--