Appendix C. Data Types in pgpro_axe

enum data types are not supported.

The DuckDB decimal data type does not support the wide range of values that the Postgres Pro numeric data type does.

To avoid errors when converting between the two, numeric is converted to double precision internally if DuckDB does not support the required precision. This may cause precision loss of the values.

The DuckDB timestamp_ns data type gets truncated to microseconds when it is converted to the Postgres Pro timestamp data type, which loses precision in the output. Operations on a timestamp_ns value, such as sorting/grouping/comparing, use the full precision.

jsonb columns are converted to json columns when reading from DuckDB. This is because DuckDB does not have the jsonb data type.

Many Postgres Pro json and jsonb functions and operators are not implemented in DuckDB. Instead, you can use DuckDB json functions and operators.

For more information about these functions, refer to the official DuckDB documentation.

The DuckDB tinyint data type is converted to the char data type in Postgres Pro, because Postgres Pro does not have the tinyint data type and displays it as a hex code instead of a regular number.

Conversion between Postgres Pro multi-dimensional arrays and DuckDB nested LISTs in DuckDB can run into various problems due to lack of compatibility between databases.

Specifically, in Postgres Pro, different arrays in a column can have different numbers of dimensions, e.g., [1] and [[1], [2]] can both occur in the same column. In DuckDB, that is not allowed, and the amount of nesting must always be the same. On the other hand, in DuckDB different lists at the same nest level can contain different numbers of elements, e.g., [[1], [1, 2]], which is not allowed in Postgres Pro. So conversion between these data types is only possible when the arrays follow the subset.

Another possible problem that you can run into is that pgpro_axe uses the Postgres Pro column metadata to determine the number of dimensions that an array has. Since Postgres Pro does not warn you when you add arrays of different dimensions, it is possible that the number of dimensions in the column metadata does not match the actual number of dimensions. To avoid this, alter the column data type:

  -- this configures the column to be a 3-dimensional array of text --
  ALTER TABLE s ALTER COLUMN a SET DATA TYPE text[][][];
  

For domain, when INSERT is executed, the check regarding domain is conducted by Postgres Pro, rather than DuckDB. When SELECT is executed, and the data type of the queried field is domain, this field is converted to the corresponding base and handled by DuckDB.