[R-sig-DB] Extend dbWriteTable to specify a primary key

[Douglas Bates]

On Thu, Mar 25, 2010 at 7:20 PM, Gabor Grothendieck
<ggrothendieck using gmail.com> wrote:
> You don't have to define column types when you create a table in
> SQLite so this works:
>
> s <- sprintf("create table %s(%s, primary key(%s))", "DF",
>                paste(names(DF), collapse = ", "),
>                names(DF)[1])
> dbGetQuery(con, s)
> dbWriteTable(con, "DF", DF, append = TRUE, row.names = FALSE)

Thanks.

> On Thu, Mar 25, 2010 at 7:05 PM, Douglas Bates <bates using stat.wisc.edu> wrote:
>> I am going to create a view from a select over several tables that are
>> created in R.  I purposely generate a value suitable as a primary key
>> in these tables but I have not decided how to specify it in a
>> dbWriteTable.  Instead I go through a complicated operation of
>> creating the table with a few records, dumping the create table
>> definition that was used, editing it to declare the primary key,
>> dropping the table, creating the table from SQL with the primary key
>> and then using dbWriteTable with append = TRUE.
>>
>> Is there a better way?  This seems somewhat baroque. If no better way
>> currently exists would it be reasonable to specify a primary key in a
>> dbWriteTable call.
>>
>> I should say that I am using RSQLite and SQLite apparently doesn't
>> allow you to declare a primary key in an ALTER TABLE statement.
>>
>> _______________________________________________
>> R-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB using stat.math.ethz.ch
>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>
>