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 1phiBp-00014P-VO for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 02:39:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phiBo-0005k7-8l for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 02:39:00 +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 1phiBn-0005jy-S7 for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 02:38:59 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phiBl-0003Nk-1c for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 02:38:59 +0000 Received: by mail-oi1-x236.google.com with SMTP id bm2so13206166oib.4 for ; Wed, 29 Mar 2023 19:38:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680143935; 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=WOW9+GOAnfVlaC2HAQyhWSf/JVTrKF9ExMZF3H6Eblg=; b=n61xDUHhMp3mdZ0uAyf9BHFXHkEq3eqH1ePI265EuBazZwbP+l9YNFv9jga0sUPmke i8Aa2pExFGb5PH78kCsDdnvxOS68MdQer63vltuhP/G9gFuBMeg2bW7y7O57rrJHjkdi 0OYbApjXpwPAxEmL8oxtIIL9EDLmWKBp+v3w/vyv62q2PNtyuWvbZafYuRdbzLgAIbXq rmETKyhSd3bLBM0B2BkSdgkH3CtHR79eeNPrWbzH5VlTnuaB6d3DERYqnIR0zBrAXFaz lCy5Cazn5EUdvcp9BFB73+IN964B5PeLR8Rm3VrhblOxUdOrZEvNbDf3kUk1+6EUZ5lz vvqw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680143935; 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=WOW9+GOAnfVlaC2HAQyhWSf/JVTrKF9ExMZF3H6Eblg=; b=ZR1xvv5RT/FJvTMk0vZvGiu+eI7NFGhqJPfevkw9bAQLCbPClckuHf32Vui0zxRxs8 ss46gmqEv0CN02tj6mxDNRG/oHF8hddoroovgcfsAYOq2KLfSjSD/UZMhW2NMQGFrfc3 K9s4WeChmPJKv7tpoN5jjNrtAUFnvILVeCq5SNpl2GdIKX5/TlED0ScUV1N5ijAE8iES Rtxw258boxV1sipBbtc0fepe+Q456JqUho+Rky4GJ8TZuN6NqTY+/iotVlirfOImorqT xshkBVTXcxPaGz5KwYl3Yi5EuiABJfSsQlSLtaRE6PH6LES0nqiB01WGGJyKVZtTarWG 4HDA== X-Gm-Message-State: AO0yUKW/57/FYK0ECfJB++j7A5fS0nNTGPiVi1d8CKXRbcnlcgONAMOY 97tTkxr8Vv4wIm5tj37jHl08Eood76I= X-Google-Smtp-Source: AK7set9x8gpM2wEjLppYgDYUzfmjTBGSaBCQeY0sz5k5zImt4z8ZSgC1c1mKC9wFnBsbqMB+NX3Ukw== X-Received: by 2002:a54:4795:0:b0:384:3e65:d0dd with SMTP id o21-20020a544795000000b003843e65d0ddmr9614904oic.41.1680143934827; Wed, 29 Mar 2023 19:38:54 -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 r84-20020a4a3757000000b0053b592c5e24sm10230539oor.3.2023.03.29.19.38.54 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 29 Mar 2023 19:38:54 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------DmQeN0HimsOMexvCRsbpBZ50" Message-ID: <9c9380da-dcf9-7159-a59e-ecc50d34b30c@gmail.com> Date: Wed, 29 Mar 2023 21:38:53 -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: 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. --------------DmQeN0HimsOMexvCRsbpBZ50 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/29/23 21:06, David G. Johnston wrote: > On Wed, Mar 29, 2023 at 6:51 PM Ron wrote: > > 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. > > > If that would give you what you need then just define the column as text > initially, load the data, then do an ALTER COLUMN ... ALTER TYPE to change > the column type to timestamptz in place, with the conversion done via USING. > > David J. > Something like this? ALTER TABLE foo     ALTER COLUMN update_ts TIMESTAMP WITHOUT TIME ZONE         USING to_timestamp(update_ts, 'YYYYMMDDHH24miSSMS'); That would definitely minimize the possibility of errors. -- Born in Arizona, moved to Babylonia. --------------DmQeN0HimsOMexvCRsbpBZ50 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 3/29/23 21:06, David G. Johnston wrote:
On Wed, Mar 29, 2023 at 6:51 PM Ron <ronljohnsonjr@gmail.com> wrote:
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.


If that would give you what you need then just define the column as text initially, load the data, then do an ALTER COLUMN ... ALTER TYPE to change the column type to timestamptz in place, with the conversion done via USING.

David J.


Something like this?

ALTER TABLE foo
    ALTER COLUMN update_ts TIMESTAMP WITHOUT TIME ZONE
        USING to_timestamp(update_ts, 'YYYYMMDDHH24miSSMS');

That would definitely minimize the possibility of errors.

--
Born in Arizona, moved to Babylonia.
--------------DmQeN0HimsOMexvCRsbpBZ50--