On Wed, Jun 14, 2023 at 8:15 PM Ron <ronljohnso...@gmail.com> wrote:
> 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? > when you describe the external file you describe the field format one by one. > > 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? > -> one sql line insert as select using the to_date() function with > appropriate format. > > -- > Born in Arizona, moved to Babylonia. >
