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.