duckdb/duckdb-postgres

Meaning of "read into DuckDB tables"

Closed this issue · 3 comments

rinormaloku commented

What happens?

In the sentence below the section in bold is not properly clarified:

The data can be queried directly from the underlying Postgres tables, or read into DuckDB tables.

How can that be done? Is it done automatically? Can I improve performance by reading Postgres data in DuckDB tables?

To Reproduce

This might be the wrong location for this question, but I didn't know where to ask the question

OS:

any

PostgreSQL Version:

any

DuckDB Version:

any

DuckDB Client:

any

Full Name:

Rinor Maloku

Affiliation:

rinormaloku

Have you tried this on the latest master branch?

  • I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

  • I agree
Alex-Monahan commented

Hello!

This is not done automatically, but you can query from Postgres and insert into DuckDB. We have some details in this blog post: https://duckdb.org/2022/09/30/postgres-scanner.html#other-use-cases

Let me know if you have further questions!

rinormaloku commented

Thank you for the prompt reply, I was just reading that article.

I have one more question. When inserting data into DuckDB tables, are those automatically synchronized? (I would expect no, that would be some scary magic, but just verifying that it is not).

Meaning that, to keep the data fresh I would have to clean up the table and create a new one (running the query below). Is that correct?

INSERT INTO my_table_duckdb_cache
SELECT * FROM postgres_scan('dbname=myshinydb', 'public', 'my_table') 
WHERE incrementing_id_column > (SELECT MAX(incrementing_id_column) FROM my_table_duckdb_cache);
Mytherin commented

I have one more question. When inserting data into DuckDB tables, are those automatically synchronized? (I would expect no, that would be some scary magic, but just verifying that it is not).

No, those would not be kept up to date. You either need to manually refresh, or keep querying the original tables.

Closing as the main issue/question has been answered.

  • ❤️1