This is true for all external tables, not just PXF. I do not know what are the historical reasons for this separation between readable and writable external tables. I guess it could be changed in the future.
At the moment, be advised to use the LIKE option, to copy the fields definition from table to table. e.g. CREATE EXTERNAL TABLE a (a int, b text, c ... ) ...... CREATE WRITABLE EXTERNAL TABLE b (like a) ...... On Fri, Nov 6, 2015 at 1:19 AM, hawqstudy <[email protected]> wrote: > If we want to create an PXF plugin to allow an external data source to be > both readable and writable, we have to implement both ReadAccessor and > WriteAccessor interface. > However, when we create external table mapping, a table has to be either > readable or writable, but can't be both. > In this case we have to create two tables pointing to the same data source: > > postgres=# \d+ t3 > > External table "public.t3" > > Column | Type | Modifiers | Storage | Description > > ----------+-------------------+-----------+----------+------------- > > id | integer | | plain | > > total | integer | | plain | > > comments | character varying | | extended | > > Type: writable > > Encoding: UTF8 > > Format type: custom > > Format options: formatter 'pxfwritable_export' > > External location: pxf://localhost:51200/foo.main?PROFILE=XXXX > > > postgres=# \d+ t4 > > External table "public.t4" > > Column | Type | Modifiers | Storage | Description > > -----------+-------------------+-----------+----------+------------- > > recordkey | character varying | | extended | > > id | integer | | plain | > > total | integer | | plain | > > comments | character varying | | extended | > > Type: readable > > Encoding: UTF8 > > Format type: custom > > Format options: formatter 'pxfwritable_import' > > External location: pxf://localhost:51200/foo.main?PROFILE=XXXX > > postgres=# insert into t3 select * from t5 ; > > INSERT 0 65536 > > postgres=# select count(*) from t4 ; > > count > > -------- > > 131077 > > (1 row) > > > I wonder is there anyway we can create a table for both read and write > purpose? > > > >
