public inbox for [email protected]
help / color / mirror / Atom feedFrom: Miguel Angel Prada <[email protected]>
To: [email protected]
Subject: Help with error date_trunc() function.
Date: Fri, 5 Apr 2024 18:23:42 +0200
Message-ID: <[email protected]> (raw)
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
--
Miguel Ángel Prada
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Help with error date_trunc() function.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox