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.

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 or UNIQUE (see CREATE TABLE for the complete syntax).
Note: PRIMARY 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
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