ALTER TABLE

CREATE TABLE hockey_fans (id       INTEGER     GENERATED ALWAYS AS IDENTITY,
name      STRING     NOT NULL,
address   STRING     NULL,
city      STRING     NOT NULL,
teamid    VARCHAR(3) DEFAULT 'BOS',
gender    CHAR(1)    CHECK (gender IN ('M','F') ),
phone     STRING);

SHOW TABLE hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string

ALTER TABLE hockey_fans ADD COLUMN age INTEGER;
ALTER TABLE hockey_fans ADD CONSTRAINT pk_hockey_fans PRIMARY KEY (id);
SHOW table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            AGE integer
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans DROP age , ADD state CHAR(2);
SHOW table hockey_fans;``
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            STATE char(2)
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans ALTER state SET DATA TYPE STRING;
SHOW table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            STATE string
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans RENAME phone TO phone_number;
SHOW table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE_NUMBER string
            STATE string
        Primary Index: PK_HOCKEY_FANS on field: ID

DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
INSERT INTO tst_table VALUES ('abc'),('ABC');
INSERT INTO tst_table VALUES ('bcd'),('BCD');
SELECT * FROM tst_table WHERE column1 = 'abc';
COLUMN1
--------
   abc

SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd

ALTER TABLE tst_table ALTER column1 COLLATE 8859-1U;
SELECT * FROM tst_table WHERE column1 = 'abc';
 COLUMN1
 --------
   abc
   ABC

SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   abc
   ABC
   bcd
   BCD

/* Cannot alter the collation on an indexed column */
USE TEST;
DROP TABLE IF EXISTS tsttable;
CREATE TABLE tsttable (column1 INTEGER, column2 string);
CREATE INDEX idx_tsttable_column2 ON tsttable(column2);
ALTER TABLE tsttable ALTER COLUMN column2 collate 8859-1U;
/* ERROR MSG: cannot modify collation on column column2 in table test.tsttable: column is used in index idx_tsttable_column2 */

USE TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER PRIMARY KEY, field2 STRING);
INSERT INTO test_table (field1, field2) VALUES (1,'row1');

ALTER TABLE test_table ADD COLUMN field3 STRING;
SELECT * FROM test_table;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------
    1     row1   <null>
SHOW TABLE test_table;
    Tables named TEST_TABLE
    Found table TEST_TABLE in schema USER
        Fields:
            FIELD1 integer
                Nullable: No
            FIELD2 string
            FIELD3 string
        Primary Index: TEST_TABLE..PRIMARY_KEY on field: FIELD1

/* cannot alter an existing column to NOT NULL */
ALTER TABLE test_table ALTER COLUMN field3 NOT NULL;
/* ERROR: NOT NULL constraint failed. Column field3 contains NULL values*/

ALTER TABLE test_table ALTER COLUMN field3 NOT NULL DEFAULT 'field3 not null';
SELECT * FROM test_table;
 FIELD1  FIELD2      FIELD3
 ------- ------- ---------------
    1     row1   field3 not null
/* cannot alter table adding a new NOT NULL column without having to specify a default
 * value */
ALTER TABLE test_table ADD COLUMN field4 STRING NOT NULL;
/* ERROR:  field FIELD4 doesn't allow NULL values, but a default value for the
 * existing rows hasn't been specified */

ALTER TABLE test_table ADD COLUMN field4 STRING NOT NULL DEFAULT 'userid';
SELECT * FROM test_table;
 FIELD1  FIELD2      FIELD3      FIELD4
 ------- ------- --------------- -------
    1     row1   field3 not null userid

USE TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER, field2 INTEGER, field3 STRING );
INSERT INTO test_table (field1, field2, field3) VALUES (NULL,1,'row1'),(NULL,1,'row2');
SHOW TABLE test_table;
    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string

ALTER TABLE test_table ADD PRIMARY KEY (field1,field2);
Null key values are not allowed in primary key index TEST_TABLE..PRIMARY_KEY

UPDATE test_table SET field1 = substr(field3,-1,1) where field1 IS NULL;
SELECT * FROM test_table;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------

    1       1     row1
    2       1     row2

