To ALTER or change the length/size of a column in Amazon AWS Redshift use the ALTER COLUMN column_name TYPE clause in ALTER TABLE SQL statement. This TYPE clause in ALTER statement is used to update the length of the column of VARCHAR type in Redshift.
Note that ALTER TABLE locks the TABLE for read and write operations until a transaction completes. When you ALTER TABLE you are modifying the TABLE, so that you cannot read from and write in that TABLE. Once your ALTER transaction is done you can do whatever you want to do with the TABLE. An important piece of information here is that ALTER only changes the varchar length hence, to change the type of a column you have to create a new column and drop the existing column.
By using ALTER statement you can only increase the length of the column but not reduce the length. To reduce the column you may need to drop and add it as a new column with the desired reduced length.
1. Redshift ALTER Column Length Syntax
The syntax for ALTER column to change the length in Amazon Redshift is as follows:
# Syntax Alter column length
ALTER TABLE TABLE_name ALTER COLUMN column_name TYPE new_data_type;
The syntax is pretty easy to write the name of the TABLE you want to alter. Next, alter the column data type by changing the size of the column to your desired size.
2. Prepare Table & Insert Records
Create the Student TABLE with five columns named id, name, address, email, and grade. And have these columns not null. To add a new record to this table, we have to fill all the fields as they are set as not null which means it is compulsory to fill these fields.
# Creating the Student 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
);
To insert values in the student TABLE, use INSERT INTO query, by using this we have added 5 student records. The size of each column is different as we defined while creating the column.
# Inserting values into the Student TABLE
INSERT INTO Students VALUES
(236,'Hira','Rawalpindi Mess','[email protected]','Graduate'),
(237,'Uzair','Rawalpindi','[email protected]','12'),
(238,'T Hira','Multan Mess','[email protected]','Graduate'),
(239,'Tassadaq','Rawat main','[email protected]','Undergraduate'),
(240,'Samra','Sparco main','[email protected]','Masters');
3. Alter the Column to Change the Length
You can change the column length of Varchar in Amazon AWS Redshift by using ALTER COLUMN column_name TYPE clause from ALTER TABLE statement. This allows only increasing VARCHAR column length and doesn’t support decreasing length. Also, While making changes to the table using ALTER statement you cannot insert or read the records from the table as it locks the table while altering.
Since columns are part of the table, first you need to write the ALTER TABLE table_name, following ALTER COLUMN column_name, following TYPE new_column_type, and finally, end the statement with a semicolon.
Replace table_name, column_name, and new_column_type from above as per your need.
Example 1: Alter the student name column size from VARCHAR(10) to VARCHAR(100).
In this example, we will increase the size of s_name column on the TABLE Students. Here, we have changed the length of the student name from 10 to 100.
# ALTER the size of s_name from TABLE Students.
ALTER TABLE Students
ALTER COLUMN s_name type VARCHAR(100);
Output:
Example 2: Alter the student address column.
In this example, we will ALTER the size of s_address from TABLE Students from varchar(20) to varchar(200).
# ALTER the size of s_address from TABLE Students.
ALTER TABLE Students
ALTER COLUMN s_address type VARCHAR(200);
Output:
Example 3: Alter two fields at the same time.
Note that the ALTER statement is used to update only one column at a time in AWS Redshift, if you wanted to change multiple column lengths, you need to write two statements. This example will change the data type of two fields at the same time.
# Changing the length of two fields at the same time.
ALTER TABLE Students
ALTER COLUMN s_email type VARCHAR(300);
ALTER TABLE Students
ALTER COLUMN s_grade type VARCHAR(200);
Output:
4. Validate Column Length Change
Let’s describe the table using a show statement to see the changes we have made to the Redshift table.
# Describe table
show table students;
Yields below results.
5. Conclusion
In this article, we have discussed how to alter the column to increase or reduce the length of the column type of varchar in the Amazon AWS Redshift table. Using alter method we can increase only the size of the varchar column and to reduce the column you have to add a new column and drop the existing column.
Related Articles
- Spark with Amazon EMR – GettingStarted with Data Processing andAnalytics
- AWS Glue PySpark Extensions Reference
- Efficiently Running Spark Applications on AWS: Finding the Best Fit
- ALTER Column Data Type in AWS Redshift