Amazon Redshift Add Column

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.

In this tutorial, we will cover the basics of Amazon Redshift and the supported statements to add a new column into a 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.