Обсуждение: 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
On Wednesday, July 3, 2024, Rich Shepard <rshepard@appl-ecosys.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@appl-ecosys.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@appl-ecosys.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@appl-ecosys.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@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
Check this out
https://www.postgresql.org/docs/16/arrays.html#ARRAYS-INPUTYou can use
('{' || email || '}')::varchar(64)[]
or the syntax I suggested earlier.
On Wed, Jul 3, 2024 at 4:13 PM Rich Shepard <rshepard@appl-ecosys.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@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 Wed, Jul 3, 2024 at 7:13 AM Rich Shepard <rshepard@appl-ecosys.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@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.
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