reorder fields in custom type inside array while copy from one table to another

Question

This is my minimal example of my problem:


CREATE TYPE "MyTypeOld" AS (
  a SMALLINT, 
  b BIGINT,
  c SMALLINT
);

CREATE TYPE "MyType" AS (
  a SMALLINT, 
  c SMALLINT,
  b BIGINT
);

-- 

CREATE TABLE old (
  items MyTypeOld[]
);

CREATE TABLE new (
  items MyType[]
);

-- insert some data
INSERT INTO old (items) VALUES ('{"(1,9999999, 2)", "(1,999999988, 2)"}');

My old table is full and new has much less rows. I want to transfer/copy all items from old table to new. I am doing this to match memory layout and save spaces (the old table will have around 500GB of data).

How can I copy such data from old table to be able to remove it completely?

INSERT INTO new (items) (SELECT items[:](a, c, b) FROM old);

score 1 · Accepted Answer · answered Sep 16 '22 at 14:37

This should work:

INSERT INTO new(items)
  SELECT (
    -- The transformation from MyTypeOld to MyType happens here
    SELECT array_agg(ROW(array_element.a, array_element.c, array_element.b)::"MyType")::"MyType"[]
    FROM unnest(old.items) AS array_element
  )
  FROM old;

The gist is that you need to unnest the old arrays and recreate new arrays from them containing your new type.

This might not be the simplest/most performant way to do it, but it is a solution nonetheless :)

reorder fields in custom type inside array while copy from one table to another

1 Answers1