[MASSMAIL]Help with error date_trunc() function.
От | Miguel Angel Prada |
---|---|
Тема | [MASSMAIL]Help with error date_trunc() function. |
Дата | |
Msg-id | 425370c4-c278-48fc-a1db-ef5aa980da8e@hoplasoftware.com обсуждение исходный текст |
Ответы |
Re: Help with error date_trunc() function.
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
--
В списке pgsql-sql по дате отправления: