Date: Wed, 3 Jul 2024 16:32:09 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Accommodating alternative column values
Message-ID: <20240703143209.r3ysarw37hm2mx2d@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
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>
 <3eb9a2d3-28c-9066-d3b5-fd6cca32af9@appl-ecosys.com>
 <CAKFQuwYGP6geSx61RUuvH07D+CDHWOiZNXi+WpB3U=QJ7h_Dgg@mail.gmail.com>
 <286181eb-9fdd-6680-de4-edcdc82d42a9@appl-ecosys.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="crdsfp3izqb6wifx"
Content-Disposition: inline
In-Reply-To: <286181eb-9fdd-6680-de4-edcdc82d42a9@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/20240703143209.r3ysarw37hm2mx2d%40hjp.at>
Precedence: bulk


--crdsfp3izqb6wifx
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-07-03 07:13:47 -0700, 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.
>=20
> David,
>=20
> No, it's not now an array.
>=20
> I thought that this expression would work, but it doesn't:
> bustrac=3D# alter table people alter column email set data type varchar(6=
4)[] using email::varchar(64)[];
> RROR:  malformed array literal: "frank@dmipx.com"
> DETAIL:  Array value must start with "{" or dimension information.
>=20
> If I correctly understand the error detail I'd need to change the contents
> 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?

No. You need *some* way of creating an array with a single element which
is your email address. Constructing a valid array literal as a text and
casting that to array type is one way to do this. However, it seems like
a rather cumbersome and error-prone way to me.

As Raymond Hettinger likes to say: "There must be a better way".

And indeed, https://www.postgresql.org/docs/current/functions-array.html
shows lots of array values written as ARRAY[1, 2, 3] or similar. So that
makes it likely that ARRAY[email] creates an array with the intended
contents.

Try it with

    select array[email] from people;

If that looks promising, you can use it in an alter table statement
(Torsten already posted the solution, but I wanted to expand a bit on
how to find it).

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--crdsfp3izqb6wifx
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaFYOIACgkQ8g5IURL+
KF1c0A/+OpnYHxvfVxfjC8dqZ8Nc8xrvNQ7KHCJxGbRchZ6oDVhyOYPOpeTCG4P6
fKNDgnBTRM13zZvEI4Jx4LKbZ+kY+4c/kUVkBZplJZfcpx/dGcq71v3cqiqPy8qo
FlTV4ZIbsT6P3IooGuyVrr+NF7WOtvqF9Ngqa98KHI0/v+Xe3LqLaOWAY+5hi6de
flrwLeq1efUc2eECkddSbBFB8C7bpVEH7e6WWSeVlB4p36dQhPBqGQXoSdSJuHQP
A1zUOldyMcPTFLXzXS13kDeE1U5qyOBt2+LG3XpTQ+uqa408DrcgavZAz6WKEXK1
Dsg7tIMvm8JQ2LK+fvgHLlBGqEpAUNWY4YUbuV3jaUd34NjXMC7QXhCe0D0Y+6TO
Ugm07mY10SEWMaBmT7lHMbC4pd1nEWpxhCe1xt8Rh52X6WQuR7nTnLKkcat5XyP+
N+9Ege5dbH6QJx7F/T9xBo9vieitxVx9FNUW1SuEco/MKI0dXVFFiwrDXfY5MLxr
nRMFsWUu9HgwdL1DyQVJZC5R7TsizUI4yZCKUvdu3bV8usaO1wz9SzsMKQiAcKbw
DjRaBQKvCqAOHELDXdDlDFq1Jf81Y75gELCumd04LVcYLpA1VJYgpHgmcaJoJEjM
IQkAGzCQ0oRu1NZpDNRuuRR52pgkyW2EFTNl9Gvububxf1qETb0=
=HvXC
-----END PGP SIGNATURE-----

--crdsfp3izqb6wifx--