Version:

/alter/table

URL: http://GPUDB_IP_ADDRESS:GPUDB_PORT/alter/table

Apply various modifications to a table, view, or collection. The available modifications include the following:

Create or delete an index on a particular column. This can speed up certain operations when using expressions containing equality or relational operators on indexed columns. This only applies to tables.

Set the time-to-live (TTL). This can be applied to tables, views, or collections. When applied to collections, every contained table & view that is not protected will have its TTL set to the given value.

Set the global access mode (i.e. locking) for a table. This setting trumps any role-based access controls that may be in place; e.g., a user with write access to a table marked read-only will not be able to insert records into it. The mode can be set to read-only, write-only, read/write, and no access.

Change the protection mode to prevent or allow automatic expiration. This can be applied to tables, views, and collections.

Allow homogeneous tables within a collection.

Manage a table's columns--a column can be added, removed, or have its type and properties modified.

Set or unset compression for a column.

Input Parameter Description

Name Type Description
table_name string Table on which the operation will be performed. Must be an existing table, view, or collection.
action string

Modification operation to be applied

Supported Values Description
allow_homogeneous_tables Sets whether homogeneous tables are allowed in the given collection. This action is only valid if input parameter table_name is a collection. The input parameter value must be either 'true' or 'false'.
create_index Creates an index on the column name specified in input parameter value. If this column is already indexed, an error will be returned.
delete_index Deletes an existing index on the column name specified in input parameter value. If this column does not have indexing turned on, an error will be returned.
move_to_collection Moves a table into a collection input parameter value.
protected Sets whether the given input parameter table_name should be protected or not. The input parameter value must be either 'true' or 'false'.
rename_table Renames a table, view or collection to input parameter value. Has the same naming restrictions as tables.
ttl Sets the time-to-live in minutes of the table, view, or collection specified in input parameter table_name.
memory_ttl Sets the time-to-live in minutes for the individual chunks of the columns of the table, view, or collection specified in input parameter table_name to free their memory if unused longer than the given time. Specify an empty string to restore the global memory_ttl setting and a value of '-1' for an infinite timeout.
add_column Adds the column specified in input parameter value to the table specified in input parameter table_name. Use column_type and column_properties in input parameter options to set the column's type and properties, respectively.
change_column Changes type and properties of the column specified in input parameter value. Use column_type and column_properties in input parameter options to set the column's type and properties, respectively. Note that primary key and/or shard key columns cannot be changed. All unchanging column properties must be listed for the change to take place, e.g., to add dictionary encoding to an existing 'char4' column, both 'char4' and 'dict' must be specified in the input parameter options map.
set_column_compression Modifies the compression setting on the column specified in input parameter value.
delete_column Deletes the column specified in input parameter value from the table specified in input parameter table_name.
create_foreign_key Creates a foreign key using the format '(source_column_name [, ...]) references target_table_name(primary_key_column_name [, ...]) [as foreign_key_name]'.
delete_foreign_key Deletes a foreign key. The input parameter value should be the foreign_key_name specified when creating the key or the complete string used to define it.
set_global_access_mode Sets the global access mode (i.e. locking) for the table specified in input parameter table_name. Specify the access mode in input parameter value. Valid modes are 'no_access', 'read_only', 'write_only' and 'read_write'.
refresh Replays all the table creation commands required to create this materialized view.
set_refresh_method Sets the method by which this materialized view is refreshed - one of 'manual', 'periodic', 'on_change'.
set_refresh_start_time Sets the time to start periodic refreshes of this materialized view to datetime string with format 'YYYY-MM-DD HH:MM:SS'. Subsequent refreshes occur at the specified time + N * the refresh period.
set_refresh_period Sets the time interval in seconds at which to refresh this materialized view. Also, sets the refresh method to periodic if not alreay set.
remove_text_search_attributes remove text_search attribute from all columns, if exists.
value string The value of the modification. May be a column name, 'true' or 'false', a TTL, or the global access mode depending on input parameter action.
options map of string to strings

Optional parameters. The default value is an empty map ( {} ).

Supported Parameters (keys) Parameter Description
column_default_value When adding a column, set a default value for existing records. For nullable columns, the default value will be null, regardless of data type.
column_properties When adding or changing a column, set the column properties (strings, separated by a comma: data, store_only, text_search, char8, int8 etc).
column_type When adding or changing a column, set the column type (strings, separated by a comma: int, double, string, null etc).
compression_type

When setting column compression (set_column_compression for input parameter action), compression type to use: none (to use no compression) or a valid compression type. The supported values are:

  • none
  • snappy
  • lz4
  • lz4hc
copy_values_from_column please see add_column_expression instead.
rename_column When changing a column, specify new column name.
validate_change_column

When changing a column, validate the change before applying it. If true, then validate all values. A value too large (or too long) for the new type will prevent any change. If false, then when a value is too large or long, it will be truncated.

Supported Values Description
true true
false false
update_last_access_time

Indicates whether need to update the last_access_time. The supported values are:

  • true
  • false
add_column_expression expression for new column's values (optional with add_column). Any valid expressions including existing columns.

Output Parameter Description

The GPUdb server embeds the endpoint response inside a standard response structure which contains status information and the actual response to the query. Here is a description of the various fields of the wrapper:

Name Type Description
status String 'OK' or 'ERROR'
message String Empty if success or an error message
data_type String 'alter_table_request' or 'none' in case of an error
data String Empty string
data_str JSON or String

This embedded JSON represents the result of the /alter/table endpoint:

Name Type Description
table_name string Table on which the operation was performed.
action string Modification operation that was performed.
value string The value of the modification that was performed.
type_id string return the type_id (when changing a table, a new type may be created)
type_definition string return the type_definition (when changing a table, a new type may be created)
properties map of string to arrays of strings return the type properties (when changing a table, a new type may be created)
label string return the type label (when changing a table, a new type may be created)

Empty string in case of an error.