Convert String to Array in postgres

Question

i Have column to_user in postgres db its type is of varchar it contains UUID. now i want to convert that column into an array of UUID. so i made a new column to_user_copy and copied data from to_user to to_user_copy. i droped the to_user column and created a new column:

ALTER TABLE "transactions" ADD "to_user" character varying array

now i want to convert the UUID's present previouly into array of UUID's.

currently:

@Column("varchar" ,{name:'to_user', array: true })
    toUser: string[];

current column types

current Values

now i want to convert the data present in to_user_copy to array and store it in to_user

          to_user_copy               |         to_user
---------------------------------------------------------------                
dc2544a6-5a5b-4268-9f31-9a9f8bae58aa |           NULL

          to_user_copy                |        to_user
---------------------------------------------------------------                
dc2544a6-5a5b-4268-9f31-9a9f8bae58aa  | {dc2544a6-5a5b-4268-9f31-9a9f8bae58aa}

UUIDs should be stored with the data type uuid, not in a text or varchar column — user330315
– user330315, Commented Nov 24, 2022 at 9:44

user330315 · Accepted Answer · 2022-11-24 09:47:55Z

2

There is no need to create a new column, to change a column from text to text[]

ALTER TABLE "transactions" 
   alter "to_user" type text[] 
   using array["to_user"];

However, UUIDs should be stored in columns defined with the data type uuid.

So this would be the preferred way:

ALTER TABLE "transactions" 
   alter "to_user" type uuid[] 
   using array["to_user"]::uuid[];

answered Nov 24, 2022 at 9:47

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert String to Array in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related