POSTGRES - INSERT INTO FOREIGN TABLE

Question

I created a table into an external server

CREATE FOREIGN TABLE external_table (
    field_1 varchar(15) NULL,
    field_2 int4 NULL
)
SERVER server_name
OPTIONS(compression 'pglz', stripe_row_count '500000');

Now I want to insert into external_table, but if I run this query

INSERT INTO external_table (field_1, field_2) VALUES  ('test',1);

It return this error

ERROR: operation is not supported

How can I add record into a foreign table?

I've tried with the following insert

INSERT INTO external_table (field_1, field_2) select 'test',1;

It works, but I can't use a INSERT INTO with SELECT statment.

Answer 1

Looks like the extension you are using supports "insert into ... select .." but not direct inserts.

you can use you should probably ask this question while specifying the extension.

PS: It looks like the extension you use is cstore_fdw. It does not support direct inserts, because it completely cancels benefits of using columnar storage and create some extra overhead. If you are using cstore_fdw, try to use bulk inserts instead of single row ones. Inserting into a regular table and moving data into cstore_fdw table when data reaches certain size (i.e. stripe_row_count number of rows) is much better option.