pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: date format
Date: Wed, 14 Jun 2023 13:14:55 -0500
Message-ID: <72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com> (raw)
In-Reply-To: <CADX_1aatzh13gdbi=rV11xcb12mewVQMrzfixhJQcyJ=mvSA6g@mail.gmail.com>
References: <CADX_1aafydAJ6RA=nGUkYQYKGJbwWssbgFa8gPiD6+1GhW1Rmg@mail.gmail.com>
	<CAKFQuwa6mQaHigAZpcwTTA5RTTnB3yZfpJZnriJdbVDHOK5Eeg@mail.gmail.com>
	<CADX_1aatzh13gdbi=rV11xcb12mewVQMrzfixhJQcyJ=mvSA6g@mail.gmail.com>

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.

view thread (31+ messages)  latest in thread

Message-ID: <72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com>
Permalink:  ../72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com/
Also on:    postgresql.org/message-id/72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: date format
  In-Reply-To: <72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com>
  References: <CADX_1aafydAJ6RA=nGUkYQYKGJbwWssbgFa8gPiD6+1GhW1Rmg@mail.gmail.com> <CAKFQuwa6mQaHigAZpcwTTA5RTTnB3yZfpJZnriJdbVDHOK5Eeg@mail.gmail.com> <CADX_1aatzh13gdbi=rV11xcb12mewVQMrzfixhJQcyJ=mvSA6g@mail.gmail.com> <72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox