CODEWITHSUNDEEP
rpostgresqlfunctionprimary-key
Alfredo G Marquez
Alfredo G Marquez

Reputation: 173

Is there a function in RPostgres that creates/identifies primary keys for a table in Postgres?

I need to first identify if a table has a primary key created for it, then I need to create that primary key if it does not, or change it if it does. Are there functions in RPostgres that will provide information on the primary keys for a table in Postres, and is there a function that will allow me to create/overwrite the primary keys for said table?

Thanks!

I have used dbSendQuery to create the primary index using SQL, but if the primary key is already created, then the code bombs out. Example below:

keys_create_tbl <-  dbplyr::build_sql(
      "ALTER TABLE ", dplyr::sql(table_name),
      " ADD CONSTRAINT ", dplyr::sql(paste0(table_name,"_keys"))," PRIMARY KEY (",dplyr::sql(primary_keys),");
      ")
# Send the query to Postgres
RPostgres::dbSendQuery(pg_conn, keys_create_tbl)

Upvotes: 2

Views: 267

Answers (1)

Kyouma
Kyouma

Reputation: 400

No, but here's one that'll work. Drop the existing first, using IF EXISTS to suppress the error if you're creating the key for the first time.

create_primary_key <- function(conn, tableName, colName) {
  dbSendQuery(conn, 
    paste0("ALTER TABLE ", tableName,
      " DROP CONSTRAINT IF EXISTS ",tableName, "_pkey;"))
  
  dbSendQuery(conn, 
    paste0("ALTER TABLE ", tableName,
      " ADD PRIMARY KEY (",colName, ");"))
}

Upvotes: 2

Related Questions