Accommodating alternative column values

In data made available from a state regulatory agency I find a few instances
where a facility contact has two email addresses. While multiple locations
are accommodated because they're in a table separate from the facility name
and details, all people associated with a facility and location are in a
'people' table with only one column for an email addresse.

While I could enter an additional email address in the 'comment' column if
I'm entering data for a single facility, I'm reformatting the downloaded
data so I can insert all new table data from one .sql file. Is there a way
to accommodate multiple email addresses other than entering both with a
forward slash separating them in the varchar(64) email address column?

Suggestions appreciated,

Rich

> On Jul 2, 2024, at 14:47, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> Is there a way
> to accommodate multiple email addresses other than entering both with a
> forward slash separating them in the varchar(64) email address column?

If you are absolutely 100% sure there will never be any metadata associated with each email address (like a "valid" flag), you can use TEXT[] array to store them. Otherwise, it's best to move them into a table with a foreign key back to the owning record.

On Tue, 2 Jul 2024, Christophe Pettus wrote:

> If you are absolutely 100% sure there will never be any metadata
> associated with each email address (like a "valid" flag), you can use
> TEXT[] array to store them. Otherwise, it's best to move them into a table
> with a foreign key back to the owning record.

Christophe,

Using text rather than varchar() is a good suggestion.

Thank you,

Rich

> On Jul 2, 2024, at 14:57, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> Using text rather than varchar() is a good suggestion.

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 address one component of the array.

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 address
> one component of the array.

Okay. I've not before done that and will learn how. This data set is the
only one I've encountered that has a very few multiple email addresses for a
person.

Thanks,

Rich

> On Jul 2, 2024, at 15:11, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> This data set is the
> only one I've encountered that has a very few multiple email addresses for a
> person.

That's pretty common out in the world. Just pulling a small dataset I have available, 4+ email addresses per customer happen frequently.

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 address
> 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=# 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)[]".

How do I incorporate the "USING email::..." string?

TIA,

Rich

On Wednesday, July 3, 2024, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:

>
> I'm not using the proper syntax and the postgres alter table doc has no
> example in the alter column choices.

Simpler syntax forms tend to get skipped over when doing examples.

>
> How do I incorporate the "USING email::..." string?
>

ALTER [ COLUMN ] *column_name* [ SET DATA ] TYPE *data_type* [ COLLATE
*collation* ] [ USING *expression* ]

David J.

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

You could try

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

something along these lines.

On Wed, Jul 3, 2024 at 3:58 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
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 address
> > 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=# 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)[]".
>
> How do I incorporate the "USING email::..." string?
>
> TIA,
>
> Rich
>
>
>

On Wednesday, July 3, 2024, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:

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

The USING phrase basically answers the question how do I convert an
existing value of the old type to the new type.

On Wed, Jul 3, 2024 at 4:03 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

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

On Wed, 3 Jul 2024, David G. Johnston wrote:

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

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: "frank(at)dmipx(dot)com"
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

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 <rshepard(at)appl-ecosys(dot)com>
wrote:

> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
> > 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,
>
> 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: "frank(at)dmipx(dot)com"
> 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
>
>
>

On Wed, Jul 3, 2024 at 7:13 AM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
> > 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,
>
> 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: "frank(at)dmipx(dot)com"
> 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?
>
>
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.

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’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,
>
> 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: "frank(at)dmipx(dot)com"
> 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?

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

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

On 7/3/24 07:13, Rich Shepard wrote:
> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
>> 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,
>
> 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: "frank(at)dmipx(dot)com"
> 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?

An example:

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

insert into array_conv values (1, 'adrian(dot)klaver(at)aklaver(dot)com'), (2,
'aklaver(at)example(dot)com');

select * from array_conv ;
id | email
----+---------------------------
1 | adrian(dot)klaver(at)aklaver(dot)com
2 | aklaver(at)example(dot)com

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

select * from array_conv ;
id | email
----+-----------------------------
1 | {adrian(dot)klaver(at)aklaver(dot)com}
2 | {aklaver(at)example(dot)com}
>
> Rich
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

On Wed, 3 Jul 2024, Adrian Klaver wrote:

> alter table array_conv alter column email type varchar[] using array[email];
>
> select * from array_conv ;
> id | email
> ----+-----------------------------
> 1 | {adrian(dot)klaver(at)aklaver(dot)com}
> 2 | {aklaver(at)example(dot)com}

Adrian,

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

Another valuable postgres lesson learned.

Much appreciated,

Rich