PostgreSQL array support

[riccardo.giampaolo]

Hello,

I am currently using PostgreSQL (with timescale) to store some data, and I'm experimenting with the native postgres arrays.
Apparently, it is absolutely no problem to write natively in the arrays with a system.db.runPrepUpdate, but when I try to read the columns back, I am unable to do so without wrapping the column in an ARRAY_TO_JSON statement.

The error that arises otherwise is:

I ignore if this depends on the JDBC driver. I tried both with the one that comes with Ignition 8.1.31 (v. 42.2.18) and with the latest available (42.6.0).

I would like to know if anybody else encountered the same issue or if it is an expected limitation. Native PostgreSQL array are kinda neat, but if I have to cast them to JSON to retrieve the data, I would use directly JSON to start with.

Thanks in advance!

[pturmel]

Ignition uses only JDBC native types, in order to be cross-platform and DB brand-agnostic. Sorry.

(Consider minimizing brand-specific SQL anyways--protect your work from future platform changes.)

[riccardo.giampaolo]

Thanks for the quick reply @pturmel

And thanks for the advice, being platform agnostic makes perfect sense even if we lose some functionalities.

Have a great weekend.

[pturmel]

New possibilities to consider:

Taking a Dataset and Inserting it to a SQL temp table

If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase…

[riccardo.giampaolo]

Hey @pturmel , thanks for updating this thread.

That's a very interesting approach, but wouldn't it go against the DB agnostic approach you highlighted in your first reply?AFAIK, unnest is not a native MySQL function, and in the topic you pointed out it seems to also be out of MSSQL.

Thank you as always for the valuable feedback!

[pturmel]

UNNEST is in the SQL standard. Oracle has equivalent functionality with different syntax (the predefined collections tools, from what I understand). This functionality is also present (as UNNEST) in Google's cloud data warehouse. Presumably others.

Microsoft simply lags other platforms, and has for many years.

My bias towards DB-agnostic solutions is to avoid vendor lock-in. My bias towards PostgreSQL is due to their stated policy of hewing close to the standard wherever possible. This means that queries written for PostgreSQL tend to work in other DBs, when the functionality involved is present, and the other DB's "standards compliant mode" is turned on. (MS SQL Server does not turn that on by default--another strike against them in my book.)

[riccardo.giampaolo]

Got it, I will have to double check Oracle's syntax then. Thank you again for the update!