ALTER TABLE test_table ADD PRIMARY KEY (field1,field2);
SHOW TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
                Nullable: No
            FIELD2 integer
                Nullable: No
            FIELD3 string
        Primary Index: TEST_TABLE..PRIMARY_KEY on fields: FIELD1, FIELD2

USE TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER, field2 INTEGER, field3 STRING );
INSERT INTO test_table (field1, field2, field3) VALUES (NULL,1,'row1'),(NULL,1,'row2');
SHOW TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string

ALTER TABLE test_table ADD unique (field1,field2);
SHOW TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string
        Unique Constraint Index: TEST_TABLE_UNIQUE_0 on fields: FIELD1, FIELD2

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(a INT GENERATED BY DEFAULT AS IDENTITY (auto_seq));
SELECT NEXT VALUE FOR auto_seq FROM DUAL;
AUTO_SEQ
---------
    1
INSERT INTO test_table VALUES();
INSERT INTO test_table VALUES();
INSERT INTO test_table VALUES();
AUTO_SEQ
---------
    3
ALTER TABLE test_table CHANGE COLUMN a UPDATE SEQUENCE RANGE;
SELECT NEXT VALUE FOR auto_seq FROM DUAL;
AUTO_SEQ
---------
  1001

/* Create a regular table and then convert it to a partitioned table */
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(f1 int, f2 int, f3 string, PRIMARY KEY (f1, f2));
INSERT INTO test_table values (1,100,'abc'), (2,200,'def'), (3,300,'ghi');

/* Unique indexes must include the partitioning column or an autogenerated
 * column */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f3)
    PARTITION p1 VALUES LESS THAN ('zzz') STORE IN DEFAULT;
/* Error:  Partitioning column TEST_TABLE.F3 must be part of unique index
 * TEST_TABLE..PRIMARY_KEY, or TEST_TABLE..PRIMARY_KEY must include an
 * autogenerated column */

/* The range must exceed the maximum value in the partitioning column */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (200) STORE IN DEFAULT;
/* Error:  partition range must exceed maximum value in the table */

SQL> SELECT MAX(f2) FROM test_table;
 MAX
 ----
 300

ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (301) STORE IN DEFAULT;

/* The table is now partitioned */
SELECT tablename, partitioningcolumn, defaultstoragegroup
    FROM system.partitionedtables WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONINGCOLUMN  DEFAULTSTORAGEGROUP
 ---------- ------------------- --------------------
 TEST_TABLE         F2                 <null>

SELECT tablename, partitionname, storagegroup
    FROM system.partitionids WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONNAME  STORAGEGROUP
 ---------- -------------- -------------
 TEST_TABLE       P1       UNPARTITIONED


/* Convert a table that is located on a specific storage group */
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(f1 int, f2 int, f3 string) STORE IN stg1;
INSERT INTO test_table values (1,100,'abc'), (2,200,'def'), (3,300,'ghi');

/* The storage group cannot be changed */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (301) STORE IN DEFAULT;
/* Error:  changing the storage group is disallowed, table TEST_TABLE is
 * located in storage group STG1 */

ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2) STORE IN stg2
    PARTITION p1 VALUES LESS THAN (301);
/* Error:  changing the storage group is disallowed, table TEST_TABLE is
 * located in storage group STG1 */

/* A different default storage group may be specified
   which will then be used by add partition */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2) STORE IN stg2
    PARTITION p1 VALUES LESS THAN (301) STORE IN stg1;

ALTER TABLE test_table ADD PARTITION p2 VALUES LESS THAN (401);
SELECT tablename, partitioningcolumn, defaultstoragegroup
    FROM system.partitionedtables WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONINGCOLUMN  DEFAULTSTORAGEGROUP
 ---------- ------------------- --------------------
 TEST_TABLE         F2                  STG2

SELECT tablename, partitionname, storagegroup
    FROM system.partitionids WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONNAME  STORAGEGROUP
 ---------- -------------- -------------
 TEST_TABLE       P2           STG2
 TEST_TABLE       P1           STG1