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 1phhRQ-0005xV-Mf for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 01:51:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phhRP-0001Dr-Fp for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 01:51:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phhRP-0001Di-3V for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 01:51:03 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phhRL-000571-D7 for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 01:51:02 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-17aa62d0a4aso18271941fac.4 for ; Wed, 29 Mar 2023 18:50:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680141058; h=content-transfer-encoding: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=ubOAxWjNHABIKaN4My92v+6dSvumM0ZjBsAP6MEJbfs=; b=Zftg4s5GKhvjSEZIXsK1ApPRF1YwgpEuc10M/5m5RYwt9aG6TmDvuNplE2+bWdBnFc ecsZBjFOLmbB7bqVy2AIXpdb65xcqRqGzA1hXRVY8D7V8XeqvQ82fP9dvLRLSys2AIBU X0pNbLas1YvyLmLrylzVX9C2LGAhetcKtNmSuV4v2dxcZzfzK6hTU5Vo8ubrVGad0evS AX7EZMlXYzsqRcyVTDZ3e+/KPW/64vcqG6xhjddIowXrcUIIUcY+rZ76Io6VyTXzxV0w 7x8OguRv6WTDk5Uyln3I7+bt4tK6VFQTmhAOfmBScqO+BMO6dNCe9StUjlOCJavqCBgf g8mA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680141058; h=content-transfer-encoding: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=ubOAxWjNHABIKaN4My92v+6dSvumM0ZjBsAP6MEJbfs=; b=wkf2KDlJkikBE2vUHBs12p+TgAnhL2lq2dkan6wyCOTxobzdOcpNn572+G1Up5aWrl h9SBlxnUAmTMmu0zLwHFEYf3FrCe8hbN6nd/rOtx2BdNlatPMrpC6q0AIQYCWhNPpDGA yaDVexG70FpPyXaIyILHH1tBnX3A77lqzlDs98HRr8u8WVLFmkY+tHbZElgdDKvp9yjG teV/2t/Yz62fxS63S9FNHkhhMmxU/gdHjUVQBcFSO61UTePZGOVUAd+ihSnmpvdEVFCJ +aJFCUgDoDlmLvWB8zP5yDwkAoi0WTx224IV2pJ3/r0BBfMtirvsFqVnUUvgWQZ+XS3M ghcA== X-Gm-Message-State: AAQBX9fjOZfMaT8NPHJIcW+iEsC3vtjRsusMIuG9ZwyVRIKPwB5E2x7b PAMz/YTI2cmjW3lhRQsNeMXKohoYSJE= X-Google-Smtp-Source: AK7set+rBlyHJmiIM9cYcixd8V5dBySjfBJfSL/PozuPfWKPNoLBt962RnVY8c9cwY/vR/VXs5pNgQ== X-Received: by 2002:a05:6870:c69a:b0:177:80e4:9a52 with SMTP id cv26-20020a056870c69a00b0017780e49a52mr15968970oab.6.1680141058250; Wed, 29 Mar 2023 18:50:58 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:54b1:168a:9c6e:c2e4? ([2600:1700:bd2c:b810:54b1:168a:9c6e:c2e4]) by smtp.googlemail.com with ESMTPSA id zq35-20020a0568718ea300b0017f647294f5sm2894738oab.16.2023.03.29.18.50.57 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 29 Mar 2023 18:50:58 -0700 (PDT) Message-ID: Date: Wed, 29 Mar 2023 20:50:57 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: COPY and custom datestyles. Or some other technique? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <9aa715ce-9485-638f-6567-e642a50699c4@aklaver.com> From: Ron In-Reply-To: <9aa715ce-9485-638f-6567-e642a50699c4@aklaver.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/29/23 18:31, Adrian Klaver wrote: > On 3/29/23 16:24, Ron wrote: >> Postgresql 13.10 >> >> $ psql -h myhost.example.com -X dba \ >>          -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH >> DELIMITER '|';" >> ERROR:  date/time field value out of range: "2013061914122501" >> CONTEXT:  COPY t_id_master, line 1, column update_timestamp: >> "2013061914122501" >> >> The timestamp format generated by a legacy dbms is YYYYMMDDHHmmSSCC (year >> to centisecond, with no delimiters). >> >> Is there any way to convince Postgresql to import these fields? > > One option: > > 1) Import into staging table as varchar field. I had thought of that. It would be really helpful to be able to reposition columns in tables.  That way, one could: add the new TIMESTAMP column, populate it using to_timestamp(), drop the text column, reposition the TIMESTAMP column to where it "should" be. -- Born in Arizona, moved to Babylonia.