sbSendQuery support for ALTER TABLE in DuckDb

[alvarocombo]

I'm trying to use DuckDb using R and DBI. The problem arises when I try to add/define a PRIMARY KEY.

In the example bellow I have an error related to a 'not yet implemented' feature. Can someone tell me if there is any roadmap for implementation this feature... or is just a problem in my code :( ?

Additionally, is there any way to go around this?

The error:

> dbExecute(conn, sql_alter)
Error in `dbSendQuery()`:
! rapi_prepare: Failed to extract statements:
Not implemented Error: No support for that ALTER TABLE option yet!
Run `rlang::last_trace()` to see where the error occurred.

The Code:

conn <- dbConnect(duckdb())

sql_create <- glue::glue("CREATE TABLE integers (i INTEGER, j INTEGER);")
sql_alter  <- glue::glue("ALTER TABLE integers ADD PRIMARY KEY (i);")

dbExecute(conn, sql_create)
dbExecute(conn, sql_alter)

Thank you very much.

AC

[krlmlr]

Thanks. Can you please file issues related to the duckdb R package at https://github.com/duckdb/duckdb-r in the future, and discuss issues related to duckdb itself at https://github.com/duckdb/duckdb?

You can add the primary key when creating the table:

library(duckdb)
#> Loading required package: DBI

conn <- dbConnect(duckdb())

sql_create <- glue::glue("CREATE TABLE integers (i INTEGER, j INTEGER, PRIMARY KEY (i));")

dbExecute(conn, sql_create)
#> [1] 0

sql_insert <- glue::glue("INSERT INTO integers VALUES (1, 2);")
dbExecute(conn, sql_insert)
#> [1] 1
dbExecute(conn, sql_insert)
#> Error in `duckdb_result()` at duckdb-r/R/dbSendQuery__duckdb_connection_character.R:13:3:
#> ! rapi_execute: Failed to run query
#> Error: Constraint Error: Duplicate key "i: 1" violates primary key constraint. If this is an unexpected constraint violation please double check with the known index limitations section in our documentation (https://duckdb.org/docs/sql/indexes).

^{Created on 2024-11-21 with reprex v2.1.1}

[alvarocombo]

Dear @krlmlr ,

Thanks for your (extremely fast) reply.
Also thanks for the guidance on subject posting (I'm a newbie on duckdb :( )

Best Regards

AC