On Wed, Jun 14, 2023 at 8:23 PM Marc Millas <marc.mil...@mokadb.com> wrote:
> > > > > 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. >> https://www.postgresql.org/docs/current/functions-formatting.html >> > I do know what s in the file, I dont have to guess... > -- >> Born in Arizona, moved to Babylonia. >> >
