For more information about storages, refer to Chapter 24.
Before performing this scenario:
To configure this scenario:
Set
duckdb.unsafe_allow_execution_inside_functions
and
duckdb.convert_unsupported_numeric_to_double
configuration parameters to true:
ALTER SYSTEM SET duckdb.unsafe_allow_execution_inside_functions TO true; SET duckdb.convert_unsupported_numeric_to_double = true;
Reload the Postgres Pro AXE server:
SELECT pg_reload_conf();
Example 24.4.
SELECT duckdb.create_simple_secret(
type := 'S3',
key_id := 'access_key_ID_example',
secret := 'simple_secret_example',
region := '',
url_style := 'path',
endpoint := 'hostname:port',
validation := '',
use_ssl := 'false'
);
Example 24.5.
SELECT metastore.add_storage('s3_storage_example', 's3://demo/s3_storage_example/','s3://demo/s3_storage_example/tmp/');
Ensure that the S3 storage is configured correctly:
Create a heap table using the
CREATE TABLE
command.
Example 24.6.
CREATE TABLE heap_table_example (
id int4 NULL,
"name" text NULL,
price numeric(10,2) NULL,
created_at timestamp NULL
);
Insert rows into the heap table using the
INSERT
command.
Example 24.7.
INSERT INTO heap_table_example VALUES (1, 'Item 1', 150.99, '2025-08-09 00:01:01.75'); INSERT INTO heap_table_example VALUES (2, 'Item 2', 300.99, '2025-08-10 00:01:01.75');
Create an analytical table from the heap table.
Example 24.8.
SELECT metastore.add_table('analytical_table_example', 's3_storage_example', 'public.heap_table_example', '');
Copy the OLAP data from the heap table to the analytical table.
Example 24.9.
SELECT metastore.copy_table('analytical_table_example', $$select * from heap_table_example$$);
Create a Postgres Pro view for the analytical table.
Example 24.10.
SELECT metastore.create_view('analytical_table_example');
Ensure that the values that you inserted into the heap table can be retrieved from the analytical table.
Example 24.11.
SELECT * FROM analytical_table_example;