Copy data from one column (bigint) to another column (bigint[]) in postgres sql?-CodePudding

I need to copy all data from one column customerId to another new column (customerIds - which is in different format) in the same table. There is a column called customerId whose type is bigint and I need to copy data from this column to customerIds whose data type is bigint[].

Is there any way to do this in postgres sql? I know how to copy data from one column to other column which is in same format but not sure how to do this when new column is array.

Same table and column is in same format.

UPDATE table_name 
SET customerId = customerIds

CodePudding user response：

As you have only one id per customer you can update simply the forst element of the array

CREATE TABLE table_name  (customerId  BIGINT,  customerIds BIGINT[]);


INSERT INTO table_name   VALUES(1);
INSERT INTO table_name  VALUES(2);
INSERT INTO table_name  VALUES(3);
INSERT INTO table_name  VALUES(4);
INSERT INTO table_name  VALUES(5);

UPDATE table_name SET customerIds[1] = customerId ;

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

UPDATE 5

SELECT * FROM table_name

customerid	customerids
1	{1}
2	{2}
3	{3}
4	{4}
5	{5}

SELECT 5

fiddle

CodePudding user response：

maybe this helps:

with cte as (Select 4::BIGINT as num ) SELECT array_agg(cte.num) from cte;

for your problem:

UPDATE table_name SET customerId = arrag_agg(customerIds)

answer is just an idea. not tested