CODEWITHSUNDEEP
sqldatabasepostgresql
DaveB
DaveB

Reputation: 3083

PostgreSQL Convert column from integer to text

I have a PostgreSQL (9.0) database with a column card_id which is currently of type integer

I need to change this to type text

What is the most best way to achieve this?

The only solution I can find involves creating a temporary column, dropping the original then renaming, I thought they might be a better method??

Upvotes: 26

Views: 41692

Answers (3)

gurungde
gurungde

Reputation: 3

Converting an existing numeric column to boolean (faced this issue during oracle to postgreSQL migration), dropping and enabling default constraint too:

ALTER TABLE table_name 
    ALTER COLUMN column_name DROP DEFAULT,
    ALTER COLUMN column_name TYPE BOOLEAN 
         USING (case when column_name =1 then true else false end)::BOOLEAN,
    ALTER COLUMN column_name SET DEFAULT false;

Upvotes: 0

Milen A. Radev
Milen A. Radev

Reputation: 62573

Have you tried what the fine manual suggests:

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

Depending on the current and the new type you may need to add USING ... to this statement. But in your specific case that should not be necessary I believe.

Upvotes: 49

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143081

ALTER TABLE table ALTER COLUMN card_id SET DATA TYPE text;

Upvotes: 13

Related Questions