Any way to automatically ask PostgreSQL to generate the primary key when using dbWriteTable?

[Bill Q]

Hi,

I just got started with R. I am trying to load some data into PostgreSQL using RPostgreSQL. Everything went quite smoothly except for that the table created by using dbWriteTable does not have a primary key.

The ideal solution is to ask PostgreSQL to do a auto-increment with the rows I am going to load into the table so I don't have to manually generate a primary key column in the data frame.

Does anybody know how to do this? 

Many thanks.

[Sean O'Riordain]

For big tables, it is probably faster to create the exact table structure you are looking for and then load it with an insert from table.
Cheers
Sean

--
You received this message because you are subscribed to the Google Groups "RPostgreSQL Development and Discussion List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rpostgresql-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Neil Tiffin]

After you load the data just execute the following SQL:

ALTER TABLE test_1 ADD COLUMN my_id SERIAL PRIMARY KEY;

where test_1 is the table name and my_id is the name of the id column you which to add.

BIGSERIAL should be used if you anticipate the use of more than 2^31 identifiers over the lifetime of the table.

Neil

[Bill Q]

Hi Neil,

Thanks for the reply. The problem is that I need to continue loading more data into the database. If I run the SQL, the newly loaded data won't match the table format. 

Bill

[NISHIYAMA Tomoaki]

Hi Bill,

Would you explain why you need the primary key at all.

With row.names = TRUE (default), the database should have a column "row.name"

that is expected to be unique as was in the R world.

Assigning auto increment column, means you don't know the value for any

particular row in R. What is the benefit at all?

the newly loaded data won't match the table format. 

The table format should not matter quite much.

In the svn version, the data column names are transferred as SQL to specify them.

With older version, additional column may not interfere or produce error for 

different number of values, that I am not sure.

Here is some test transaction that shows how it works.

> library(RPostgreSQL)

> library(datasets)

> con<-dbConnect("PostgreSQL")

> dbWriteTable(con, "rockdata", rock[1:8,])

> dbWriteTable(con, "rockdata", rock[9:12,1:3], append=T)

> dbGetQuery(con,"select * from rockdata")

   row.names area    peri     shape perm

1          1 4990 2791.90 0.0903296  6.3

2          2 7002 3892.60 0.1486220  6.3

3          3 7558 3930.66 0.1833120  6.3

4          4 7352 3869.32 0.1170630  6.3

5          5 7943 3948.54 0.1224170 17.1

6          6 7979 4010.15 0.1670450 17.1

7          7 9333 4345.75 0.1896510 17.1

8          8 8209 4344.75 0.1641270 17.1

9          9 8393 3682.04 0.2036540   NA

10        10 6425 3098.65 0.1623940   NA

11        11 9364 4480.05 0.1509440   NA

12        12 8624 3986.24 0.1481410   NA

> dbGetQuery(con,"ALTER TABLE rockdata ADD COLUMN my_id SERIAL PRIMARY KEY;")

# this is from Neil's post

> dbGetQuery(con,"select * from rockdata")

   row.names area    peri     shape perm my_id

1          1 4990 2791.90 0.0903296  6.3     1

2          2 7002 3892.60 0.1486220  6.3     2

3          3 7558 3930.66 0.1833120  6.3     3

4          4 7352 3869.32 0.1170630  6.3     4

5          5 7943 3948.54 0.1224170 17.1     5

6          6 7979 4010.15 0.1670450 17.1     6

7          7 9333 4345.75 0.1896510 17.1     7

8          8 8209 4344.75 0.1641270 17.1     8

9          9 8393 3682.04 0.2036540   NA     9

10        10 6425 3098.65 0.1623940   NA    10

11        11 9364 4480.05 0.1509440   NA    11

12        12 8624 3986.24 0.1481410   NA    12

> dbWriteTable(con, "rockdata", rock[12:14,1:2], append=T)

[1] TRUE

> dbGetQuery(con,"select * from rockdata")

   row.names  area    peri     shape perm my_id

1          1  4990 2791.90 0.0903296  6.3     1

2          2  7002 3892.60 0.1486220  6.3     2

3          3  7558 3930.66 0.1833120  6.3     3

4          4  7352 3869.32 0.1170630  6.3     4

5          5  7943 3948.54 0.1224170 17.1     5

6          6  7979 4010.15 0.1670450 17.1     6

7          7  9333 4345.75 0.1896510 17.1     7

8          8  8209 4344.75 0.1641270 17.1     8

9          9  8393 3682.04 0.2036540   NA     9

10        10  6425 3098.65 0.1623940   NA    10

11        11  9364 4480.05 0.1509440   NA    11

12        12  8624 3986.24 0.1481410   NA    12

13        12  8624 3986.24        NA   NA    13

14        13 10651 4036.54        NA   NA    14

15        14  8868 3518.04        NA   NA    15

> dbWriteTable(con, "rockdata", rock[14:18,3:4], append=T)

[1] TRUE

> dbGetQuery(con,"select * from rockdata")

   row.names  area    peri     shape perm my_id

1          1  4990 2791.90 0.0903296  6.3     1

2          2  7002 3892.60 0.1486220  6.3     2

3          3  7558 3930.66 0.1833120  6.3     3

4          4  7352 3869.32 0.1170630  6.3     4

5          5  7943 3948.54 0.1224170 17.1     5

6          6  7979 4010.15 0.1670450 17.1     6

7          7  9333 4345.75 0.1896510 17.1     7

8          8  8209 4344.75 0.1641270 17.1     8

9          9  8393 3682.04 0.2036540   NA     9

10        10  6425 3098.65 0.1623940   NA    10

11        11  9364 4480.05 0.1509440   NA    11

12        12  8624 3986.24 0.1481410   NA    12

13        12  8624 3986.24        NA   NA    13

14        13 10651 4036.54        NA   NA    14

15        14  8868 3518.04        NA   NA    15

16        14    NA      NA 0.2316230 82.4    16

17        15    NA      NA 0.1725670 82.4    17

18        16    NA      NA 0.1534810 82.4    18

19        17    NA      NA 0.2043140 58.6    19

20        18    NA      NA 0.2627270 58.6    20

# note this example should work only with the svn version.

-- 

Tomoaki NISHIYAMA

Advanced Science Research Center,

Kanazawa University,

13-1 Takara-machi, 

Kanazawa, 920-0934, Japan