Error when use MERGE in production enviroment

[Maegor]

What happens?

Hello,
I am having trouble executing a MERGE against a PostgreSQL 16.8 database. I run the MERGE from a Python script using Duckdb 1.4.1, when executing the script:

import duckdb as dd 
....
dd.execute(MERGE INTO attached_postgres_db.table  as target
            USING pandas_dataframe as source on (target.field1 = source.field1 )
            WHEN MATCHED THEN UPDATE SET field2 = source.field2                           
            WHEN NOT MATCHED by target then INSERT (field1,field2)  VALUES (     field1,field2) ;)

in my production enviroment the following error occurs:

_duckdb.Error: Failed to prepare COPY "COPY "attached_db"."table" FROM STDIN (FORMAT TEXT, NULL '�')": ERROR: permission denied for table "table"

However, when I run it from my local environment, the query works perfectly. The database is the same.

In my local environment (Ubuntu 22) and in my production environment (Amazon Linux 2023), I target the same Postgresql server.
‘Table’ has ‘user1’ as its owner. In my local environment (Ubuntu), I run the Python script with my “user_local” user, and on the production server (Amazon Linux 2023), the ‘user_prod’ user is used.

To Reproduce

import duckdb as dd
import pandas as pd

# TODO insert hostname and secret_name
dd.sql(
    f"ATTACH IF NOT EXISTS 'host=hostnanme' AS attached_db (TYPE postgres, SECRET secret_name);")

df = pd.DataFrame({
    'date': ['2024-01-31', '2024-02-29', '2024-03-31'],
    'concept': ['ConceptA', 'ConceptB', 'ConceptC'],
    'currency': ['EUR', 'EUR', 'EUR'],
    'amount': [0, 0, 0],
    'closed': [False, False, False]
})

dd.execute("""create table attached_db.table_test as select * from df""")

dd.execute("""
MERGE INTO attached_db.table_test  as target
USING df as source on (target.date = source.date)
WHEN MATCHED THEN UPDATE SET amount = source.amount                             
WHEN NOT MATCHED by target then INSERT ( date,
    concept,
    currency,
    amount,
    closed) VALUES (           source.date,
    source.concept,
    source.currency,
    source.amount_qb,
    false) ;
    """)

OS:

Amazon Linux 2023

PostgreSQL Version:

16.8

DuckDB Version:

1.4..1

DuckDB Client:

Python

Full Name:

Molero

Affiliation:

Darwinex

Have you tried this on the latest main 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