ALTER Column Data Type in AWS Redshift

  • Post author:
  • Post category:Amazon AWS
  • Post last modified:May 30, 2024
  • Reading time:13 mins read

Alter command doesn’t support changing the column data type in Amazon AWS Redshift hence, you need to create a new column with the desired data type, copy the data from the old to this new column and then drop the old column. Unlike SQL servers and other databases, amazon redshift has no method for changing the data type of the table.

Advertisements

Though creating a new column and dropping an existing column is not a good practice, we have to use this approach for now as Amazon AWS Redshift does not have any query to alter column data type. Therefore, it is the utmost suitable technique for changing data types.

This article will explain how we can alter the data types of columns once they are created with different examples.

1. Prepare Table & Data

Let’s create a Redshift table with a few columns.


# Creating the table.
CREATE TABLE Students(
s_id integer not null,
s_name VARCHAR(10) not null,
s_address  VARCHAR(20) not null,
s_email  VARCHAR(40) not null,
s_grade VARCHAR(30) not null,
s_date DATE not null,
s_height REAL not null
);

Let’s insert values into the table using INSERT INTO


# Inserting values into the table.
INSERT INTO Students VALUES
(236,'Hira','Rawalpindi Mess','[email protected]','Graduate','2020-05-01',5.5),
(237,'Uzair','Rawalpindi','[email protected]','12','2021-06-01',5.6),
(238,'T Hira','Multan Mess','[email protected]','Graduate','2019-06-01',5.6),
(239,'Tassadaq','Rawat main','[email protected]','Undergraduate','2018-06-25',5.3),
(240,'Samra','Sparco main','[email protected]','Masters','2019-04-06',5.2);

2. Redshift ALTER Column Data Type

Amazon AWS Redshift doesn’t have a single SQL statement to alter the column data type hence, to alter the type first, create a new column with the desired data type, second copy the data from the old column to this new column, and finally drop the old column.

Steps to alter or change the column data type in Redshoft-

  • Step 1 – Add a new field of your desired data type in the table.
  • Step 2 – Copy the data from the old column into the new column by casting the data type.
  • Step 3 – Drop the old column.
  • Step 4 – Rename the newly created field with the old column name.

Let’s use the above steps and change the data type of a few columns in the above table.

3. Change Column Data Type from INT to VARCHAR(100)

In this section, I will demonstrate how to alter/change the column type of s_height from integer to varchar(100) in AWS Redshift.

Step1 – Create a new column

First, create a new column new_height with the desired data type using ALTER TABLE <table_name> ADD COLUMN statement.


# Creating new field/column
ALTER TABLE students add column new_height VARCHAR(100);

Step 2 – Copy data from the old to the new column.

To copy the data from the columns s_height to new_height, use the UPDATE statement with the cast() to change the data type.


# Copying data from column
UPDATE students 
SET new_height = cast(s_height as VARCHAR(100));

Step 3 – Drop the old column

Since we already copied the data from the cold column to the new column, we can safely delete the old column by using ALTER TABLE <table_name> DROP statement.


# Dropping the column
ALTER TABLE students drop column s_height;

Rename new_height to s_height:

Finally, rename the column from new_height to s_height using ALTER TABLE <table_name> RENAME COLUMN statement.


# Renaming new_height
ALTER TABLE students rename column new_height to s_height;

3.1 All Queries

Following is the complete list of redshift queries for reference that change the column type from int to varchar.


# Example 1 all queries

# Creating new field/column
ALTER TABLE students add column new_height VARCHAR(100);

# copying data from column
UPDATE students 
SET new_height = cast(s_height as VARCHAR(100));

# Dropping the column
ALTER TABLE students drop column s_height;

# Renaming new_height
ALTER TABLE students rename column new_height to s_height;

4. Change Column Data Type from int to real (float)

In this section, I will demonstrate with examples of how to alter/change the column type of s_id from integer to real in AWS Redshift.

Create a new field:


# Creating new field/column
ALTER TABLE students add column new_id REAL;

Copy data from column s_id to new_id:


# copying data from column
UPDATE students 
SET new_id = cast(s_id as REAL);

Drop column s_id:


# Dropping the column
ALTER TABLE students drop column s_id;

Rename new_id is to s_id:


# Renaming new_id 
ALTER TABLE students rename column new_id to s_id;

4.2 All Queries

Following is the complete list of redshift queries for reference that change the column type from int to real.


# Example 2 all queries

# Creating new field/column
ALTER TABLE students add column new_id REAL;

# copying data from column
UPDATE students 
SET new_id = cast(s_id as REAL);

# Dropping the column
ALTER TABLE students drop column s_id;

# Renaming new_id
ALTER TABLE students rename column new_id to s_id;

5. Change Column from Date to string

In this section, I will demonstrate with examples of how to alter/change the column type of s_date from string to date in AWS Redshift.

Create a new field:


# Creating new field/column
ALTER TABLE students add column new_date VARCHAR(100);

Copy data from column s_date to new_date:


# copying data from column
UPDATE students 
SET new_date = cast(s_date as VARCHAR(100));

Drop column s_date


# Dropping the column
ALTER TABLE students drop column s_date;

Rename new_date to s_date:


# Renaming new_date 
ALTER TABLE students rename column new_date to s_date;

5.2 All Queries

In this section, I will demonstrate with examples of how to alter/change the column type of s_height from string to real (floating type) in AWS Redshift.


# Example 3 all queries

# Creating new field/column
ALTER TABLE students add column new_date VARCHAR(100);
# copying data from column
UPDATE students 
SET new_date = cast(s_date as VARCHAR(100));
# Dropping the column
ALTER TABLE students drop column s_date;
# Renaming new_date 
ALTER TABLE students rename column new_date to s_date;

6. Change Column from String to INTEGER

In this example, we will convert the string value of s_height column to the integer value.

Create a new field:


# Creating new field/column
ALTER TABLE students add column new_height INT;

Copy data from column s_height to new_height:


# copying data from column
UPDATE students 
SET new_height = cast(s_height as INT);

Drop column s_height:


# Dropping the column
ALTER TABLE students drop column s_height;

Rename new_height is to s_height:


# Renaming new_height
ALTER TABLE students rename column new_height to s_height;

6.2 All Queries:

Following is the complete list of redshift queries for reference that change the column type from read to int.


# Example 4 all queries

# Creating new field/column
ALTER TABLE students add column new_height INT;

# copying data from column
UPDATE students 
SET new_height = cast(s_height as INT);

# Dropping the column
ALTER TABLE students drop column s_height;

# Renaming new_height
ALTER TABLE students rename column new_height to s_height;

7. Validation of Alter Column Type in Redshift

Let’s describe the table using a show statement to see the data type changes we have made to the Redshift table.


# Describe table
show table students;

Yields below output.

Here, we have made the following changes to the table.

  • s_height is changed from int to varchar and then back to int
  • s_id is changed from int to real.
  • s_date is changed from string to date

Conclusion:

In this article, we have discussed how to alter or change the column data type in Amazon AWS Redshift. This article provides an in-depth concept of how we can alter the data type of columns step-by-step with examples. The type casting data types is an easy task but in Redshift, you have to go through a few steps to alter the data type.

Tags: