For more information about storages, refer to Chapter 15.
Before performing this scenario:
To connect to an S3 storage:
Set duckdb.unsafe_allow_execution_inside_functions
and duckdb.convert_unsupported_numeric_to_double
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();
Create a simple secret using duckdb.create_simple_secret.
Example D.1.
SELECT duckdb.create_simple_secret(
type := 'S3',
key_id := 'access_key_ID_example',
secret := 'simple_secret_example',
region := '',
url_style := 'path',
endpoint := 'hostname:port',
use_ssl := 'false');
Example D.2.
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
CREATE TABLE.
Example D.3.
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
INSERT.
Example D.4.
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 D.5.
SELECT metastore.add_table('analytical_table_example', 's3_storage_example', 'public.heap_table_example', '');
Copy the OLAP data to the analytical table from the heap table.
Example D.6.
SELECT metastore.copy_table('analytical_table_example', $$select * from heap_table_example$$);
Create a Postgres Pro view from the analytical table.
Example D.7.
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 D.8.
SELECT * from analytical_table_example;