Adding Columns | ALTER TABLE | VantageCloud Lake - Adding Columns to a Table - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530
The following rules and restrictions apply to adding columns to any table.
  • You cannot modify a column more than once in an ALTER TABLE request.
  • When you add a column to a base table that contains data, the system typically accesses each row of the table to add a field for the new column. The following principles apply when adding a column to a base table that already contains rows.
    Condition Result
    DEFAULT is specified for the new column. All rows initially contain the specified constant values in the field.
    WITH DEFAULT specified for the new column. All rows initially contain the system default in the field.
    DEFAULT

    WITH DEFAULT is not specified for the new column.

    		 All rows initially are null for the column and you cannot specify the NOT NULL phrase.
  • A column that is defined as NOT NULL can be added to an empty base table only if the column has no DEFAULT or the WITH DEFAULT phrase.
  • You cannot change the data type, nullability, or name of an indexed column.
  • You cannot change the data type, nullability, or name of a partitioning column for a partitioned table.
  • You cannot add an identity column to an existing base table, nor can you add the identity column attribute to an existing column.
  • You can add BLOB and CLOB columns to a maximum of 32 per base table. See the "Large Object Data Types" section in Rules and Restrictions for ALTER TABLE (Basic Table Parameters).
  • You cannot add a CHARACTER, VARCHAR, or CLOB column with a server character set of KANJI1 to a table. Otherwise, the system returns an error to the requestor.

Maximum Number of Columns in a Base Table

A base table can contain a maximum of 2,048 columns. Up to 32 columns can be defined with a LOB data type. See the "Large Object Data Types" section in Rules and Restrictions for ALTER TABLE (Basic Table Parameters).

A maximum of 2,560 columns can be defined for a base table over its lifetime. Dropping columns does not affect this rule.

If new columns need to be added that would exceed this number, you must create a new table. You can use the SHOW TABLE statement and INSERT … SELECT statements to create and load the spin-off table.