From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: date format |
Date: | 2023-06-14 18:14:55 |
Message-ID: | 72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 6/14/23 13:02, Marc Millas wrote:
>
> On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc(dot)millas(at)mokadb(dot)com>
> wrote:
>
> Hi,
>
> I would like to load data from a file via file_fdw or COPY.. its a
> postgres 14 cluster
>
> but.. One date (timestamp) column is written french order and
> another column is written english order. Data comes from a state
> owned entity so asking for a normalization may take ages.
>
> obviously I could load as char and then apply an appropriate
> transformation. no pb.
> But is there a direct way to do this ?
>
>
> Probably no - casting formats via locale cannot be specified at that
> scope when using copy. Either the cast for a given single setting
> produces the correct result or it doesn't. If you need a custom cast
> like this you have to get away from COPY first. Usually that is best
> done after importing data to a temporary table as text.
>
> David J.
>
>
> So, creating a foreign table with varchar type, and then doing the insert
> as select with the appropriate format.. clear.
> somewhat sad as it was a one step process with the former oracle db we get
> rid off.
How did Oracle know what format the date was in?
In Postgresql, could you write a simple anonymous procedure that reads the
file_fdw table records, does the conversion and then inserts into the
destination table?
--
Born in Arizona, moved to Babylonia.
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Millas | 2023-06-14 18:23:06 | Re: date format |
Previous Message | Marc Millas | 2023-06-14 18:02:01 | Re: date format |