sqldf - Alter syntax to add new column to data frame

Question

I want to add a new field to a data frame by sqldf.

> data(mtcars)
> library(sqldf)
> sqldf("alter table mtcars add newfield  int not null")
Error in result_create(conn@ptr, statement) : 
  Cannot add a NOT NULL column with default value NULL
> 
> sqldf("alter table mtcars add newfield  int")
data frame with 0 columns and 0 rows
Warning message:
In result_fetch(res@ptr, n = n) :
  Don't need to call dbFetch() for statements, only for queries

I get an empty data frame from the last command. The sql expression seems ok. But I don't know what is going wrong.

Answer 1

We can add new column using sqldf (I am not sure about adding not null column):

data(mtcars)
library(sqldf)
sqldf(c("alter table mtcars add newfield numeric","select * from mtcars"))

Sample Output:

     mpg cyl  disp  hp drat    wt  qsec vs am gear carb newfield
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4       NA
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4       NA
3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1       NA
4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1       NA