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);
CodePudding user response:
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 :)