date format

Can you use a CASE statement? The real issue with date conversion is
not knowing if a value of 02-03-2023 is mm-dd-yyyy or dd-mm-yyyy.

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 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.

Marc

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?

--
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.

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

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

I do know what s in the file, I dont have to guess...

On 6/14/23 11:23, Marc Millas wrote:

Sort of like?:

https://pgloader.readthedocs.io/en/latest/ref/csv.html

date format

column-name [date format 'YYYY-MM-DD HH24-MI-SS.US']

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 2023-06-14 at 20:02 +0200, Marc Millas wrote:

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.

It can be a one-step process, if you create a view on the table that uses
to_date() to convert the column to a "date".

Yours,
Laurenz Albe