public inbox for [email protected]
help / color / mirror / Atom feedRe: Accommodating alternative column values
7+ messages / 4 participants
[nested] [flat]
* Re: Accommodating alternative column values
@ 2024-07-03 14:03 Rich Shepard <[email protected]>
2024-07-03 14:05 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Rich Shepard @ 2024-07-03 14:03 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Accommodating alternative column values
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
@ 2024-07-03 14:05 ` David G. Johnston <[email protected]>
2024-07-03 14:13 ` Re: Accommodating alternative column values Rich Shepard <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-07-03 14:05 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Wednesday, July 3, 2024, Rich Shepard <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Accommodating alternative column values
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:05 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
@ 2024-07-03 14:13 ` Rich Shepard <[email protected]>
2024-07-03 14:21 ` Re: Accommodating alternative column values Torsten Förtsch <[email protected]>
2024-07-03 14:23 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
2024-07-03 14:39 ` Re: Accommodating alternative column values Adrian Klaver <[email protected]>
0 siblings, 3 replies; 7+ messages in thread
From: Rich Shepard @ 2024-07-03 14:13 UTC (permalink / raw)
To: pgsql-general
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: "[email protected]"
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Accommodating alternative column values
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:05 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
2024-07-03 14:13 ` Re: Accommodating alternative column values Rich Shepard <[email protected]>
@ 2024-07-03 14:21 ` Torsten Förtsch <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Torsten Förtsch @ 2024-07-03 14:21 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
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 protected]>
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: "[email protected]"
> 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Accommodating alternative column values
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:05 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
2024-07-03 14:13 ` Re: Accommodating alternative column values Rich Shepard <[email protected]>
@ 2024-07-03 14:23 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2024-07-03 14:23 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Wed, Jul 3, 2024 at 7:13 AM Rich Shepard <[email protected]>
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: "[email protected]"
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Accommodating alternative column values
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:05 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
2024-07-03 14:13 ` Re: Accommodating alternative column values Rich Shepard <[email protected]>
@ 2024-07-03 14:39 ` Adrian Klaver <[email protected]>
2024-07-03 15:12 ` Re: Accommodating alternative column values [RESOLVED] Rich Shepard <[email protected]>
2 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2024-07-03 14:39 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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: "[email protected]"
> 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, '[email protected]'), (2,
'[email protected]');
select * from array_conv ;
id | email
----+---------------------------
1 | [email protected]
2 | [email protected]
alter table array_conv alter column email type varchar[] using array[email];
select * from array_conv ;
id | email
----+-----------------------------
1 | {[email protected]}
2 | {[email protected]}
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Accommodating alternative column values [RESOLVED]
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:05 ` Re: Accommodating alternative column values David G. Johnston <[email protected]>
2024-07-03 14:13 ` Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:39 ` Re: Accommodating alternative column values Adrian Klaver <[email protected]>
@ 2024-07-03 15:12 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Rich Shepard @ 2024-07-03 15:12 UTC (permalink / raw)
To: pgsql-general
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 | {[email protected]}
> 2 | {[email protected]}
Adrian,
Given my inexperience with arrays this seems to be the simplest of all
offered syntaxes.
Another valuable postgres lesson learned.
Much appreciated,
Rich
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-07-03 15:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-03 14:03 Re: Accommodating alternative column values Rich Shepard <[email protected]>
2024-07-03 14:05 ` David G. Johnston <[email protected]>
2024-07-03 14:13 ` Rich Shepard <[email protected]>
2024-07-03 14:21 ` Torsten Förtsch <[email protected]>
2024-07-03 14:23 ` David G. Johnston <[email protected]>
2024-07-03 14:39 ` Adrian Klaver <[email protected]>
2024-07-03 15:12 ` Re: Accommodating alternative column values [RESOLVED] Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox