ALTER TABLE - Amazon Redshift

ALTER TABLE

This command changes the definition of a Amazon Redshift table or Amazon Redshift Spectrum external table. This command updates the values and properties set by CREATE TABLE or CREATE EXTERNAL TABLE.

You can't run ALTER TABLE on an external table within a transaction block (BEGIN ... END). For more information about transactions, see Serializable isolation.

ALTER TABLE locks the table for read and write operations until the transaction enclosing the ALTER TABLE operation completes, unless it's specifically stated in the documentation that you can query data or perform other operations on the table while it is altered.

Required privileges

The user that alters a table needs the proper privilege for the command to succeed. Depending on the ALTER TABLE command, one of the following privileges is required.

  • Superuser

  • Users with the ALTER TABLE privilege

  • Table owner with the USAGE privilege on the schema

Syntax

ALTER TABLE table_name
{
ADD table_constraint
| DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
| OWNER TO new_owner
| RENAME TO new_name
| RENAME COLUMN column_name TO new_name
| ALTER COLUMN column_name TYPE updated_varchar_data_type_size
| ALTER COLUMN column_name ENCODE new_encode_type
| ALTER COLUMN column_name ENCODE encode_type,
| ALTER COLUMN column_name ENCODE encode_type, .....;
| ALTER DISTKEY column_name
| ALTER DISTSTYLE ALL
| ALTER DISTSTYLE EVEN
| ALTER DISTSTYLE KEY DISTKEY column_name
| ALTER DISTSTYLE AUTO
| ALTER [COMPOUND] SORTKEY ( column_name [,...] )
| ALTER SORTKEY AUTO
| ALTER SORTKEY NONE
| ALTER ENCODE AUTO
| ADD [ COLUMN ] column_name column_type
  [ DEFAULT default_expr ]
  [ ENCODE encoding ]
  [ NOT NULL | NULL ]
  [ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ] |
| DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ] 
| ROW LEVEL SECURITY { ON | OFF } [ CONJUNCTION TYPE { AND | OR } ] [ FOR DATASHARES ]}

where table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] )
| PRIMARY KEY ( column_name [, ... ] )
| FOREIGN KEY (column_name [, ... ] )
   REFERENCES  reftable [ ( refcolumn ) ]}

The following options apply only to external tables:

SET LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
| SET FILE FORMAT format |
| SET TABLE PROPERTIES ('property_name'='property_value')
| PARTITION ( partition_column=partition_value [, ...] )
  SET LOCATION { 's3://bucket/folder' |'s3://bucket/manifest_file' }
| ADD [IF NOT EXISTS]
    PARTITION ( partition_column=partition_value [, ...] ) LOCATION { 's3://bucket/folder' |'s3://bucket/manifest_file' }
    [, ... ]
| DROP PARTITION ( partition_column=partition_value [, ...] )  

To reduce the time to run the ALTER TABLE command, you can combine some clauses of the ALTER TABLE command.

Amazon Redshift supports the following combinations of the ALTER TABLE clauses:

ALTER TABLE tablename ALTER SORTKEY (column_list), ALTER DISTKEY column_Id; ALTER TABLE tablename ALTER DISTKEY column_Id, ALTER SORTKEY (column_list); ALTER TABLE tablename ALTER SORTKEY (column_list), ALTER DISTSTYLE ALL; ALTER TABLE tablename ALTER DISTSTYLE ALL, ALTER SORTKEY (column_list);

Parameters

table_name

The name of the table to alter. Either specify just the name of the table, or use the format schema_name.table_name to use a specific schema. External tables must be qualified by an external schema name. You can also specify a view name if you're using the ALTER TABLE statement to rename a view or change its owner. The maximum length for the table name is 127 bytes; longer names are truncated to 127 bytes. You can use UTF-8 multibyte characters up to a maximum of four bytes. For more information about valid names, see Names and identifiers.

ADD table_constraint

A clause that adds the specified constraint to the table. For descriptions of valid table_constraint values, see CREATE TABLE.

Note

You can't add a primary-key constraint to a nullable column. If the column was originally created with the NOT NULL constraint, you can add the primary-key constraint.

DROP CONSTRAINT constraint_name

A clause that drops the named constraint from the table. To drop a constraint, specify the constraint name, not the constraint type. To view table constraint names, run the following query.

select constraint_name, constraint_type from information_schema.table_constraints;
RESTRICT

A clause that removes only the specified constraint. RESTRICT is an option for DROP CONSTRAINT. RESTRICT can't be used with CASCADE.

CASCADE

A clause that removes the specified constraint and anything dependent on that constraint. CASCADE is an option for DROP CONSTRAINT. CASCADE can't be used with RESTRICT.

