Postgres Pro AXE : Documentation: 1: C.1. Data Types: pgpro

C.1. Data Types: pgpro_axe
Prev	Up	Appendix C. Data Types	Home	Next

C.1. Data Types: pgpro_axe #

Supported Data Types #

pgpro_axe supports the following Postgres Pro and DuckDB data types:

Integer data types (e.g., integer, bigint).
Floating-point data types (e.g., real, double precision).
numeric.
The DuckDB decimal data type does not support the same value range as the Postgres Pro numeric data type. When numeric values exceed the precision supported by DuckDB, they are internally converted to double precision, which may cause loss of precision.
text / varchar / bpchar.
Bit data types.
Both fixed-size and variable-size bit arrays.
bytea / blob.
Date and time data types: timestamp, timestamptz, date, interval, timestamp_ns, timestamp_ms, timestamp_s.
The timestamp_ns data type is truncated to microseconds when converted to the Postgres Pro timestamp data type, which loses precision. Operations on timestamp_ns values, such as sorting, grouping, and comparing, use full precision.
boolean.
uuid.
json / jsonb.
DuckDB does not have the jsonb data type, so jsonb columns are converted to the json data type.
Postgres Pro json and jsonb functions and operators are not supported. You can use stored procedures for JSON data instead.
domain.
When an INSERT query is executed, the domain constraint check is performed by Postgres Pro rather than DuckDB. When a SELECT query is executed on a domain field, the field is converted to its base type and processed by DuckDB.

Special Data Types #

pgpro_axe uses the following special data types:

You do not need to create these data types explicitly, but they can appear in error messages.

duckdb.row #

The duckdb.row data type is returned by certain stored procedures, such as read_parquet, read_csv, and iceberg_scan. Values of this data type contain columns and their respective data types. pgpro_axe cannot determine column data types before executing a query, as they depend on the parameters of the stored procedure.

You can reference a column in a query by assigning an alias to the stored procedure and using the square bracket indexing syntax.

For example:

  SELECT r['id'], r['name'] FROM read_parquet('file.parquet') r WHERE r['age'] > 21;

With the SELECT * syntax, all columns are returned directly, so the query result never contains a column of the duckdb.row data type.

For example:

  SELECT * FROM read_parquet('file.parquet') ON CONFLICT DO NOTHING;

When you use a stored procedure that returns the duckdb.row data type in a common table expression (CTE) or subquery, pgpro_axe cannot automatically assign aliases to the selected columns.

For example, without a CTE or subquery, the following query returns the company column:

  SELECT r['company']
  FROM duckdb.query(' SELECT ''DuckDB Labs'' company ') r;

   company
─────────────
 DuckDB Labs

The same query in a CTE or subquery returns the company column as r:

  WITH mycte AS (
  SELECT r['company']
  FROM duckdb.query(' SELECT ''DuckDB Labs'' company ') r
  )
  SELECT * FROM mycte;

    r
─────────────
 DuckDB Labs

You can avoid this by assigning an explicit alias to the company column in a CTE or subquery:

  WITH mycte AS (
  SELECT r['company'] AS company
  FROM duckdb.query(' SELECT ''DuckDB Labs'' company ') r
  )
  SELECT * FROM mycte;

   company
─────────────
 DuckDB Labs

When using the SELECT * syntax inside a CTE or subquery, you must use the r['column_name'] syntax to reference a specific column outside the CTE or subquery instead of specifying the column name directly.

For example, the following query works as expected:

  WITH mycte AS (
  SELECT *
  FROM duckdb.query(' SELECT ''DuckDB Labs'' company ') r
  )
  SELECT * FROM mycte;

   company
─────────────
 DuckDB Labs

However, the following query returns an error:

  WITH mycte AS (
  SELECT *
  FROM duckdb.query(' SELECT ''DuckDB Labs'' company ') r
  )
  SELECT * FROM mycte WHERE company = 'DuckDB Labs';

  ERROR:  42703: column "company" does not exist
  LINE 5: SELECT * FROM mycte WHERE company = 'DuckDB Labs';

You can avoid this by using the r['column_name'] syntax:

  WITH mycte AS (
  SELECT *
  FROM duckdb.query(' SELECT ''DuckDB Labs'' company ') r
  )
  SELECT * FROM mycte WHERE r['company'] = 'DuckDB Labs';

    company
  ─────────────
  DuckDB Labs

duckdb.unresolved_type #

The duckdb.unresolved_type data type represents an expression whose type cannot be determined by pgpro_axe when parsing a query. It is the data type of any column extracted from a duckdb.row value using the r['column_name'] syntax. For example, the expression r['age'] + 10 has the duckdb.unresolved_type data type because the data type of the r['age'] column cannot be determined when parsing the query.

The actual data type is determined when executing a query, so the query result never contains a column of the duckdb.unresolved_type data type.

An error may occur indicating that a function or operator does not exist for the duckdb.unresolved_type data type. You can avoid this by adding an explicit cast to a data type accepted by the function, such as length(r['name']::text).

duckdb.json #

The duckdb.json data type is used by stored procedures for JSON data. It accepts json, jsonb, and duckdb.unresolved_type values.

Data Type Limitations #

The DuckDB tinyint data type is converted to char because Postgres Pro does not have an equivalent.
The enum data type is not supported.
Converting Postgres Pro multi-dimensional arrays to the DuckDB nested LIST data type can cause compatibility issues.
In Postgres Pro, arrays in a column can have different nesting depths, e.g., [1] and [[1], [2]] can both occur in the same column, which is not allowed in DuckDB. DuckDB allows lists at the same nesting level to contain different numbers of elements, e.g., [[1], [1, 2]], which is not allowed in Postgres Pro.
You can only convert between these data types when the arrays have the same nesting depth and the same number of elements at each level.
pgpro_axe determines the required nesting depth from the Postgres Pro column metadata, but since Postgres Pro does not enforce consistent dimensions when arrays are added to a column, the metadata may not match the actual nesting depth. You can avoid this by altering the column data type.
For example:
```
  ALTER TABLE s ALTER COLUMN a SET DATA TYPE text[][][];
```

Prev	Up	Next
Appendix C. Data Types	Home	C.2. Data Types: pgpro_metastore

epub pdf