Alter a table column into smallint column in postgres

Question

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?

What is the real problem you are trying to solve here? Once you have done this, you have two columns with exactly the same content and definition. What's the purpose of that? — user330315
– user330315, Commented Apr 7, 2022 at 8:21
So later I can make update_type_id a foreign key and also since we don't have any other column as a table I want to normalize it — Helio Trigueiros
– Helio Trigueiros, Commented Apr 7, 2022 at 8:27
The update_type column seems completely useless in that case. Why not simply drop it entirely? — user330315
– user330315, Commented Apr 7, 2022 at 8:28
Because this table needs the value update_type. But you gave me an idea, I could create a new column called update_type_id, pass the content of update_type.update_type_id there and then delete update_type. — Helio Trigueiros
– Helio Trigueiros, Commented Apr 7, 2022 at 8:35

score 1 · Accepted Answer · 2022-04-07 08:26:49Z

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;

edited Apr 7, 2022 at 8:26

answered Apr 7, 2022 at 8:19

user330315

Sign up to request clarification or add additional context in comments.

Comments

Helio Trigueiros · Accepted Answer · 2022-04-07 14:36:20Z

0

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

answered Apr 7, 2022 at 14:36

Helio Trigueiros

272 silver badges8 bronze badges

Collectives™ on Stack Overflow

Alter a table column into smallint column in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related