Table options v1.6

You can run CREATE TABLE, ALTER TABLE statements with the USING PGAA WITH ... clause and the following options:

Option	Type	Description
`pgaa.storage_location`	text	PGFS storage location name.
`pgaa.path`	text	Path within storage location.
`pgaa.format`	enum	Table format. Valid values are `delta`, `iceberg`, and `parquet`. (Some might not be supported depending on the feature).
`pgaa.managed_by`	text	Catalog name for catalog-managed tables.
`pgaa.catalog_namespace`	text	Namespace/schema in catalog.
`pgaa.catalog_table`	text	Table name in catalog.
`pgaa.auto_truncate`	boolean	Truncate heap data on access method switch. Requires PGD integration.
`pgaa.purge_data_if_exists`	boolean	Purge existing analytics data when running `CREATE TABLE AS`.
`pgaa.tiered_table`	boolean	Mark as tiered table (PGD only).

Examples

Creating a catalog-managed table

CREATE TABLE managed_sales_data (
    id BIGINT,
    sale_date TIMESTAMP,
    amount NUMERIC
) 
USING PGAA 
WITH (
    pgaa.format = 'iceberg',
    pgaa.managed_by = 'my_iceberg_catalog',
    pgaa.catalog_namespace = 'public',
    pgaa.catalog_table = 'managed_sales_data'
);

Note

If you leave the column definitions empty (()), PGAA will automatically discover the schema from the metadata files when you first query the table and will map columns by name to the fields found in the remote Iceberg/Delta metadata or Parquet headers.

Altering the table from the previous example to point at a PGFS storage location

ALTER TABLE managed_sales_data
SET ACCESS METHOD PGAA,
SET (
    pgaa.format = 'iceberg',
    pgaa.storage_location = 'my_lake_data',
    pgaa.path = 'warehouse_data'
);

On this page
Examples

← Prev

Functions

↑ Up

Reference

Data types and definitions