Re: date format

On Wed, Jun 14, 2023 at 8:23 PM Marc Millas <marc(dot)millas(at)mokadb(dot)com> wrote:

>
>
>
>
> On Wed, Jun 14, 2023 at 8:15 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> 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?
>>
> 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.
>>
>