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.

Which FDW are you using? Please show us the create foreign server statement as well. — user330315
– user330315, Commented Apr 1, 2019 at 8:24

Murat Tuncer · Accepted Answer · 2019-04-01 10:41:45Z

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.

answered Apr 1, 2019 at 10:41

Murat Tuncer

1463 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

POSTGRES - INSERT INTO FOREIGN TABLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related