Re: date format - Mailing list pgsql-general

From Ron
Subject Re: date format
Date
Msg-id 72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com
Whole thread Raw
In response to Re: date format  (Marc Millas <marc.millas@mokadb.com>)
Responses Re: date format
List pgsql-general
On 6/14/23 13:02, Marc Millas wrote:

On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.millas@mokadb.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.

pgsql-general by date:

Previous
From: Marc Millas
Date:
Subject: Re: date format
Next
From: Marc Millas
Date:
Subject: Re: date format