OWNER TO new_owner

A clause that changes the owner of the table (or view) to the new_owner value.

RENAME TO new_name

A clause that renames a table (or view) to the value specified in new_name. The maximum table name length is 127 bytes; longer names are truncated to 127 bytes.

You can't rename a permanent table to a name that begins with '#'. A table name beginning with '#' indicates a temporary table.

You can't rename an external table.

ALTER COLUMN column_name TYPE updated_varchar_data_type_size

A clause that changes the size of a column defined as a VARCHAR data type. This clause only supports altering the size of a VARCHAR data type. Consider the following limitations:

  • You can't alter a column with compression encodings BYTEDICT, RUNLENGTH, TEXT255, or TEXT32K.

  • You can't decrease the size less than maximum size of existing data.

  • You can't alter columns with default values.

  • You can't alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.

  • You can't alter columns within a transaction block (BEGIN ... END). For more information about transactions, see Serializable isolation.

ALTER COLUMN column_name ENCODE new_encode_type

A clause that changes the compression encoding of a column. If you specify compression encoding for a column, the table is no longer set to ENCODE AUTO. For information on compression encoding, see Working with column compression.

When you change compression encoding for a column, the table remains available to query.

Consider the following limitations:

  • You can't alter a column to the same encoding as currently defined for the column.

  • You can't alter the encoding for a column in a table with an interleaved sortkey.

ALTER COLUMN column_name ENCODE encode_type, ALTER COLUMN column_name ENCODE encode_type, .....;

A clause that changes the compression encoding of multiple columns in a single command. For information on compression encoding, see Working with column compression.

When you change compression encoding for a column, the table remains available to query.

Consider the following limitations:

  • You can't alter a column to the same or different encoding type multiple times in a single command.

  • You can't alter a column to the same encoding as currently defined for the column.

  • You can't alter the encoding for a column in a table with an interleaved sortkey.

ALTER DISTSTYLE ALL

A clause that changes the existing distribution style of a table to ALL. Consider the following:

  • An ALTER DISTSTYLE, ALTER SORTKEY, and VACUUM can't run concurrently on the same table.

    • If VACUUM is currently running, then running ALTER DISTSTYLE ALL returns an error.

    • If ALTER DISTSTYLE ALL is running, then a background vacuum doesn't start on a table.

  • The ALTER DISTSTYLE ALL command is not supported for tables with interleaved sort keys and temporary tables.

  • If the distribution style was previously defined as AUTO, then the table is no longer a candidate for automatic table optimization.

For more information about DISTSTYLE ALL, see CREATE TABLE.

ALTER DISTSTYLE EVEN

A clause that changes the existing distribution style of a table to EVEN. Consider the following:

  • An ALTER DISTSYTLE, ALTER SORTKEY, and VACUUM can't run concurrently on the same table.

    • If VACUUM is currently running, then running ALTER DISTSTYLE EVEN returns an error.

    • If ALTER DISTSTYLE EVEN is running, then a background vacuum doesn't start on a table.

  • The ALTER DISTSTYLE EVEN command is not supported for tables with interleaved sort keys and temporary tables.

  • If the distribution style was previously defined as AUTO, then the table is no longer a candidate for automatic table optimization.

For more information about DISTSTYLE EVEN, see CREATE TABLE.

ALTER DISTKEY column_name or ALTER DISTSTYLE KEY DISTKEY column_name

A clause that changes the column used as the distribution key of a table. Consider the following:

  • VACUUM and ALTER DISTKEY can't run concurrently on the same table.

    • If VACUUM is already running, then ALTER DISTKEY returns an error.

    • If ALTER DISTKEY is running, then background vacuum doesn't start on a table.

    • If ALTER DISTKEY is running, then foreground vacuum returns an error.

  • You can only run one ALTER DISTKEY command on a table at a time.

  • The ALTER DISTKEY command is not supported for tables with interleaved sort keys.

  • If the distribution style was previously defined as AUTO, then the table is no longer a candidate for automatic table optimization.

When specifying DISTSTYLE KEY, the data is distributed by the values in the DISTKEY column. For more information about DISTSTYLE, see CREATE TABLE.

ALTER DISTSTYLE AUTO

A clause that changes the existing distribution style of a table to AUTO.

When you alter a distribution style to AUTO, the distribution style of the table is set to the following:

  • A small table with DISTSTYLE ALL is converted to AUTO(ALL).

  • A small table with DISTSTYLE EVEN is converted to AUTO(ALL).

  • A small table with DISTSTYLE KEY is converted to AUTO(ALL).

  • A large table with DISTSTYLE ALL is converted to AUTO(EVEN).

  • A large table with DISTSTYLE EVEN is converted to AUTO(EVEN).

  • A large table with DISTSTYLE KEY is converted to AUTO(KEY) and the DISTKEY is preserved. In this case, Amazon Redshift makes no changes to the table.

