Request a Demo
3.4. Columns of Analytical Tables
Prev UpChapter 3. AdministrationHome Next

3.4. Columns of Analytical Tables #

3.4.1. Creating a Column
3.4.2. Renaming a Column
3.4.3. Changing the Data Type of a Column
3.4.4. Deleting a Column

For more information about analytical tables, refer to Section 3.3.

3.4.1. Creating a Column #

Execute the following query: 

  SELECT metastore.add_column('schema_name.table_name' or 'table_name', 'column_name', 'data_type');

Where:

  • schema_name: The name of the analytical schema where the table is created.

    Optional parameter. If you do not specify it, the current schema is used.

  • table_name: The name of the analytical table where the column will be created.

  • column_name: The unique column name.

  • data_type: The data type of the column.

    For more information about supported data types, refer to Appendix E.

Example 3.23. 

  SELECT metastore.add_column('testtable', 'third_col', 'uint8');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Creates a new column in pga_column metadata table.

  3. Updates the view_sql value for the analytical table.

3.4.2. Renaming a Column #

Execute the following query: 

  SELECT metastore.rename_column('schema_name.table_name' or 'table_name', 'current_column_name', 'new_column_name');

Where:

  • schema_name: The name of the analytical schema where the table is created.

    Optional parameter. If you do not specify it, the current schema is used.

  • table_name: The name of the analytical table where the column is created.

  • current_column_name: The current column name.

  • new_column_name: The new unique column name.

Example 3.24. 

  SELECT metastore.rename_column('testtable', 'first_col', 'eleventh_col');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Updates the column_name value for the column in the pga_column metadata table.

  3. Updates the view_sql value for the analytical table.

3.4.3. Changing the Data Type of a Column #

Execute the following query: 

  SELECT metastore.change_column_type('schema_name.table_name' or 'table_name', 'column_name', 'new_data_type');

Where:

  • schema_name: The name of the analytical schema where the table is created.

    Optional parameter. If you do not specify it, the current schema is used.

  • table_name: The name of the analytical table where the column is created.

  • column_name: The name of the column whose data type will be changed.

  • data_type: The new data type of the column.

    For more information about supported data types, refer to Appendix E.

Example 3.25. 

  SELECT metastore.change_column_type('testtable', 'third_col', 'uint8');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Ensures that the new column data type does not match the previous one.

  3. Ensures that the analytical table is empty or that the new data type is compatible with the previous one:

    • int8 can be changed to int16, int32, or int64

    • int16 can be changed to int32 or int64

    • int32 can be changed to int64

    • uint8 can be changed to uint16, uint32, or uint64

    • uint16 can be changed to uint32 or uint64

    • uint32 can be changed to uint64

    • float32 can be changed to float64

  4. Updates the column_type value for the column in the pga_column metadata table.

  5. Updates the view_sql value for the analytical table.

3.4.4. Deleting a Column #

Execute the following query: 

  SELECT metastore.delete_column('schema_name.table_name' or 'table_name', 'column_name');

Where:

  • schema_name: The name of the analytical schema where the table is created.

    Optional parameter. If you do not specify it, the current schema is used.

  • table_name: The name of the analytical table where the column is created.

  • column_name: The name of the column that will be deleted.

Example 3.26. 

  SELECT metastore.delete_column('my_schema.testtable', 'second_col');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Creates a new snapshot in the pga_snapshot metadata table and specifies for the column the end_snapshot value in the pga_column metadata table.

  3. Updates the view_sql value for the analytical table.

Prev Up Next
3.3. Analytical Tables Home 3.5. Analytical Schemas
epub pdf