Subject: Re: Accommodating alternative column values

From: Rich Shepard <[email-masked]>
Date: Wed, 3 Jul 2024 07:03:42 -0700 (PDT)

On Wed, 3 Jul 2024, Rich Shepard wrote:

> What I've tried:
> bustrac=# alter table people alter column email set data type varchar(64) [];
> ERROR:  column "email" cannot be cast automatically to type character 
> varying[]
> HINT:  You might need to specify "USING email::character varying(64)[]".

What I forgot to mention is that the current datatype is varchar(64) and I
want to make it an array.

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 3 Jul 2024 07:05:36 -0700

> want to make it an array.
Yeah, the simply cast suggested will not work.  You’d have to apply an
expression that turns the current contents into an array.  The current
contents are not likely to be an array literal.

David J.

---

From: Rich Shepard <[email-masked]>
Date: Wed, 3 Jul 2024 07:13:47 -0700 (PDT)

> 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=# alter table people alter column email set data type varchar(64)[] using email::varchar(64)[];
RROR:  malformed array literal: "[email-masked]"
DETAIL:  Array value must start with "{" or dimension information.

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?

Rich

---

From: Torsten Förtsch <[email-masked]>
Date: Wed, 3 Jul 2024 16:21:39 +0200

Check this out
https://www.postgresql.org/docs/16/arrays.html#ARRAYS-INPUT

You can use
    ('{' || email || '}')::varchar(64)[]

or the syntax I suggested earlier.

On Wed, Jul 3, 2024 at 4:13 PM Rich Shepard <[email-masked]>
wrote:

> bustrac=# alter table people alter column email set data type
> varchar(64)[] using email::varchar(64)[];

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 3 Jul 2024 07:23:00 -0700

> 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.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 3 Jul 2024 07:39:03 -0700

> before I can convert the datatype to an array. Is that correct?

An example:

create table array_conv(id integer, email varchar(64));

insert into array_conv values (1, [email-masked]'), (2, 
[email-masked]');

select * from array_conv ;
  id |           email
----+---------------------------
   1 | [email-masked]
   2 | [email-masked]

alter table array_conv alter column email type varchar[] using array[email];

select * from array_conv ;
  id |            email
----+-----------------------------
   1 | [email-masked]}
   2 | [email-masked]}

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Wed, 3 Jul 2024 08:12:32 -0700 (PDT)

> select * from array_conv ;

Adrian,

Given my inexperience with arrays this seems to be the simplest of all
offered syntaxes.

Another valuable postgres lesson learned.

Much appreciated,

Rich