If Amazon Redshift determines that a new distribution style or key will improve the performance of queries, then Amazon Redshift might change the distribution style or key of your table in the future. For example, Amazon Redshift might convert a table with a DISTSTYLE of AUTO(KEY) to AUTO(EVEN), or vice versa. For more information about behavior when distribution keys are altered, including data redistribution and locks, see Amazon Redshift Advisor recommendations.

For more information about DISTSTYLE AUTO, see CREATE TABLE.

To view the distribution style of a table, query the SVV_TABLE_INFO system catalog view. For more information, see SVV_TABLE_INFO. To view the Amazon Redshift Advisor recommendations for tables, query the SVV_ALTER_TABLE_RECOMMENDATIONS system catalog view. For more information, see SVV_ALTER_TABLE_RECOMMENDATIONS. To view the actions taken by Amazon Redshift, query the SVL_AUTO_WORKER_ACTION system catalog view. For more information, see SVL_AUTO_WORKER_ACTION.

ALTER [COMPOUND] SORTKEY ( column_name [,...] )

A clause that changes or adds the sort key used for a table.

When you alter a sort key, the compression encoding of columns in the new or original sort key can change. If no encoding is explicitly defined for the table, then Amazon Redshift automatically assigns compression encodings as follows:

  • Columns that are defined as sort keys are assigned RAW compression.

  • Columns that are defined as BOOLEAN, REAL, or DOUBLE PRECISION data types are assigned RAW compression.

  • Columns that are defined as SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP, or TIMESTAMPTZ are assigned AZ64 compression.

  • Columns that are defined as CHAR or VARCHAR are assigned LZO compression.

Consider the following:

  • You can define a maximum of 400 columns for a sort key per table.

  • You can alter an interleaved sort key to a compound sort key or no sort key. However, you can't alter a compound sort key to an interleaved sort key.

  • If the sort key was previously defined as AUTO, then the table is no longer a candidate for automatic table optimization.

  • Amazon Redshift recommends using RAW encoding (no compression) for columns defined as sort keys. When you alter a column to choose it as a sort key, the column’s compression is changed to RAW compression (no compression). This can increase the amount of storage required by the table. How much the table size increases depend on the specific table definition and table contents. For more information about compression, see Compression encodings

When data is loaded into a table, the data is loaded in the order of the sort key. When you alter the sort key, Amazon Redshift reorders the data. For more information about SORTKEY, see CREATE TABLE.

ALTER SORTKEY AUTO

A clause that changes or adds the sort key of the target table to AUTO.

When you alter a sort key to AUTO, Amazon Redshift preserves the existing sort key of the table.

If Amazon Redshift determines that a new sort key will improve the performance of queries, then Amazon Redshift might change the sort key of your table in the future.

For more information about SORTKEY AUTO, see CREATE TABLE.

To view the sort key of a table, query the SVV_TABLE_INFO system catalog view. For more information, see SVV_TABLE_INFO. To view the Amazon Redshift Advisor recommendations for tables, query the SVV_ALTER_TABLE_RECOMMENDATIONS system catalog view. For more information, see SVV_ALTER_TABLE_RECOMMENDATIONS. To view the actions taken by Amazon Redshift, query the SVL_AUTO_WORKER_ACTION system catalog view. For more information, see SVL_AUTO_WORKER_ACTION.

ALTER SORTKEY NONE

A clause that removes the sort key of the target table.

If the sort key was previously defined as AUTO, then the table is no longer a candidate for automatic table optimization.

ALTER ENCODE AUTO

A clause that changes the encoding type of the target table columns to AUTO. When you alter encoding to AUTO, Amazon Redshift preserves the existing encoding type of the columns in the table. Then, if Amazon Redshift determines that a new encoding type can improve query performance, Amazon Redshift can change the encoding type of the table columns.

If you alter one or more columns to specify an encoding, Amazon Redshift no longer automatically adjusts encoding for all columns in the table. The columns retain the current encode settings.

The following actions don't affect the ENCODE AUTO setting for the table:

  • Renaming the table.

  • Altering the DISTSTYLE or SORTKEY setting for the table.

  • Adding or dropping a column with an ENCODE setting.

  • Using the COMPUPDATE option of the COPY command. For more information, see Data load operations.

To view the encoding of a table, query the SVV_TABLE_INFO system catalog view. For more information, see SVV_TABLE_INFO.

