ALTER TABLE

Use the ALTER TABLE statement to change:

  • the structure of a table that already exists on the Kognitio by adding or dropping columns,

  • the name, type, character set or comment of a specific column,

  • any combination of a table’s name, owner, schema or comment,

  • the slabs the table is assigned to (internal tables only)

  • the connector used, target string and read/write capabilitites of an external table

Note: Tables can also be renamed with the RENAME TABLE <> command.

Usage

ALTER TABLE table_name
[(] {ADD [COLUMN] col_name data_type
         [FIRST | LAST | BEFORE col_name | AFTER col_name} |
    {DROP [COLUMN] col_name [, ...] [)]
[CASCADE [{CREATE | DROP} IMAGES] [IGNORE ERRORS]]];

ALTER TABLE table_name
[(] ALTER COLUMN col_name ALTER NAME TO new_name [)];

ALTER TABLE table_name
[(] ALTER COLUMN col_name ALTER TYPE TO new_type
          [CHARACTER SET character-set] [)];

ALTER TABLE table_name
    ALTER COLUMN col_name
          ALTER CHARACTER SET TREAT AS character_set;

ALTER TABLE table_name
    ALTER COLUMN col_name
          SET COMMENT TO 'new_comment';

ALTER TABLE table_name
SET {NAME | OWNER | SCHEMA | COMMENT} TO new_val[, ...] [FORCE];

ALTER TABLE table_name
SET SLABS TO slab_list | ALL | SCHEMA DEFAULT;

ALTER TABLE table_name
SET CONNECTOR to new_connector;

ALTER TABLE table_name
SET TARGET to 'new_target_String';

ALTER TABLE table_name
SET OPERATIONS TO [SELECT ONLY | INSERT | INSERT ONLY];

Notes on the ADD/DROP form

The column definition clause in this statement is almost identical to the column definition clause used in the CREATE TABLE statement. Any columns added to the table specification must comply with the usual naming conventions.

If no position is specified the new column is added to the end of the column definitions for the original table, and appears as the right-most column in subsequent queries against the table.

It is valid to use a column that is being dropped to indicate the position that a new column is to be added.

If an image of the table exists it is dropped and not recreated by ALTER TABLE.

Any new column is usually declared NULL by default. In order to add a NOT NULL column, you must specify a DEFAULT value.

If the table has any views based on it then the CASCADE option forces the definition of these views to be regenerated. If any dependent views have images the command will return an error; to avoid this use the {CREATE | DROP} IMAGES option to indicates that existing images of the view and any dependents should be recreated or dropped.

If CREATE IMAGES is specified, the target view for the command will have a random image generated. To specify a different distribution use the CREATE OR REPLACE VIEW IMAGE command.

In addition, the IGNORE ERRORS option can be specified with the CASCADE and {CREATE | DROP} IMAGES options; this indicates any dependent views which can no longer be created because their definition is now invalid should be removed. If a dependent view image cannot be created although the view definition is still valid, the view definition will still exist but the image will not.

Notes on altering names, types and character sets

Character sets can only be specified for CHAR and VARCHAR columns. See Using National Character Sets for more details of supported character sets.

It is only possible to alter a column’s type if all the existing data in the column can be converted to the new type – if this isn’t possible an overflow error will be returned.

Notes on the SET form

You can use any or all of NAME, OWNER and SCHEMA in any order.

An error is reported if an attempt is made to alter the same attribute more than once within a single statement.

Changing the OWNER or SCHEMA of a table does not alter any privileges associated with it. So typically the previous owner will still have privileges to access and manipulate the table, whereas the new owner may have no privileges at all. See section 4.1, “Privileges” for information about granting and revoking privileges.

The owner of a table can be changed even if the table has dependent views defined by appending the FORCE keyword.

The following permissions are required to change attributes:

  • NAME Identical to those required for dropping and creating.

  • SCHEMA DROP & CREATE in source schema, CREATE in destination schema.

  • OWNER You must be SYS

The SET form can also be used with existing external tables to alter:

  • CONNECTOR - the Kognitio connector used to access the data. For example if an external data source location is moved from Hadoop to AWS, or vice versa

  • TARGET - the location of data. If data is moved or the data partition required changes the target string can be set accordingly.

  • OPERATIONS - change an external table read/write capabilities. If you develop processes in Kognitio that need to write results back into the data source you can alter the external table operation from SELECT ONLY to INSERT while your process carries out the write back. Then you can restrict access back to SELECT ONLY once it is complete. If you wanted to restrict SELECT access to the table for other users while the process is running then you could use INSERT ONLY.

Example 1: Adding Single Columns

Add a column to the CUSTOMER table for the name of an AGENT:

ALTER TABLE customer(ADD COLUMN agent CHAR(25))

Add a column to the PARTSUPP table for a supplementary charge for delivery over the Christmas period. We will ensure this cannot be NULL by providing a default:

ALTER TABLE partsupp(ADD COLUMN s_xmas_supp DECIMAL(7, 2)
NOT NULL DEFAULT 0)

Example 2: Adding Multiple Columns and Dropping a Column

Suppose you want to add columns for the names of two agents to the CUSTOMER table. You can add two columns and drop the column created in the previous example in a single statement:

ALTER TABLE customer
ADD agent1 CHAR(25) BEFORE agent,
DROP agent, ADD agent2 CHAR(25)

Example 3: Renaming and Changing Owner and Containing Schema

The following renames the CUSTOMER table and changes the owner and schema attributes:

ALTER TABLE customer SET
NAME TO newcustomers,
OWNER TO presales,
SCHEMA TO sales

Example 4: Altering the Type and Character Set of a Column

The following alters the type and character set of the agent1 column that was added to the CUSTOMER table above:

ALTER TABLE customer
ALTER COLUMN agent1 ALTER TYPE TO VARCHAR(40)
CHARACTER SET UTF8