Chapter 10. Typical Commands for Working with the OLAP Data
After configuring Postgres Pro AXE, you can start working with the OLAP data using the following commands:
Creating a storage:
Example 10.1.
! rm -rf /tmp/mt_storage/ ! mkdir -p /tmp/mt_storage/tmp/ SELECT metastore.add_storage('mt_storage', 'file:///tmp/mt_storage/', 'file:///tmp/mt_storage/tmp/');For more detailed instructions, refer to Section 24.1.
Creating a heap table and inserting the OLAP data into it:
Example 10.2.
CREATE TABLE my_table (id int4 NULL, "name" text NULL, price numeric(10,2) NULL, created_at timestamp NULL); INSERT INTO my_table values (1, 'Product 1', 150.99, '2025-08-09 00:01:01.75'); INSERT INTO my_table values (1, 'Product 2', 300.99, '2025-08-10 00:01:01.75');
Creating an analytical table:
Example 10.3.
SELECT metastore.add_table('mt_tbl_pg_schema', 'mt_storage', 'public.my_table');For more detailed instructions, refer to Section 25.1.
Copying the OLAP data from a heap table to an analytical table:
Example 10.4.
SELECT metastore.copy_table('mt_tbl_pg_schema', $$SELECT * FROM my_table$$);For more detailed instructions, refer to Section 29.2.
Adding Parquet files to an analytical table:
Example 10.5.
\! mkdir /tmp/pga_storage_dir_test/folder_path/ COPY my_table TO '/tmp/mt_storage/folder_path/t.parquet'; SELECT metastore.add_folder('folder_name', 'mt_storage', 'folder_path'); SELECT metastore.add_files('mt_tbl_pg_schema', 'folder_name');For more detailed instructions, refer to Section 28.1 and Section 29.1.
Creating a Postgres Pro view for an analytical table:
Example 10.6.
SELECT metastore.create_view('mt_tbl_pg_schema');For more detailed instructions, refer to Section 25.3.
Reading the OLAP data from the analytical table:
Example 10.7.
SELECT * FROM mt_tbl_pg_schema; SELECT id, price FROM mt_tbl_pg_schema;
Renaming a column:
Example 10.8.
SELECT metastore.rename_column('mt_tbl_pg_schema', 'id', 'newid');For more detailed instructions, refer to Section 26.2.
Changing column data type:
Example 10.9.
SELECT metastore.change_column_type('mt_tbl_pg_schema', 'newid', 'int64');For more detailed instructions, refer to Section 26.3.
Deleting a column:
Example 10.10.
SELECT metastore.delete_column('mt_tbl_pg_schema', 'created_at');For more detailed instructions, refer to Section 26.4.
Deleting a Postgres Pro view:
Example 10.11.
DROP VIEW mt_tbl_pg_schema;
Deleting an analytical table:
Example 10.12.
SELECT metastore.remove_table('mt_tbl_pg_schema');For more detailed instructions, refer to Section 25.4.
Deleting a shared directory:
Example 10.13.
SELECT metastore.remove_folder('folder_name');For more detailed instructions, refer to Section 28.2.
Deleting a storage:
Example 10.14.
SELECT metastore.remove_storage('mt_storage');For more detailed instructions, refer to Section 24.2.
Deleting a heap table:
Example 10.15.
DROP TABLE my_table;