Question 113804 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search