RENAME COLUMN column_name TO new_name

A clause that renames a column to the value specified in new_name. The maximum column name length is 127 bytes; longer names are truncated to 127 bytes. For more information about valid names, see Names and identifiers.

ADD [ COLUMN ] column_name

A clause that adds a column with the specified name to the table. You can add only one column in each ALTER TABLE statement.

You can't add a column that is the distribution key (DISTKEY) or a sort key (SORTKEY) of the table.

You can't use an ALTER TABLE ADD COLUMN command to modify the following table and column attributes:

  • UNIQUE

  • PRIMARY KEY

  • REFERENCES (foreign key)

  • IDENTITY or GENERATED BY DEFAULT AS IDENTITY

The maximum column name length is 127 bytes; longer names are truncated to 127 bytes. The maximum number of columns you can define in a single table is 1,600.

The following restrictions apply when adding a column to an external table:

  • You can't add a column to an external table with the column constraints DEFAULT, ENCODE, NOT NULL, or NULL.

  • You can't add columns to an external table that's defined using the AVRO file format.

  • If pseudocolumns are enabled, the maximum number of columns that you can define in a single external table is 1,598. If pseudocolumns aren't enabled, the maximum number of columns that you can define in a single table is 1,600.

For more information, see CREATE EXTERNAL TABLE.

column_type

The data type of the column being added. For CHAR and VARCHAR columns, you can use the MAX keyword instead of declaring a maximum length. MAX sets the maximum length to 4,096 bytes for CHAR or 65,535 bytes for VARCHAR. The maximum size of a GEOMETRY object is 1,048,447 bytes.

For information about the data types that Amazon Redshift supports, see Data types.

DEFAULT default_expr

A clause that assigns a default data value for the column. The data type of default_expr must match the data type of the column. The DEFAULT value must be a variable-free expression. Subqueries, cross-references to other columns in the current table, and user-defined functions aren't allowed.

The default_expr is used in any INSERT operation that doesn't specify a value for the column. If no default value is specified, the default value for the column is null.

If a COPY operation encounters a null field on a column that has a DEFAULT value and a NOT NULL constraint, the COPY command inserts the value of the default_expr.

DEFAULT isn't supported for external tables.

ENCODE encoding

The compression encoding for a column. By default, Amazon Redshift automatically manages compression encoding for all columns in a table if you don't specify compression encoding for any column in the table or if you specify the ENCODE AUTO option for the table.

If you specify compression encoding for any column in the table or if you don't specify the ENCODE AUTO option for the table, Amazon Redshift automatically assigns compression encoding to columns for which you don't specify compression encoding as follows:

  • All columns in temporary tables are assigned RAW compression by default.

  • Columns that are defined as sort keys are assigned RAW compression.

  • Columns that are defined as BOOLEAN, REAL, DOUBLE PRECISION, GEOMETRY, or GEOGRAPHY data type are assigned RAW compression.

  • Columns that are defined as SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP, or TIMESTAMPTZ are assigned AZ64 compression.

  • Columns that are defined as CHAR, VARCHAR, or VARBYTE are assigned LZO compression.

Note

If you don't want a column to be compressed, explicitly specify RAW encoding.

The following compression encodings are supported:

  • AZ64

  • BYTEDICT

  • DELTA

  • DELTA32K

  • LZO

  • MOSTLY8

  • MOSTLY16

  • MOSTLY32

  • RAW (no compression)

  • RUNLENGTH

  • TEXT255

  • TEXT32K

  • ZSTD

ENCODE isn't supported for external tables.

NOT NULL | NULL

NOT NULL specifies that the column isn't allowed to contain null values. NULL, the default, specifies that the column accepts null values.

NOT NULL and NULL aren't supported for external tables.

COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE }

A clause that specifies whether string search or comparison on the column is CASE_SENSITIVE or CASE_INSENSITIVE. The default value is the same as the current case sensitivity configuration of the database.

To find the database collation information, use the following command:

SELECT db_collation(); db_collation ---------------- case_sensitive (1 row)
DROP [ COLUMN ] column_name

The name of the column to delete from the table.

You can't drop the last column in a table. A table must have at least one column.

You can't drop a column that is the distribution key (DISTKEY) or a sort key (SORTKEY) of the table. The default behavior for DROP COLUMN is RESTRICT if the column has any dependent objects, such as a view, primary key, foreign key, or UNIQUE restriction.

