Обсуждение: 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@appl-ecosys.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
tothe 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@appl-ecosys.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@appl-ecosys.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
happenfrequently. 

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

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

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

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

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@dmipx.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 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@dmipx.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@hjp.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@dmipx.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.klaver@aklaver.com'), (2, 
'aklaver@example.com');

select * from array_conv ;
  id |           email
----+---------------------------
   1 | adrian.klaver@aklaver.com
   2 | aklaver@example.com

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

select * from array_conv ;
  id |            email
----+-----------------------------
   1 | {adrian.klaver@aklaver.com}
   2 | {aklaver@example.com}
> 
> Rich
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.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.klaver@aklaver.com}
>  2 | {aklaver@example.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