Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sP0tb-002Si6-7O for pgsql-general@arkaria.postgresql.org; Wed, 03 Jul 2024 14:23:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sP0tZ-009fws-5C for pgsql-general@arkaria.postgresql.org; Wed, 03 Jul 2024 14:23:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sP0tY-009fwk-Qg for pgsql-general@lists.postgresql.org; Wed, 03 Jul 2024 14:23:41 +0000 Received: from mail-ot1-x32f.google.com ([2607:f8b0:4864:20::32f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sP0tX-000DI6-0U for pgsql-general@postgresql.org; Wed, 03 Jul 2024 14:23:40 +0000 Received: by mail-ot1-x32f.google.com with SMTP id 46e09a7af769-700ca6171f1so2227074a34.2 for ; Wed, 03 Jul 2024 07:23:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720016618; x=1720621418; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=wvxPLDkeudx+IbyQNd4J0U+WA1vm1z6gEO43OwDCkRo=; b=nh5egAwGF2o0atMIx12zDxoGOj2+ZhhqgmNxGIscTKGKMBAaExxXkYvGcUIFgAj7e/ Ztnj4ysFOVlZnjDrj8xZsxQi6K8c7t8WGL5do7K9QLzSRfLWARBC2Fagla/YgXpFfv+L D4RqS0h5y0mq3SMBJLmUFWxi/3ALNdDXo69nUIjvFONSgfMBH2IhLFxKmgaop41CU4Vb IGcubbkaud5BvSbP14nEYx0iN76KmpKVPMxWTC3hgscvOfV7xsQKZeY73b+J7ZQ1qi0g HtMRC31+MGmVZYrSoGWFZ9S8NkFOoc0KxqkQgcmQ7BjbIxTaKW6LB3mARGVw0pZ2dAfn Hj7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720016618; x=1720621418; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=wvxPLDkeudx+IbyQNd4J0U+WA1vm1z6gEO43OwDCkRo=; b=YUwjzEw/BTdrxilKqT4DX/uHaEWc1846mKKn4ubCwBS60f0ZOYkCVLxLsfvJ0h3gRe FV8zK7ncAMHmhu+TKXAteWM+AdQ4ybbpxZeOlQ85VUy4DvcWT5Apg0g38KDsTdaoxG5A zJ+BWTOax5fkOYp0/okNqEqPp9itscYut/y1lP89zmHiTtptA9zI5jT01n8S7Pg2o21W QWn4jIepvBZnFeNR9CuRkL1Nmo4xIfgwZ+QwRnjHnqWKTWnzerkZV5JBWWOsV8jOWEux +ynRk3Dfb4As1k8vZ8DUdybgwuQvKXrKt/37WULoX4vTWwqEgb2arizUqoDI/5tdJM/o R9Kw== X-Gm-Message-State: AOJu0Yz5+MWVtQwPKqi/PcZF0Q20cJcNj2nTJdJ0qDJMnGKY/uzD8981 Sts+ilpJ0oFyuNchhQuL0cWzLk8fWhEy5oQ8K651xOGAGTarKnE0+f2EdPpBbMVPaUq2gDZ6PIi gQEtEWMmOe4nxMgLtcVYoPZgq4F0jwA== X-Google-Smtp-Source: AGHT+IGOFoYaoEunT990AAhzIJpWbpuRqCbPtDvPX45EbYhL2Y9eYN0ZoG7sC2X0QVVytICS0bY3WP4foR5G/tPOod4= X-Received: by 2002:a05:6870:548e:b0:254:b318:8a05 with SMTP id 586e51a60fabf-25db371e0bfmr7572028fac.57.1720016618185; Wed, 03 Jul 2024 07:23:38 -0700 (PDT) MIME-Version: 1.0 References: <449bdf5-31d-9189-34d-5ce4188fe2@appl-ecosys.com> <74f52ac6-61cb-f995-34e5-16271b5c6832@appl-ecosys.com> <3eb9a2d3-28c-9066-d3b5-fd6cca32af9@appl-ecosys.com> <286181eb-9fdd-6680-de4-edcdc82d42a9@appl-ecosys.com> In-Reply-To: <286181eb-9fdd-6680-de4-edcdc82d42a9@appl-ecosys.com> From: "David G. Johnston" Date: Wed, 3 Jul 2024 07:23:00 -0700 Message-ID: Subject: Re: Accommodating alternative column values To: Rich Shepard Cc: "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary="00000000000088e944061c58936b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000088e944061c58936b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jul 3, 2024 at 7:13=E2=80=AFAM Rich Shepard wrote: > On Wed, 3 Jul 2024, David G. Johnston wrote: > > > Yeah, the simply cast suggested will not work. You=E2=80=99d have to ap= ply an > > expression that turns the current contents into an array. The current > > contents are not likely to be an array literal. > > David, > > No, it's not now an array. > > I thought that this expression would work, but it doesn't: > bustrac=3D# alter table people alter column email set data type > varchar(64)[] using email::varchar(64)[]; > RROR: malformed array literal: "frank@dmipx.com" > DETAIL: Array value must start with "{" or dimension information. > > If I correctly understand the error detail I'd need to change the content= s > of that column for all 1280 rows to enclose the contents in curly braces > before I can convert the datatype to an array. Is that correct? > > Assuming today there is only one email per row, no, see Torsten's reply. You also wouldn't need to perform an update..."using ('{' || email || '}')::text[]" if just doing that... David J. --00000000000088e944061c58936b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jul 3, 2024 at 7:13=E2=80=AFAM Rich Shepard <rshepard@appl-ecosys.com> = wrote:
On Wed, 3 Jul 2024, David G. Johnston wrot= e:

> Yeah, the simply cast suggested will not work. You=E2=80=99d have to a= pply an
> expression that turns the current contents into an array. The current<= br> > contents are not likely to be an array literal.

David,

No, it's not now an array.

I thought that this expression would work, but it doesn't:
bustrac=3D# alter table people alter column email set data type varchar(64)= [] using email::varchar(64)[];
RROR:=C2=A0 malformed array literal: "frank@dmipx.com"
DETAIL:=C2=A0 Array value must start with "{" or dimension inform= ation.

If I correctly understand the error detail I'd need to change the conte= nts
of that column for all 1280 rows to enclose the contents in curly braces before I can convert the datatype to an array. Is that correct?


Assuming today there is only one email per row, = no, see Torsten's reply.

You also wouldn't nee= d to=C2=A0perform=C2=A0an update..."using ('{' || email || = 9;}')::text[]" if just doing that...

David J= .

--00000000000088e944061c58936b--