Amazon Redshift, commonly known as Redshift, is a fully managed, petabyte-scale data warehouse service in the cloud provided by Amazon Web Services (AWS).
When working with Redshift tables, you will encounter instances where you need to add a new column to an existing table.
Redshift Add Column
Like any SQL flavored database, we can add a new column to an existing table using the ALTER TABLE clause using the syntax as shown:
ALTER TABLE table_name ADD column_name data_type;
Where the table_name refers to the name of the table to which you wish to add a new column.
The following are some of the supported data types in Amazon Redshift.
- SMALLINT (INT2)
- INTEGER (INT, INT4)
- BIGINT (INT8)
- DECIMAL (NUMERIC)
- REAL (FLOAT4)
- DOUBLE PRECISION (FLOAT8)
- BOOLEAN (BOOL)
- CHAR (CHARACTER)
- VARCHAR (CHARACTER VARYING)
- DATE
- TIMESTAMP
For example, to add a new column of integer type, we can run the query as shown:
ALTER TABLE sample_table ADD log_id INT NOT NULL;
This should add a new column called log_id of type INT with a NOT NULL constraint.
You can also add a new column with a default value as demonstrated below:
ALTER TABLE sample_table ADD log_id INT NOT NULL DEFAULT 0;
Conclusion
In this short post, we quickly covered how you can add a new column to an existing table in the Amazon Redshift database.
