public inbox for [email protected]
help / color / mirror / Atom feedFrom: Torsten Förtsch <[email protected]>
To: veem v <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Getting specific partition from the partition name
Date: Fri, 9 Aug 2024 19:35:12 +0200
Message-ID: <CAKkG4_knN0ayoanYSzMuG5PJBkv7q+kBmBJCobxq9kFv24Z-YQ@mail.gmail.com> (raw)
In-Reply-To: <CAB+=1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs=ek5qCgxw@mail.gmail.com>
References: <CAB+=1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs=ek5qCgxw@mail.gmail.com>
If you want to convert your table name into a timestamp, you don't need
substring or similar. This also works:
=# select to_date('table_part_p2024_08_08', '"table_part_p"YYYY"_"MM"_"DD');
to_date
------------
2024-08-08
(1 row)
But as Greg said, your strings are perfectly sortable.
On Thu, Aug 8, 2024 at 9:52 PM veem v <[email protected]> wrote:
> Hi ,
> We are using postgres version 15.4. We have a range partition table and
> the partition naming convention is generated by pg_partman and is something
> like "table_name>_pYYYY_MM_DD".
>
> We have a requirement of extracting specific partitions ordered by the
> date criteria and also do some operations on that specific date. But I am
> struggling and it's not working as expected.I tried something as below but
> it's not working.Can somebody guide me here please.
>
> to_date( substring('table_part_p2024_08_08' from
> '_p(\d{4})_(\d{2})_(\d{2})'), 'YYYY_MM_DD'
> ) < current_date
>
> or is there any ready-made data dictionary which will give us the order of
> the partitions by the date and we can get hold of the specific nth
> partition in that table?
>
> Regards
> Veem
>
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], [email protected], [email protected]
Subject: Re: Getting specific partition from the partition name
In-Reply-To: <CAKkG4_knN0ayoanYSzMuG5PJBkv7q+kBmBJCobxq9kFv24Z-YQ@mail.gmail.com>
* 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