README.md in embulk-input-postgresql-0.8.5 vs README.md in embulk-input-postgresql-0.8.6

- old
+ new

@@ -33,11 +33,11 @@ - **incremental_columns**: column names for incremental loading (array of strings, default: use primary keys) - **last_record**: values of the last record for incremental loading (array of objects, default: load all records) - **default_timezone**: If the sql type of a column is `date`/`time`/`datetime` and the embulk type is `string`, column values are formatted int this default_timezone. You can overwrite timezone for each columns using column_options option. (string, default: `UTC`) - **column_options**: advanced: a key-value pairs where key is a column name and value is options for the column. - **value_type**: embulk get values from database as this value_type. Typically, the value_type determines `getXXX` method of `java.sql.PreparedStatement`. - (string, default: depends on the sql type of the column. Available values options are: `long`, `double`, `float`, `decimal`, `boolean`, `string`, `json`, `date`, `time`, `timestamp`) + (string, default: depends on the sql type of the column. Available values options are: `long`, `double`, `float`, `decimal`, `boolean`, `string`, `json`, `date`, `time`, `timestamp`, `array`) See below for `hstore` column. - **type**: Column values are converted to this embulk type. Available values options are: `boolean`, `long`, `double`, `string`, `json`, `timestamp`). By default, the embulk type is determined according to the sql type of the column (or value_type if specified). See below for `hstore` column. @@ -58,11 +58,29 @@ {"key1": "value1", "key2": "value2"} ``` `value_type` is ignored. +### Arrays column support +PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays and this plugin supports converting its value into `string` or `json`. + +By default, `type` of `column_options` for `array` column is `string`, and output will be similar to what `psql` produces: +``` + {1000,2000,3000,4000}, {{red,green},{blue,cyan}} + {5000,6000,7000,8000}, {{yellow,magenta},{purple,"light,dark"}} +``` + +Output of `json` type will be as follow: +``` +[1000,2000,3000,4000],[["red","green"],["blue","cyan"]] +[5000,6000,7000,8000],[["yellow","magenta"],["purple"","light,dark"]] +``` +However, the support for `json` type has the following limitations: +- Postgres server version must be 8.3.0 and above +- The value type of array element must be number, bool, or text, e.g. bool[], integer[], text[][], bigint[][][]... + ### Incremental loading Incremental loading uses monotonically increasing unique columns (such as auto-increment (serial / bigserial) column) to load records inserted (or updated) after last execution. First, if `incremental: true` is set, this plugin loads all records with additional ORDER BY. For example, if `incremental_columns: [updated_at, id]` option is set, query will be as following: @@ -80,10 +98,10 @@ ``` SELECT * FROM ( ...original query is here... ) -WHERE created_at > '2017-01-01 00:32:12' OR (created_at = '2017-01-01 00:32:12' AND id > 5291) +WHERE updated_at > '2017-01-01 00:32:12' OR (updated_at = '2017-01-01 00:32:12' AND id > 5291) ORDER BY updated_at, id ``` Then, it updates `last_record: ` so that next execution uses the updated last_record.