Re: Accommodating alternative column values [RESOLVED]
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.kla...@aklaver.com}
2 | {akla...@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
Re: Accommodating alternative column values
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: "fr...@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.kla...@aklaver.com'), (2,
'akla...@example.com');
select * from array_conv ;
id | email
+---
1 | adrian.kla...@aklaver.com
2 | akla...@example.com
alter table array_conv alter column email type varchar[] using array[email];
select * from array_conv ;
id |email
+-
1 | {adrian.kla...@aklaver.com}
2 | {akla...@example.com}
Rich
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Accommodating alternative column values
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: "fr...@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.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Accommodating alternative column values
On Wed, Jul 3, 2024 at 7:13 AM 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: "fr...@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?
>
>
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.
Re: Accommodating alternative column values
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
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: "fr...@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
>
>
>
Re: Accommodating alternative column values
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: "fr...@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
Re: Accommodating alternative column values
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 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 > > >
Re: Accommodating alternative column values
On Wednesday, July 3, 2024, Rich Shepard 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.
Re: Accommodating alternative column values
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 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 > > >
Re: Accommodating alternative column values
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
Re: Accommodating alternative column values
On Wednesday, July 3, 2024, Rich Shepard 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.
Re: Accommodating alternative column values
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
Re: Accommodating alternative column values
> On Jul 2, 2024, at 15:11, Rich Shepard 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.
Re: Accommodating alternative column values
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
Re: Accommodating alternative column values
> On Jul 2, 2024, at 14:57, Rich Shepard 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.
Re: Accommodating alternative column values
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
Re: Accommodating alternative column values
> On Jul 2, 2024, at 14:47, Rich Shepard 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.
