MIME-Version: 1.0
References: <fd94aff-aec9-1b3d-64c6-16b0eaeccc58@appl-ecosys.com>
 <D9A3CE80-B0A8-4EF5-8FE8-BF4C70FA7306@thebuild.com> <449bdf5-31d-9189-34d-5ce4188fe2@appl-ecosys.com>
 <B90F940C-8532-4D21-8242-E0E764CE80AE@thebuild.com> <74f52ac6-61cb-f995-34e5-16271b5c6832@appl-ecosys.com>
In-Reply-To: <74f52ac6-61cb-f995-34e5-16271b5c6832@appl-ecosys.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Wed, 3 Jul 2024 16:04:21 +0200
Message-ID: <CAKkG4_n_pT-CB2_yyCVpwM5vXnRoKzZobZbrQop90uvqaq1tXg@mail.gmail.com>
Subject: Re: Accommodating alternative column values
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000513f4f061c584f6a"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_n_pT-CB2_yyCVpwM5vXnRoKzZobZbrQop90uvqaq1tXg%40mail.gmail.com>
Precedence: bulk

--000000000000513f4f061c584f6a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

You could try

ALTER TABLE ... SET TYPE TEXT[] USING ARRAY[email]::TEXT[]

something along these lines.

On Wed, Jul 3, 2024 at 3:58=E2=80=AFPM Rich Shepard <rshepard@appl-ecosys.c=
om>
wrote:

> On Tue, 2 Jul 2024, Christophe Pettus wrote:
>
> > To be clear, I wasn't suggesting stuffing them all into a text column
> with
> > a delimiter, but storing them in a text *array* field, each email addre=
ss
> > one component of the array.
>
> Christophe,
>
> I'm not using the proper syntax and the postgres alter table doc has no
> example in the alter column choices.
>
> What I've tried:
> bustrac=3D# alter table people alter column email set data type varchar(6=
4)
> [];
> ERROR:  column "email" cannot be cast automatically to type character
> varying[]
> HINT:  You might need to specify "USING email::character varying(64)[]".
>
> How do I incorporate the "USING email::..." string?
>
> TIA,
>
> Rich
>
>
>

--000000000000513f4f061c584f6a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">You could try<div><br></div><div>ALTER TABLE ... SET TYPE =
TEXT[] USING ARRAY[email]::TEXT[]</div><div><br></div><div>something along =
these lines.</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">On Wed, Jul 3, 2024 at 3:58=E2=80=AFPM Rich Shepard &lt;<=
a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-ecosys.com</a>&gt;=
 wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Tue, =
2 Jul 2024, Christophe Pettus wrote:<br>
<br>
&gt; To be clear, I wasn&#39;t suggesting stuffing them all into a text col=
umn with<br>
&gt; a delimiter, but storing them in a text *array* field, each email addr=
ess<br>
&gt; one component of the array.<br>
<br>
Christophe,<br>
<br>
I&#39;m not using the proper syntax and the postgres alter table doc has no=
<br>
example in the alter column choices.<br>
<br>
What I&#39;ve tried:<br>
bustrac=3D# alter table people alter column email set data type varchar(64)=
 [];<br>
ERROR:=C2=A0 column &quot;email&quot; cannot be cast automatically to type =
character varying[]<br>
HINT:=C2=A0 You might need to specify &quot;USING email::character varying(=
64)[]&quot;.<br>
<br>
How do I incorporate the &quot;USING email::...&quot; string?<br>
<br>
TIA,<br>
<br>
Rich<br>
<br>
<br>
</blockquote></div>

--000000000000513f4f061c584f6a--