CODEWITHSUNDEEP
sqlpostgresql
Kevin
Kevin

Reputation: 6292

SQL: Change data type of a column from String to Numeric

I am going to change the data type of a column from "character varying" to "bigint" in postgre.

The old column contains string "male" and "female", now I want to change it to 0 or 1, each representing male or female.

I think a simple Alter table ... Alter Column does not solve this problem as it will not do the male -> 0, female -> 1 conversion.

Can anyone let me know what is the safest way to do this?

Many thanks.

Upvotes: 2

Views: 3642

Answers (4)

StuartLC
StuartLC

Reputation: 107367

Note that Postgres has the ability to provide the mapping with ALTER COLUMN, which is simpler, as per @ScottMarlowe.

In most RDBMS, this can also be done as follows:

  1. Create a new temp Column containing the integer classification for Gender
  2. Update the table, using CASE / WHEN to map the old varchar to the new INT
  3. DROP the old varchar Column
  4. Add the old Column back to the table, this time with the new Data Type
  5. Copy the data from the temp BIGINT column back to the new Column
  6. Drop the temp BIGINT column

You may need to handle invalid mappings as well - I've done this with a case ELSE. As @Madhivanan suggests, using a BIGINT for a gender classification sounds total overkill.

SQL Fiddle here:

-- Add new Column
ALTER TABLE SomeTable ADD GenderTypeId BIGINT;

-- Map old to new
UPDATE SomeTable SET GenderTypeId = 
CASE
   WHEN GenderVarchar = 'male' 
      THEN 0 
   WHEN  GenderVarchar = 'female' 
       THEN 1
   ELSE
      -1 -- Invalid source data
END;

-- Drop old
ALTER TABLE SomeTable DROP COLUMN GenderVarchar;

Update

If you need to use the same column name as the original, then you also need to add your old column back, this time with the new data type, and copy data across again. (I've given it a new name)

ALTER TABLE SomeTable ADD Gender BIGINT;

-- Copy the data across
UPDATE SomeTable SET Gender = GenderTypeId;

-- Drop the temporary column
ALTER TABLE SomeTable DROP COLUMN GenderTypeId;

Upvotes: 1

Scott Marlowe
Scott Marlowe

Reputation: 8910

You guys are all taking the long way around this. Here's a short test case:

 create table test (username text, sex text); 
 insert into test values ('bob','male'),('sandra','female'),('stan','male'),('sue','male');
 select * from test;
 username |  sex   
 ----------+--------  
 bob      | male  
 sandra   | female  
 stan     | male  
 sue      | male 
 (4 rows)

 alter table test alter column sex type int using case when sex='male' then 0 when sex='female' then 1 end;

 select * from test;  
 username | sex 
 ----------+-----  
 bob      |   0  
 sandra   |   1  
 stan     |   0  
 sue      |   0 (4 rows)

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Points to be considered

  1. When you are going to use 1 for Male and 0 for Female, why do you want to use bigint datatype? BIT/TINYINT datatype will work
  2. Before altering the datatype of the column, you need to update Male to 1 and Female to 0

Upvotes: 4

Sachin
Sachin

Reputation: 2148

You have to first update table and change male to 0 and female to 1

and then you can Alter table ... Alter Column

Upvotes: 0

Related Questions