The following restrictions apply when dropping a column from an external table:

  • You can't drop a column from an external table if the column is used as a partition.

  • You can't drop a column from an external table that is defined using the AVRO file format.

  • RESTRICT and CASCADE are ignored for external tables.

  • You can't drop the columns of the policy table referenced inside the policy definition unless you drop or detach the policy. This also applies when the CASCADE option is specified. You can drop other columns in the policy table.

For more information, see CREATE EXTERNAL TABLE.

RESTRICT

When used with DROP COLUMN, RESTRICT means that column to be dropped isn't dropped, in these cases:

  • If a defined view references the column that is being dropped

  • If a foreign key references the column

  • If the column takes part in a multipart key

RESTRICT can't be used with CASCADE.

RESTRICT and CASCADE are ignored for external tables.

CASCADE

When used with DROP COLUMN, removes the specified column and anything dependent on that column. CASCADE can't be used with RESTRICT.

RESTRICT and CASCADE are ignored for external tables.

The following options apply only to external tables.

SET LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }

The path to the Amazon S3 folder that contains the data files or a manifest file that contains a list of Amazon S3 object paths. The buckets must be in the same AWS Region as the Amazon Redshift cluster. For a list of supported AWS Regions, see Amazon Redshift Spectrum considerations. For more information about using a manifest file, see LOCATION in the CREATE EXTERNAL TABLE Parameters reference.

SET FILE FORMAT format

The file format for external data files.

Valid formats are as follows:

  • AVRO

  • PARQUET

  • RCFILE

  • SEQUENCEFILE

  • TEXTFILE

SET TABLE PROPERTIES ( 'property_name'='property_value')

A clause that sets the table definition for table properties for an external table.

Note

Table properties are case-sensitive.

'numRows'='row_count'

A property that sets the numRows value for the table definition. To explicitly update an external table's statistics, set the numRows property to indicate the size of the table. Amazon Redshift doesn't analyze external tables to generate the table statistics that the query optimizer uses to generate a query plan. If table statistics aren't set for an external table, Amazon Redshift generates a query execution plan. This plan is based on an assumption that external tables are the larger tables and local tables are the smaller tables.

'skip.header.line.count'='line_count'

A property that sets number of rows to skip at the beginning of each source file.

PARTITION ( partition_column=partition_value [, ...] SET LOCATION { 's3://bucket/folder' | 's3://bucket/manifest_file' }

A clause that sets a new location for one or more partition columns.

ADD [ IF NOT EXISTS ] PARTITION ( partition_column=partition_value [, ...] ) LOCATION { 's3://bucket/folder' | 's3://bucket/manifest_file' } [, ... ]

A clause that adds one or more partitions. You can specify multiple PARTITION clauses using a single ALTER TABLE … ADD statement.

Note

If you use the AWS Glue catalog, you can add up to 100 partitions using a single ALTER TABLE statement.

The IF NOT EXISTS clause indicates that if the specified partition already exists, the command should make no changes. It also indicates that the command should return a message that the partition exists, rather than terminating with an error. This clause is useful when scripting, so the script doesn’t fail if ALTER TABLE tries to add a partition that already exists.

DROP PARTITION (partition_column=partition_value [, ...] )

A clause that drops the specified partition. Dropping a partition alters only the external table metadata. The data on Amazon S3 isn't affected.

ROW LEVEL SECURITY { ON | OFF } [ CONJUNCTION TYPE { AND | OR } ] [ FOR DATASHARES ]

A clause that turns on or off row-level security for a relation.

When row-level security is turned on for a relation, you can only read the rows that the row-level security policy permits you to access. When there isn't any policy granting you access to the relation, you can't see any rows from the relation. Only superusers and users or roles that have the sys:secadmin role can set the ROW LEVEL SECURITY clause. For more information, see Row-level security.

  • [ CONJUNCTION TYPE { AND | OR } ]

    A clause that allows you to choose the conjunction type of row-level security policy for a relation. When multiple row-level security policies are attached to a relation, you can combine the policies with the AND or OR clause. By default, Amazon Redshift combines RLS policies with the AND clause. Superusers, users, or roles that have the sys:secadmin role can use this clause to define the conjunction type of row-level security policy for a relation. For more information, see Combining multiple policies per user.

  • FOR DATASHARES

    A clause that determines whether an RLS-protected relation can be accessed over datashares. By default, an RLS-protected relation can’t be accessed over a datashare. An ALTER TABLE ROW LEVEL SECURITY command run with this clause only affects the relation’s datashare accessibility property. The ROW LEVEL SECURITY property isn’t changed.

    If you make an RLS-protected relation accessible over datashares, the relation doesn’t have row-level security in the consumer-side datashared database. The relation retains its RLS property on the producer side.

Examples

For examples that show how to use the ALTER TABLE command, see the following.