Thread: date format

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 ?

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.

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?
-> one sql line insert as select  using the to_date() function with appropriate format.

--
Born in Arizona, moved to Babylonia.

On Wed, Jun 14, 2023 at 8:15 PM Ron <ronljohnsonjr@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.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?

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

--
Born in Arizona, moved to Babylonia.