Data manipulation with cstore_fdw
222 views
Skip to first unread message
Alik
Jun 4, 2015, 8:54:44 PM6/4/15
to cstore...@googlegroups.com
I've read all available cstore_fdw documentation, FAQs and posts to this group. Can someone confirm or deny my conclusions on the data manipulation methods available for cstore_fdw tables?
1. As of 1.2 INSERT, UPDATE, DELETE are not supported
2. There are only two methods to insert data into cstore_fdw tables: COPY from .csv file or INSERT INTO ... SELECT FROM ...
3. There are no workarounds to make UPDATE possible
4. Both DELETE and TRUNCATE are not supported. If I need to empty a cstore_fdw table and reclaim storage space I can only drop the table and create a new one.
5. In my tests in order to empty cstore tables I tried to delete the related files from $PGDATA/cstore_fdw/ directory. It sims to be working just fine. I was able to reuse the same foreign table again. Is there any danger in using this method?
Thank you!
--Alik
Hadi Moshayedi
Jun 5, 2015, 2:28:01 AM6/5/15
to Alik, cstore...@googlegroups.com
Hello Alik,
1. As of 1.2 INSERT, UPDATE, DELETE are not supported2. There are only two methods to insert data into cstore_fdw tables: COPY from .csv file or INSERT INTO ... SELECT FROM ...3. There are no workarounds to make UPDATE possible4. Both DELETE and TRUNCATE are not supported. If I need to empty a cstore_fdw table and reclaim storage space I can only drop the table and create a new one.
Correct.
5. In my tests in order to empty cstore tables I tried to delete the related files from $PGDATA/cstore_fdw/ directory. It sims to be working just fine. I was able to reuse the same foreign table again. Is there any danger in using this method?
No danger. You should be able to use the table again after adding some new data.
--
Hadi Moshayedi
Computer Programmer | Citus Data
Alik
Jun 5, 2015, 4:59:56 AM6/5/15
to cstore...@googlegroups.com
Thank you!
Josef Machytka
Nov 5, 2015, 9:07:07 AM11/5/15
to cstore users
Hi Alik
as for not working INSERT you can use this workaround (I have foreign cstore table documents, direct insert is not working):
insert into documents (customer_id, document_info, document_content_json)
select * from (values (1,
'{ "name": "first_document", "author": "Erica Hernandez"}'::json,
'{ "line1": "fdjfjskjskfjskfjsk" }'::json))
as t(customer_id, document_info, document_content_json)
Which means that is works as you need to insert one record without the need for another table.
as for not working INSERT you can use this workaround (I have foreign cstore table documents, direct insert is not working):
insert into documents (customer_id, document_info, document_content_json)
select * from (values (1,
'{ "name": "first_document", "author": "Erica Hernandez"}'::json,
'{ "line1": "fdjfjskjskfjskfjsk" }'::json))
as t(customer_id, document_info, document_content_json)
Which means that is works as you need to insert one record without the need for another table.
Reply all
Reply to author
Forward
0 new messages