Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

ALTER TABLE ​

Modify the attributes of a table.

ALTER TABLE [ IF EXISTS ] name 
ADD column_name data_type [ column_constraint [ ... ] ] |
ADD table_constraint 
ALTER [ COLUMN ] column_name SET DEFAULT expression |
ALTER [ COLUMN ] column_name DROP DEFAULT |
ALTER [ COLUMN ] column_name DROP NOT NULL |
ALTER [ COLUMN ] varchar_column_name VARCHAR(new_length) |
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] |
OWNER TO { new_owner | CURRENT_USER | SESSION_USER } |
RENAME column_name TO new_column_name |
RENAME TO new_name |
SET MAX_SIZE [=] 'size' |
SET MAX_SIZE DISABLE |
SET SCHEMA new_schema |
SET ROWSTORE_SIZE_LIMIT 'size' |
SET ROWSTORE_FULL_ACTION { { 'B' | 'BLOCK' } | { 'C' | 'CANCEL' } | { 'S' | 'SLOW' } }
IF EXISTS

Alter the table if it exists, but do not return an error message if it does not exist.

ADD column_name

Add a column to a table. You must specify the data type.

You cannot drop columns from tables. ALTER TABLE DROP COLUMN is not supported.

Tables have a limit of 2,000 user-defined columns.

ADD column_constraint

Specify column constraints for added columns, such as UNIQUE, NULL, and DEFAULT; however, you cannot add a column with a NOT NULL constraint. See CREATE TABLE for complete column constraint syntax.

Note: When you add a column with a DEFAULT constraint, the default value is applied only for new rows that are inserted after the column was added. Existing rows in the table will contain a NULL value for that column.

ADD table_constraint

Add a table constraint: PRIMARY KEY, FOREIGN KEY, or UNIQUE (see CREATE TABLE for the complete syntax).

Note: PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints are not enforced.

SET DEFAULT expression

Define a default value for the column, which applies only to new rows that are inserted or updated; existing rows do not change.

DROP DEFAULT

Drop the default value for the column. Make sure the expression is compatible with the data type of the column. This change applies only to new rows that are inserted or updated; existing rows do not change.

DROP NOT NULL

Drop the NOT NULL constraint for the column. NULL values will be allowed in new rows. This command applies only to columns that were defined NOT NULL. (SET NOT NULL is not supported.)

varchar_column_name

Specify an existing VARCHAR column in the table. The column must be a VARCHAR column; you cannot alter column lengths for other data types. You cannot alter the length of a column that is referenced in a view.

VARCHAR(new_length)

The new length of the VARCHAR column you want to modify. This number must be greater than the current length of the column. For example, you can change a VARCHAR(20) column to a VARCHAR(50).

DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

Drop a constraint on a table, such as a foreign key constraint. The CASCADE option automatically drops objects that depend on the dropped constraint. The RESTRICT option (the default) does not drop the constraint if dependent objects exist.

OWNER TO

Change the owner of the table.

RENAME TO

Rename a column in the table or the name of the table itself.

SET MAX_SIZE [=] 'size'

Specify the maximum amount of space an individual table may use, where 'size' is the number of bytes. 'size' must be a positive integer with the possible inclusion of units: MB, GB, or TB. If units are included, the command will be accepted with and without a space between the integer and unit.

SET MAX_SIZE DISABLE

Disable the disk usage limit for the specified table.

SET SCHEMA

Change the schema that the table belongs to.

SET ROWSTORE_SIZE_LIMIT 'size'

Specify a row store size limit for the table, using a number and a unit in single quotes. For example:

  • '1MB'
  • '10MB'
  • '50GB'

A value of -1 means infinity (equivalent to no limit other than the size of the row store itself). A value of 0 means that rows are immediately flushed for every insert (creating very small shards in the column store file system).

Specified limits are converted into a number of files and rounded down. The default file size is 32MB. For example, a limit of 32GB converts to a maximum of 1024 32MB files.

The current limit for each table is logged in the sys.table view.

SET ROWSTORE_FULL_ACTION

Specify the action to take when the row store limit for the table is reached. For more details, see Managing the Row Store.

  • 'S' ('SLOW'): Insert rows directly into the column store. This option will make sure the data continues to load but performance will not be optimal. This option is the default. When rows start being loaded into the column store instead of the row store, you will see a warning message.

  • 'B'('BLOCK'): Block the insert until a flush is possible, then resume.

    Note: When the BLOCK action is set, inserts tracked in the sys.query view remain in the plan state.

  • 'C'('CANCEL'): Cancel the insert and return an error.

The current action for each table is logged in the sys.table view.

Examples ​

Change the schema of a table:

premdb=# alter table team set schema private;
ALTER TABLE

Add a new column to a table:

premdb=# alter table matchstats add column matchid uuid null;
ALTER TABLE

Define a default value for a column:

premdb=# alter table match alter column matchday set default '2017-10-21';
ALTER TABLE

Remove a default value for a column:

premdb=# alter table match alter column matchday drop default;
ALTER TABLE

Remove a NOT NULL constraint from a column:

premdb=# alter table newmatchstats alter column seasonid drop not null;
ALTER TABLE

Add a UNIQUE constraint to a table:

premdb=# alter table match add constraint uniquematch unique(matchid);
ALTER TABLE

Add a FOREIGN KEY constraint to a table:

premdb=# alter table city add constraint fk_city_country foreign key (countrycode) references country(code);

Increase the length of the VARCHAR column city in the team table. The column was originally defined as VARCHAR(20).

premdb=# alter table team alter column city varchar(30);
ALTER TABLE

Drop a foreign-key constraint from a table:

premdb=# alter table match drop constraint match_seasonid_fkey;
ALTER TABLE

Set the row store size limit and the row store full action for the match table:

premdb=# alter table match set rowstore_size_limit '10GB';
ALTER TABLE
premdb=# alter table match set rowstore_full_action 'C';
ALTER TABLE
premdb=# select * from sys.table where name='match';
-[ RECORD 1 ]--------+------------------------------
table_id             | 16515
database_id          | 16498
name                 | match
schema_id            | 2200
owner_id             | 10
distribution         | hash
sort_key             | [NULL]
distribution_key     | seasonid
cluster_keys         | 
partition_keys       | 
is_temp              | f
definition           | [NULL]
is_auto_analyze      | t
auto_analyze_policy  | default_policy
last_analyzed        | 2020-06-01 17:44:35.669725-07
creation_time        | 2020-06-01 11:40:25.059889-07
rowstore_bytes       | 0
rowstore_row_count   | 0
rowstore_disk_usage  | 0
rowstore_size_limit  | 10737418240
rowstore_full_action | C

Set a quota for the match table:

premdb=# alter table match set max_size='1MB';
ALTER TABLE

Parent topic:SQL Commands