Skip to contents

Check key uniqueness inside the database

Source: R/memory.R
db_check_unique.Rd

Checks that the key columns identify each row of a table once, with a SQL aggregation run by DuckDB: the table is never loaded into R, which makes the check possible on tables of tens of millions of rows (a sanity check on text or text_cleaned). check_unique() does the same on a data frame.

Usage

db_check_unique(con, table, keys, n_examples = 5L)

Arguments

con

A DBI connection, or the path of a .duckdb file.

table

Character. Name of the table.

keys

Character. Key columns.

n_examples

Integer. Number of duplicated keys returned.

Value

A data.table of duplicated keys with their number of rows (n), at most n_examples rows; no row when the keys are unique.

See also

Other memory: db_query_chunks(), free_memory_gb()

Examples

con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "author", data.frame(
  doc_id = c("a:1", "a:1", "a:2"),
  author_order = c(1, 1, 1)
))
db_check_unique(con, "author", c("doc_id", "author_order"))
#> ✖ author: 1 duplicated value of doc_id and author_order.
db_disconnect(con)