Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9V1N-0007sj-O5 for pgsql-general@arkaria.postgresql.org; Wed, 14 Jun 2023 18:15:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9V1L-0002d4-PQ for pgsql-general@arkaria.postgresql.org; Wed, 14 Jun 2023 18:15:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9V1L-0002cs-Bj for pgsql-general@lists.postgresql.org; Wed, 14 Jun 2023 18:15:03 +0000 Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q9V1G-002NOw-BU for pgsql-general@lists.postgresql.org; Wed, 14 Jun 2023 18:15:02 +0000 Received: by mail-yb1-xb2a.google.com with SMTP id 3f1490d57ef6-bd20beffda6so943964276.1 for ; Wed, 14 Jun 2023 11:14:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686766496; x=1689358496; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=7B1mcFMpIcBah8rtZDBSwpPRhcoD/b1p8udtBvuDKIo=; b=XsRK3JrqYnA8Ey5TI6k938x+TSTIIECHHRXuaZgttehEJxJeoTbQnkopNevdG4CLQq ExllF9O5cfKQlvF9yTRTVND1SUGsIMPJbV9wR/IZOYxFbk360IxgT5EdtGy4tiiNP+Zp uYZG+kZwRdZhoqR9xfZqRRwCRBSLLigVpJ75KB5AeA8jJ6uWwe44laDcSAmZ9iHy63Af 08tCySOf0zun0DNjL/gZidsZ/iGd5aOwBzt9leZ3fU4PoEk2M8FpGMsSs56NArbyLpXY R7j+s6wFHuRRz6X0Ezv4gLs8yFIXUb6X/CHTLM0uHN7NUTbF32In9wu8r/psl/3okODT S0/g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686766496; x=1689358496; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=7B1mcFMpIcBah8rtZDBSwpPRhcoD/b1p8udtBvuDKIo=; b=MW1huIIukQDOv48hS0kyHQcw9n4BiLhZGsS524HT6pXexQYnthF29DjpZBy/nSMhYz BkiPqE0J6oRB1uQ4uZ9mV99VkKSgN+wVSv4adxmvoV6Q8VN5QErJKhDvenrDGBnJT7wZ QIBuMzS6YSTVvJ0wGgjdI4Omz0SFZERrRtGgygis7ofG1PjeQPgYAEj+bRHxpdDkQ3G1 olyGoLV7OsSaB1hibOdmITgdBqqHHDATWYiF8SAmIdV4uAzkbDZyixY/Uy0Vu1qrSEXp tAa4GWRX+qSLCRgMd4SvlJI9hvoEGcOd59s1P8l/4oWISRSg9vNRm6rHc5aXGr8AE60j tDTw== X-Gm-Message-State: AC+VfDydmaM0A9dHSmCJ6K7kMc6Jqisv0QGFk2aMlHJxknvN3cEJIZfE GFRN1b9ROkFd+WhREiQq3fu6t+HUisI= X-Google-Smtp-Source: ACHHUZ6XC4AT6RM+IILW0o7RHsQfnzCF3O/orMu2rW3rcXk/NML4DVHu/mHzUo0rVzoned2ATk4eOg== X-Received: by 2002:a25:f512:0:b0:bc6:3fbc:1180 with SMTP id a18-20020a25f512000000b00bc63fbc1180mr2913366ybe.41.1686766495870; Wed, 14 Jun 2023 11:14:55 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:681b:2201:b20:efc0? ([2600:1700:bd2c:b810:681b:2201:b20:efc0]) by smtp.googlemail.com with ESMTPSA id e71-20020a25694a000000b00bd586162582sm744149ybc.53.2023.06.14.11.14.55 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 14 Jun 2023 11:14:55 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------v9x6bX6JSUI2W3EVZjTSUVLq" Message-ID: <72f9bd35-43ef-0fdb-2bb8-339e585a5375@gmail.com> Date: Wed, 14 Jun 2023 13:14:55 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: date format Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------v9x6bX6JSUI2W3EVZjTSUVLq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 6/14/23 13:02, Marc Millas wrote: > > On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston > wrote: > > On Wed, Jun 14, 2023 at 9:42 AM Marc Millas > 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. --------------v9x6bX6JSUI2W3EVZjTSUVLq Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------v9x6bX6JSUI2W3EVZjTSUVLq--