Alter a table column into smallint column in postgres-CodePudding

I have a postgres table called update_profile with a column that is a table:

And I want to alter this column to a smallint containing the value of update_type_id.

Initially I tried:

ALTER TABLE update_profile ALTER COLUMN update_type TYPE SMALLINT USING update_type.update_type_id;

But I had the following error: missing FROM-clause entry for table "update_type"

Then I tried:

ALTER TABLE update_profile AS u ALTER COLUMN u.update_type TYPE SMALLINT USING u.update_type.update_type_id;

Which is not allowed.

Note: update_type_id is also a smallint

Is there a way to do this operation?

CodePudding user response：

Don't repeat the table name when you reference the other column. You can't assign any alias for the table (or column) either.

ALTER TABLE update_profile 
   ALTER COLUMN update_type TYPE SMALLINT
   USING update_type_id;

CodePudding user response：

This is what I ended up doing:

ALTER TABLE update_profile ADD COLUMN update_type_id SMALLINT;

UPDATE update_profile up SET update_type_id =
(
    SELECT ut.update_type_id
    FROM n3rgy_update_type ut
    WHERE ut = up.update_type
)
WHERE up.update_type IS NOT NULL;

ALTER TABLE update_profile DROP COLUMN update_type;

Because I didn't find a way to alter the column update_type, I created a new column called update_type_id, passed the values of update_profile.update_type.update_type_id, and then dropped update_type.

So now I have the values of update_profile.update_type.update_type_id in update_profile.update_type_id