Import/export of PostgreSQL array fields

PSQL array fields are processed by the PostgreSQL Adapter as the STRING FlowHeater data type. The individual elements are enclosed in curly brackets, e.g. {"one", "two", "three"} for a CHARACTER array with three elements, or {1.23, 4.56} for a NUMERIC array with two elements.

The import/export of such array fields is no problem in PostgreSQL itself. With the PostgreSQL Adapter a PSQL array field can easily be read on the READ side and imported on the WRITE side into an array field again. Difficulties only begin to arise when such fields have to be exported, for example to a CSV text file, or the values have to be gathered from separate fields and imported into a PSQL array. It is further complicated when multi-dimensional arrays have to be processed.
These examples are intended to illustrate how PSQL arrays are generally handled using the FlowHeater PostgreSQL Adapter.

Note: The examples described here are available as completed Definitions in the latest FlowHeater Download ZIP archive. You will find the examples in subfolder “.\Examples\EN\PostgreSQLAdapter\Array-Handling\”

Preparation

PostgreSQL Array Import - create tableTo enable us to use the examples we need a table “t_import_array”. In the examples folder of the ZIP archive described above you will find an SQL script “create-table.sql” that can be executed directly, using pgAdmin, for example. This will create a new table “t_import_array” with three array fields and data types:

as well as a Primary Key “ID” with the (serial) auto-increment property. This table will be used as reference for the import and subsequently for the export.

CSV import of PSQL arrays

As described above, the array elements are handled in FlowHeater as the STRING data type. This means that in order to be able to import arrays, we must format the string with the correct SQL syntax. To concatenate the individual array values we employ the String Append Heater. However, we must gather the individual values together slightly differently for each data type.

Importing an array of CHARACTER data type {"one", "two", "..."}

PostgreSQL Array Import - quotation marksHere we effectively list the values of elements in the array. The whole array is surrounded by curly brackets and individual array elements are separated by commas. In the case of string elements it is also necessary to enclose each string in double quotation marks. Should characters within a string array element contain double quotation marks, then these must be prefixed by a backslash. To ensure this, we need to insert a String Replace Heater, with settings as shown in the screenshot, for each field of a CHARACTER array.

PostgreSQL Array Import - charactersWe also require a String Append Heater for each string field, to enclose the string in quotation marks. In the Heater’s parameters we enter for both “Prefix” and “Suffix” a double quotation mark, see screenshot.

PostgreSQL Array Import - combine array elementsNow we need a further String Append Heater to combine all the individual array elements in syntax required for a PSQL array. In this case we add a comma as separator, an opening curly bracket as prefix and a closing curly bracket as suffix, see screenshot. We can now connect the formatted array string with the PostgreSQL field on the WRITE side.

Importing an array of NUMERIC data type {1.23, 9.99, 20.00, ...}

PostgreSQL Array Import - numeric formatIn principle an array of numbers is prepared in a similar way to a CHARACTER array except that the individual elements must not be enclosed in double quotation marks and regardless of national conventions it is mandatory to format using a point as decimal character (unless integer) and to omit any thousand marks.

In order to format numbers in this way, independently of the chosen FlowHeater Standard Format, we need a String Format Heater for each array element with settings as illustrated in the screenshot.

The individual array elements are subsequently combined with a downstream String Append Heater. The separator is again a comma and the array is surrounded by curly brackets, in the same way as above.

Importing and array of TIMESTAMP data types {2017-01-01T10:30:15+01, 2017-02-02T09:00:00+01, ...}

PostgreSQL Array Import - datetime formatThe procedure is the identical as for importing an array of DECIMAL values above. To achieve this the format of the date value must be defined as ISO. In the Date/Time tab of the String Format Heater set the DateTime format to yyyy-MM-ddTHH:mm:ss+01 as in the screenshot.

The +01 at the end of the date format string is only necessary when you use the PostgreSQL data type "TIMESTAMP WITH TIME ZONE"

+01 in this case means UTC+1 for the Central European time zone (Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna). An alternative example would be -07 for UTC-7 (Arizona, USA).

CSV export of PSQL arrays

availale soon