Issue found on page 'PostgreSQL Extension'

[dominijk]

Please describe the problem you encountered in the DuckDB documentation and include the "Page URL" link shown below. Note: only create an issue if you wish to report a problem with the DuckDB documentation. For questions about DuckDB or the use of certain DuckDB features, use GitHub Discussions, Stack Overflow, or Discord.

Page URL: https://duckdb.org/docs/extensions/postgres.html

On this page I find https://duckdb.org/docs/extensions/postgres.html#:~:text=Configuring%20via%20Environment%20Variables the docs unclear and this doesn't work for me. Testing in a notebook i run a bash cell with

export PGPASSWORD="secret"
export PGHOST=localhost
export PGUSER=owner
export PGDATABASE=mydatabase

then

ATTACH '' AS p (TYPE POSTGRES); but get no connection, I tried

-- connect to the Postgres instance with the given parameters in read-only mode ATTACH 'dbname=PGDATABASE user=PGUSER host=PGHOST' AS db (TYPE POSTGRES, READ_ONLY);

Which causes error

IOException: IO Error: Unable to connect to Postgres at dbname=PGDATABASE user=PG_USERNAME host=PGHOST password=PG_PWD: could not translate host name "PG_ADDRESS" to address: nodename nor servname provided, or not known

[szarnyasg]

Hi @dominijk, I recommend testing this is in CLI using the psql client. For example, on my local setup, I can run:

export PGPASSWORD=""
export PGHOST=localhost
export PGUSER=gabor
export PGDATABASE=postgres

psql

which results in a successful connection.

[dominijk]

Thank you, for those that do want to do this within a notebook the below works and could be further refined. This is using a magic-duckdb call where -j is inserting variables stored in python which can be used in the query with {{variable}} syntax. You can combine that with dot env etc. to protect your db credentials

%%dql -j
-- connect to the Postgres instance with the given parameters in read-only mode
ATTACH 'password={{PG_PWD}} dbname={{PG_DBNAME}} user={{PG_USERNAME}} host={{PG_ADDRESS}}' 
AS db_alias (TYPE POSTGRES, READ_ONLY);

[szarnyasg]

Thanks, I'll elaborate on this in the docs.