F.2. Syntax #
F.2.1. Creating a Heap Table #
For example:
-- This is a standard heap table --
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_name TEXT,
amount NUMERIC,
order_date DATE
) ON CONFLICT DO NOTHING;
INSERT INTO orders (product_name, amount, order_date)
VALUES ('Laptop', 1200.00, '2024-07-01'),
('Keyboard', 75.50, '2024-07-01'),
('Mouse', 25.00, '2024-07-02') ON CONFLICT DO NOTHING;
F.2.2. Querying Heap Tables #
For analytical queries on your existing heap tables, use standard SQL. No special syntax is needed. pgpro_axe automatically accelerates these queries when you set the duckdb.force_execution configuration parameter to true.
For example:
SET duckdb.force_execution = true;
-- Standard SELECT on a heap table --
SELECT
category,
AVG(price) AS avg_price,
COUNT(*) AS item_count
FROM
products -- This is a regular heap table --
GROUP BY
category
ORDER BY
avg_price DESC;
F.2.3. Querying External Files #
To query files from a data lake (e.g., local or S3 storage), use read_* functions.
To access columns, use the r[' syntax. column_name']
For example:
-- Query a single Parquet file --
SELECT
r['product_id'],
r['review_text']
FROM
read_parquet('s3://my-bucket/reviews.parquet') r -- 'r' is a required alias --
LIMIT 100;
-- Query multiple CSV files using a glob pattern --
SELECT
r['timestamp'],
r['event_type'],
COUNT(*) AS event_count
FROM
read_csv('s3://my-datalake/logs/2024-*.csv') r
GROUP BY
r['timestamp'],
r['event_type'];
F.2.4. Joining Postgres Pro and External Data #
You can join heap tables with external data sources in a single query.
For example:
-- Join a local 'customers' heap table with a remote Parquet file of 'orders' --
SELECT
c.customer_name,
c.signup_date,
SUM(r['order_total']) AS total_spent
FROM
customers c -- This is a heap table --
JOIN
read_parquet('s3://my-bucket/orders/*.parquet') r ON c.customer_id = r['customer_id']
WHERE
c.status = 'active'
GROUP BY
c.customer_name,
c.signup_date
ORDER BY
total_spent DESC;