Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rsmM1-009CKS-G8 for pgsql-sql@arkaria.postgresql.org; Fri, 05 Apr 2024 16:23:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rsmM0-0029f0-Kn for pgsql-sql@arkaria.postgresql.org; Fri, 05 Apr 2024 16:23:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rsmM0-0029eo-4O for pgsql-sql@lists.postgresql.org; Fri, 05 Apr 2024 16:23:48 +0000 Received: from mail-wr1-x42e.google.com ([2a00:1450:4864:20::42e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rsmLx-000oit-5Z for pgsql-sql@postgresql.org; Fri, 05 Apr 2024 16:23:47 +0000 Received: by mail-wr1-x42e.google.com with SMTP id ffacd0b85a97d-343d1003106so1056970f8f.1 for ; Fri, 05 Apr 2024 09:23:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hoplasoftware-com.20230601.gappssmtp.com; s=20230601; t=1712334224; x=1712939024; darn=postgresql.org; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=vP93SRNDGsv7/uV3Tn6M6SVb7lb4h7pYc19E/jpCFHU=; b=HN5X+8pcEFEaGzxoztUNKauC4QSbufaak4S/0CpXMFHmfW8DgbU7wcNQeFlu1Vhh5O Vmruuuk7lP4+0y/uNqSTri6ISCEgSNRY5P5hiyElScOjuzXmZh9rcxTJReCulhFWevjz w87Gbcu0HbWY8EFvyw1xQcrMdOZYItindXwW1jQqmVaXEWVUZ/qJvd6YgxIsUHVDrtxO tvXkjsCJuE964Fc0CQfhl4zS4IUpE0C9LkLKUcnpigw+XQsrvfGCR7cEifT5WubBf9it mZHtV1tjNGe67KMI5xRnKgKTy6prw7bq0ZZ3kf6ZF4myWd+5geOZ6E+krnNit3nFQpan /gZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712334224; x=1712939024; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=vP93SRNDGsv7/uV3Tn6M6SVb7lb4h7pYc19E/jpCFHU=; b=cLtc2NjcDtL+vQLg+5vmCw7kyd8aTTyqZ6xasDXJTeB5ntGqYUFWyOGyacq3TCk7uW UUWjKIcPdRA/c6dtefvzTB835kzNf0FiZCbsIuhqu5dgRUMeRdZrRfkFjcl8K1MBnzQC cd7GCYkrGGwUWbwqCLRiXnDzQKfhIwQUhDJOF5IXuuKmbaMh8GdexqwzfPBsRyj3iw4S rZUaKd4NKaUGdUev3kpOmjMFEjcYPd8Z45WhKebJL3xuULWVtZ2TUIschIVr/HMphjI6 gMPgjzqd/bebf9KysP3rMLnb0fGXfXu6xXJpqYmdEB4pt/rDRgk6IVmzBRUUzZhFtR2R DFEg== X-Gm-Message-State: AOJu0YxyxoirBl1+XDZxBAmGtF6qHZ7W5YhqFbU0nsRGsO2BgLe3ri2J +Fu+T92dV37veRSxy8w8GZUqjRkFbj+FFX2H++WWXIDwW5jdzFix1fDI4lYhhAWcHBGZxXW8ZVh mSiY= X-Google-Smtp-Source: AGHT+IFkMx1E+hlWgqkkKC2RSJjxzbP5V000sjfq1eeu1w66QA8/ps4ONR7MMpiIcosZpu5E6wiFXA== X-Received: by 2002:adf:f30b:0:b0:343:ba6c:19f1 with SMTP id i11-20020adff30b000000b00343ba6c19f1mr1617473wro.15.1712334223729; Fri, 05 Apr 2024 09:23:43 -0700 (PDT) Received: from [192.168.1.189] (cm-93-156-204-107.telecable.es. [93.156.204.107]) by smtp.gmail.com with ESMTPSA id z13-20020a056000110d00b0034174875850sm2364056wrw.70.2024.04.05.09.23.43 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 05 Apr 2024 09:23:43 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------JPXb0DlYE3JjHglDR3SaOBr4" Message-ID: <425370c4-c278-48fc-a1db-ef5aa980da8e@hoplasoftware.com> Date: Fri, 5 Apr 2024 18:23:42 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: es-ES To: pgsql-sql@postgresql.org From: Miguel Angel Prada Subject: Help with error date_trunc() function. List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------JPXb0DlYE3JjHglDR3SaOBr4 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------JPXb0DlYE3JjHglDR3SaOBr4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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

--------------JPXb0DlYE3JjHglDR3SaOBr4--