To ALTER or change the length 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, 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 this you can only increase the length of the column. To decrease the column you may need to drop and add it as a new column with the desired decreased 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. Preparse 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 and add 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’,’hiratassadaq52@gmail.com’,’Graduate’),
(237,’Uzair’,’Rawalpindi’,’kiyaniuzair08@gmail.com’,’12’),
(238,’T Hira’,’Multan Mess’,’hiratassadaq90@gmail.com’,’Graduate’),
(239,’Tassadaq’,’Rawat main’,’tassadaq90@gmail.com’,’Undergraduate’),
(240,’Samra’,’Sparco main’,’kiransamra124@gmail.com’,’Masters’);
3. Alter the Column to Change the VARCHAR Length
Example 1: Alter the student name column change the size of variable.
In this example we will ALTER the size of s_name from TABLE Students. The query to ALTER TABLE is very simple. We will change the length of 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);
To alter the student name from student TABLE first write the query to alter TABLE with name that shows the name of TABLE in which you want to make changes. The next line shows the name of the column which you want to alter. The type shows we want to make change in the data type of the field. Now write the data type of variable you want. Put the semi colon at the end of the statement. Click run button to execute the query.
Output:
Example 2:
Alter student address column.
In this example we will ALTER the size of s_address from TABLE Students. The query to will ALTER TABLE 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.
This example will change the data type of two fields at the same time.
# changing the data type 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:
Conclusion:
In this article, we have discussed how to alter the column to increase or descrease the length of the varchar. The alter method changes the size of varchar () but it does not convert the integer to real and for that purpose we should adopt other methods like casting and creating new TABLE then dropping the existing TABLE. The alter column data type is discussed with different examples display the working of alter method. In the nutshell, alter method for changing column data type is useful for varchar () to change its length which makes it easy to redefine the size of field.
To ALTER or change the length 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 Read More Amazon AWS, Redshift