Query Postgres from Duckdb

Question

Does anyone here use duckdb to query PostgreSQL?

ATTACH postgres_attach('hostaddr=192.168.0.1 port=5432 dbname=mydb connect_timeout=20 user=postgres 
password=masterkey');

always returns like this ^

SQL Error: Invalid Error: Failed to prepare COPY " COPY (SELECT NULL FROM "public"."acessohabilitacao" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid) TO STDOUT (FORMAT binary); FROM ibroker.main.acessohabilitacao;` ERRO: erro de sintaxe em ou próximo a "binary" LINE 2: ...'::tid AND '(4294967295,0)'::tid) TO STDOUT (FORMAT binary); ^ the query

SELECT 
    ['idacessohabilitacao']
FROM 
    ibroker.main.acessohabilitacao
WHERE 
    ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid) 
TO STDOUT (FORMAT binary);

which version of duck db are you using? from documentation The old postgres_attach function is deprecated. It is recommended to switch over to the new ATTACH syntax. duckdb.org/docs/extensions/postgres.html — roman
– roman, Commented Jun 21, 2024 at 20:35
Perhaps, should be TO STDOUT WITH(FORMAT binary) or TO STDOUT binary . See (postgresql.org/docs/current/sql-copy.html) — ValNik
– ValNik, Commented Jun 22, 2024 at 21:14
SQL Error: Invalid Error: Failed to prepare COPY " COPY (SELECT "idacessosiscomex", "maxforcode", "cdacessosiscomex", "nmusuario", "cpfusuario", "senhadrawback", "paginasiscomex", "tpusuario", "seqinicialidarquivosiscomexweb", "nmloginanvisa", "snativo", "snvisualizarsenha" FROM "public"."acessosiscomex" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid) TO STDOUT (FORMAT binary); ": ERRO: erro de sintaxe em ou próximo a "binary" LINE 2: ...'::tid AND '(4294967295,0)'::tid) TO STDOUT (FORMAT binary); — Gustavo Leo
– Gustavo Leo, Commented Jun 26, 2024 at 20:13

MSS · Accepted Answer · 2024-08-22 06:08:09Z

0

I am able to access postgres from DuckDb using the following syntax:

import duckdb
duckdb.sql("""
ATTACH '  
        dbname=postgresdb
        hostaddr=xxx.xx.xxx.xxx
        port=1234
        user=postgresusr
        password=postgresusr@123
    ' AS db (TYPE postgres, READ_ONLY);  

""")

duckdb.sql("""
SELECT * 
FROM db.schema_nm.input_tbl_nm;

""")

answered Aug 22, 2024 at 6:08

MSS

3,6832 gold badges28 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

Query Postgres from Duckdb

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