'Alter a table column into smallint column in postgres

I have a postgres table called update_profile with a column that is a table: enter image description here

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?

sqlpostgresql


Solution 1:[1]

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;

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Helio Trigueiros

Related Questions