Can you change the data type of a postgres column from TIMESTAMP[] to unix INTEGER[] (and convert da-CodePudding

I have a postgres database with a table that has a column which is an array of postgres timestamps (TIMESTAMP[]). For reasons, I need to convert this column to be an integer array of unix timestamps (INTEGER[]). I have some data (existing timestamp arrays) in the column I would like to convert over when I change the data type, somehow.

I'm not too well versed in sql, have tried googling around for an answer, and the best I could piece together is this:

ALTER TABLE crafting_bays 
ALTER COLUMN phases_started_at TYPE integer[]  
USING phases_started_at::integer[];

This gives me sql conversion errors though, saying something generic about not being able to convert a timestamp with no zone to an integer. I assume I need to do this manually somehow, just not quite sure how to tackle it.

Thank you

CodePudding user response：

I really do think this is a bad idea.

You can do this with a single ALTER TABLE if you are willing to create a function that does the conversion from a proper timestamp to the dreaded epoch value.

create function obfuscate_timestamps(p_input timestamp[]) 
  returns integer[]
as
$$
  select array_agg(extract(epoch from ts)  order by idx)
  from unnest(p_input) with ordinality as x(ts, idx);
$$
language sql
immutable;

Then you can change the table using:

ALTER TABLE crafting_bays 
   ALTER COLUMN phases_started_at TYPE integer[]  
   USING obfuscate_timestamps(phases_started_at);

Note that with integer you are limiting yourself to timestamps no further than 2038-01-19. You should at least use bigint[]

CodePudding user response：

You cannot use the ALTER TABLE ... ALTER COLUMN option because there are no automatic conversion options for arrays. Instead, you have to do it the hard way.

The function extract(epoch from <column>) is what you need to convert the timestamp into an integer, but first you'll have to unnest() the array. Even before that, you have to add a new column to your table to hold the array of integer[] values and then tie it all together with an UPDATE, followed by deleting the original column of timestamp[]. Somewhat like so:

ALTER TABLE crafting_bays
    ADD COLUMN tmp integer[];

UPDATE crafting_bays SET tmp = agg
FROM (
    -- Sub-query to convert the timestamps to integer and aggregate back into an array
    SELECT id, array_agg(extract(epoch from ps)) agg
    FROM (
        -- Sub-query to unnest the array into separate rows
        SELECT id, unnest(phases_started) AS ps
        FROM crafting_bays ) x
    GROUP BY id ) y
    WHERE y.id = crafting_bays.id;

ALTER TABLE crafting_bays
    DROP COLUMN phases_started;
ALTER TABLE crafting_bays
    RENAME COLUMN tmp TO phases_started;

VACUUM FULL ANALYZE;

In PostgreSQL, when you do an UPDATE the system actually writes a new row to the physical file and marks the original row as deleted. Since you update every row, the table will grow to twice its size. You can reclaim this space with the VACUUM command.