Netezza Alter Table Command and Examples

You can use the Netezza ALTER TABLE command to change the structure of an existing table. You can add, modify existing columns in Netezza tables. If the table is in use by an active query, the ALTER command waits until that query completes.

Read:

• Netezza CREATE TABLE command and Examples
• Netezza Data Types
• Clustered base table in Netezza
• Netezza Foreign Key Constraint and Syntax
• Netezza Primary Key Constraint and Syntax

Uses of Netezza Alter Table Command

The main use of the alter table command is to alter the table structure. Below are the uses of alter table command:

• You can use Netezza alter table command change or drop column defaults if any.
• Rename a column or table without changing the data type or size within column or table
• Add or drop constrains on table or columns.
• Reorganize the columns in Netezza database.

ALTER TABLE <table> <action> [ORGANIZE ON {(<columns>) | NONE}]

Where <action> can be one of:

ADD COLUMN <col> <type> [<col_constraint>][,…] |
ADD <table_constraint> |
ALTER [COLUMN] <col> { SET DEFAULT <value> | DROP DEFAULT } |
DROP [COLUMN] column_name[,column_name…] {CASCADE | RESTRICT } |
DROP CONSTRAINT <constraint_name> {CASCADE | RESTRICT} |
MODIFY COLUMN (<col> VARCHAR(<maxsize>)) |
OWNER TO <user_name> |
RENAME [COLUMN] <col> TO <new_col_name> |
RENAME TO <new_table> |
SET PRIVILEGES TO <table>

Netezza Alter Table drop default

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test ALTER COLUMN col1 DROPDEFAULT;

Netezza Alter Table change length of varchar column

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test MODIFY COLUMN (col1 varchar(6));

Netezza Alter Table Rename name of a table

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo RENAME COLUMN addr TO new_addr;

Netezza Alter Table Rename Table

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo RENAME TO test_demo1;

Netezza Alter Table change table owner

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo OWNER TO user2;

Netezza Alter Table add or drop PRIMARY KEY constraints

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo ADD CONSTRAINT keys PRIMARY KEY (col1) INITIALLY IMMEDIATE;TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo DROP CONSTRAINT keys cascade;

Netezza Alter Table add foreign key

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo ADD CONSTRAINT test_fk FOREIGN KEY (col1) REFERENCES test (col1);

Netezza Alter Table add not null constraint

TRAINING.ADMIN(ADMIN)=> ALTER TABLE test_demo ADD COLUMN col_test varchar(10) NOT NULL DEFAULT 0;