On 6/14/23 13:02, Marc Millas wrote:
On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston
<david.g.johns...@gmail.com> wrote:
On Wed, Jun 14, 2023 at 9:42 AM Marc Millas <marc.mil...@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.
