Writing a single column of R object into pre-existing postgres db table

Question

I have a working knowledge of R but I haven't used it in conjunction with databases too much. My question seems very similar to this question: update table in postgresql database through r

But I can't get that code to work and the doMC package is not available for recent versions of R.

I'm able to connect to the geodatabase, read in data and manipulate it. I can also write an R object into the geodatabase as an entire table. I'm having trouble though, appending an existing geodatabase table with an R object. Unfortunately it's a secure database, so I can't give my connection information out.

-"wearing", "weekday", and "days" are blank columns in a pre-existing table in the geodatabase

-"participant_id", "date_id" and "gps_time" are populated columns in the pre-existing table that I would like to merge on.

-ucsd is the schema name and sage_choi is the existing table name

choifnl <- structure(list(wearing = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = c("nw", 
"w"), class = "factor"), weekday = c("Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday"), days = c(1, 
1, 1, 1, 1, 1), participant_id = c("0adf37c4-f950-40ad-9370-6bee2f93e935", 
"0adf37c4-f950-40ad-9370-6bee2f93e935", "0adf37c4-f950-40ad-9370-6bee2f93e935", 
"0adf37c4-f950-40ad-9370-6bee2f93e935", "0adf37c4-f950-40ad-9370-6bee2f93e935", 
"0adf37c4-f950-40ad-9370-6bee2f93e935"), date = c("20130202", 
"20130202", "20130202", "20130202", "20130202", "20130202"), 
    time = structure(1:6, .Label = c("00:00:00", "00:01:00", 
    "00:02:00", "00:03:00", "00:04:00", "00:05:00", "00:06:00", 
    "00:07:00", "00:08:00", "00:09:00", "00:10:00", "00:11:00", 
    "00:12:00", "00:13:00", "00:14:00", "00:15:00", "00:16:00", 
    "00:17:00", "00:18:00", "00:19:00", "00:20:00", "00:21:00", 
    "00:22:00", "00:23:00", "00:24:00", "00:25:00", "00:26:00", 
    "00:27:00", "00:28:00", "00:29:00", "00:30:00", "00:31:00", 
    "00:32:00", "00:33:00", "00:34:00", "00:35:00", "00:36:00", 
    "00:37:00", "00:38:00", "00:39:00", "00:40:00", "00:41:00", 
    "00:42:00", "00:43:00", "00:44:00", "00:45:00", "00:46:00", 
    "00:47:00", "00:48:00", "00:49:00", "00:50:00", "00:51:00", 
    "00:52:00", "00:53:00", "00:54:00", "00:55:00", "00:56:00", 
    "00:57:00", "00:58:00", "00:59:00", "01:00:00", "01:01:00", 
    "01:02:00", "01:03:00", "01:04:00", "01:05:00", "01:06:00", 
    "01:07:00", "01:08:00", "01:09:00", "01:10:00", "01:11:00", 
    "01:12:00", "01:13:00", "01:14:00", "01:15:00", "01:16:00", 
    "01:17:00", "01:18:00", "01:19:00", "01:20:00", "01:21:00", 
    "01:22:00", "01:23:00", "01:24:00", "01:25:00", "01:26:00", 
    "01:27:00", "01:28:00", "01:29:00", "01:30:00", "01:31:00", 
    "01:32:00", "01:33:00", "01:34:00", "01:35:00", "01:36:00", 
    "01:37:00", "01:38:00", "01:39:00", "01:40:00", "01:41:00", 
    "01:42:00", "01:43:00", "01:44:00", "01:45:00", "01:46:00", 
    "01:47:00", "01:48:00", "01:49:00", "01:50:00", "01:51:00", 
    "01:52:00", "01:53:00", "01:54:00", "01:55:00", "01:56:00", 
    "01:57:00", "01:58:00", "01:59:00", "02:00:00", "02:01:00", 
    "02:02:00", "02:03:00", "02:04:00", "02:05:00", "02:06:00", 
    "02:07:00", "02:08:00", "02:09:00", "02:10:00", "02:11:00", 
    "02:12:00", "02:13:00", "02:14:00", "02:15:00", "02:16:00", 
    "02:17:00", "02:18:00", "02:19:00", "02:20:00", "02:21:00", 
    "02:22:00", "02:23:00", "02:24:00", "02:25:00", "02:26:00", 
    "02:27:00", "02:28:00", "02:29:00", "02:30:00", "02:31:00", 
    "02:32:00", "02:33:00", "02:34:00", "02:35:00", "02:36:00", 
    "02:37:00", "02:38:00", "02:39:00", "02:40:00", "02:41:00", 
    "02:42:00", "02:43:00", "02:44:00", "02:45:00", "02:46:00", 
    "02:47:00", "02:48:00", "02:49:00", "02:50:00", "02:51:00", 
    "02:52:00", "02:53:00", "02:54:00", "02:55:00", "02:56:00", 
    "02:57:00", "02:58:00", "02:59:00", "03:00:00", "03:01:00", 
    "03:02:00", "03:03:00", "03:04:00", "03:05:00", "03:06:00", 
    "03:07:00", "03:08:00", "03:09:00", "03:10:00", "03:11:00", 
    "03:12:00", "03:13:00", "03:14:00", "03:15:00", "03:16:00", 
    "03:17:00", "03:18:00", "03:19:00", "03:20:00", "03:21:00", 
    "03:22:00", "03:23:00", "03:24:00", "03:25:00", "03:26:00", 
    "03:27:00", "03:28:00", "03:29:00", "03:30:00", "03:31:00", 
    "03:32:00", "03:33:00", "03:34:00", "03:35:00", "03:36:00", 
    "03:37:00", "03:38:00", "03:39:00", "03:40:00", "03:41:00", 
    "03:42:00", "03:43:00", "03:44:00", "03:45:00", "03:46:00", 
    "03:47:00", "03:48:00", "03:49:00", "03:50:00", "03:51:00", 
    "03:52:00", "03:53:00", "03:54:00", "03:55:00", "03:56:00", 
    "03:57:00", "03:58:00", "03:59:00", "04:00:00", "04:01:00", 
    "04:02:00", "04:03:00", "04:04:00", "04:05:00", "04:06:00", 
    "04:07:00", "04:08:00", "04:09:00", "04:10:00", "04:11:00", 
    "04:12:00", "04:13:00", "04:14:00", "04:15:00", "04:16:00", 
    "04:17:00", "04:18:00", "04:19:00", "04:20:00", "04:21:00", 
    "04:22:00", "04:23:00", "04:24:00", "04:25:00", "04:26:00", 
    "04:27:00", "04:28:00", "04:29:00", "04:30:00", "04:31:00", 
    "04:32:00", "04:33:00", "04:34:00", "04:35:00", "04:36:00", 
    "04:37:00", "04:38:00", "04:39:00", "04:40:00", "04:41:00", 
    "04:42:00", "04:43:00", "04:44:00", "04:45:00", "04:46:00", 
    "04:47:00", "04:48:00", "04:49:00", "04:50:00", "04:51:00", 
    "04:52:00", "04:53:00", "04:54:00", "04:55:00", "04:56:00", 
    "04:57:00", "04:58:00", "04:59:00", "05:00:00", "05:01:00", 
    "05:02:00", "05:03:00", "05:04:00", "05:05:00", "05:06:00", 
    "05:07:00", "05:08:00", "05:09:00", "05:10:00", "05:11:00", 
    "05:12:00", "05:13:00", "05:14:00", "05:15:00", "05:16:00", 
    "05:17:00", "05:18:00", "05:19:00", "05:20:00", "05:21:00", 
    "05:22:00", "05:23:00", "05:24:00", "05:25:00", "05:26:00", 
    "05:27:00", "05:28:00", "05:29:00", "05:30:00", "05:31:00", 
    "05:32:00", "05:33:00", "05:34:00", "05:35:00", "05:36:00", 
    "05:37:00", "05:38:00", "05:39:00", "05:40:00", "05:41:00", 
    "05:42:00", "05:43:00", "05:44:00", "05:45:00", "05:46:00", 
    "05:47:00", "05:48:00", "05:49:00", "05:50:00", "05:51:00", 
    "05:52:00", "05:53:00", "05:54:00", "05:55:00", "05:56:00", 
    "05:57:00", "05:58:00", "05:59:00", "06:00:00", "06:01:00", 
    "06:02:00", "06:03:00", "06:04:00", "06:05:00", "06:06:00", 
    "06:07:00", "06:08:00", "06:09:00", "06:10:00", "06:11:00", 
    "06:12:00", "06:13:00", "06:14:00", "06:15:00", "06:16:00", 
    "06:17:00", "06:18:00", "06:19:00", "06:20:00", "06:21:00", 
    "06:22:00", "06:23:00", "06:24:00", "06:25:00", "06:26:00", 
    "06:27:00", "06:28:00", "06:29:00", "06:30:00", "06:31:00", 
    "06:32:00", "06:33:00", "06:34:00", "06:35:00", "06:36:00", 
    "06:37:00", "06:38:00", "06:39:00", "06:40:00", "06:41:00", 
    "06:42:00", "06:43:00", "06:44:00", "06:45:00", "06:46:00", 
    "06:47:00", "06:48:00", "06:49:00", "06:50:00", "06:51:00", 
    "06:52:00", "06:53:00", "06:54:00", "06:55:00", "06:56:00", 
    "06:57:00", "06:58:00", "06:59:00", "07:00:00", "07:01:00", 
    "07:02:00", "07:03:00", "07:04:00", "07:05:00", "07:06:00", 
    "07:07:00", "07:08:00", "07:09:00", "07:10:00", "07:11:00", 
    "07:12:00", "07:13:00", "07:14:00", "07:15:00", "07:16:00", 
    "07:17:00", "07:18:00", "07:19:00", "07:20:00", "07:21:00", 
    "07:22:00", "07:23:00", "07:24:00", "07:25:00", "07:26:00", 
    "07:27:00", "07:28:00", "07:29:00", "07:30:00", "07:31:00", 
    "07:32:00", "07:33:00", "07:34:00", "07:35:00", "07:36:00", 
    "07:37:00", "07:38:00", "07:39:00", "07:40:00", "07:41:00", 
    "07:42:00", "07:43:00", "07:44:00", "07:45:00", "07:46:00", 
    "07:47:00", "07:48:00", "07:49:00", "07:50:00", "07:51:00", 
    "07:52:00", "07:53:00", "07:54:00", "07:55:00", "07:56:00", 
    "07:57:00", "07:58:00", "07:59:00", "08:00:00", "08:01:00", 
    "08:02:00", "08:03:00", "08:04:00", "08:05:00", "08:06:00", 
    "08:07:00", "08:08:00", "08:09:00", "08:10:00", "08:11:00", 
    "08:12:00", "08:13:00", "08:14:00", "08:15:00", "08:16:00", 
    "08:17:00", "08:18:00", "08:19:00", "08:20:00", "08:21:00", 
    "08:22:00", "08:23:00", "08:24:00", "08:25:00", "08:26:00", 
    "08:27:00", "08:28:00", "08:29:00", "08:30:00", "08:31:00", 
    "08:32:00", "08:33:00", "08:34:00", "08:35:00", "08:36:00", 
    "08:37:00", "08:38:00", "08:39:00", "08:40:00", "08:41:00", 
    "08:42:00", "08:43:00", "08:44:00", "08:45:00", "08:46:00", 
    "08:47:00", "08:48:00", "08:49:00", "08:50:00", "08:51:00", 
    "08:52:00", "08:53:00", "08:54:00", "08:55:00", "08:56:00", 
    "08:57:00", "08:58:00", "08:59:00", "09:00:00", "09:01:00", 
    "09:02:00", "09:03:00", "09:04:00", "09:05:00", "09:06:00", 
    "09:07:00", "09:08:00", "09:09:00", "09:10:00", "09:11:00", 
    "09:12:00", "09:13:00", "09:14:00", "09:15:00", "09:16:00", 
    "09:17:00", "09:18:00", "09:19:00", "09:20:00", "09:21:00", 
    "09:22:00", "09:23:00", "09:24:00", "09:25:00", "09:26:00", 
    "09:27:00", "09:28:00", "09:29:00", "09:30:00", "09:31:00", 
    "09:32:00", "09:33:00", "09:34:00", "09:35:00", "09:36:00", 
    "09:37:00", "09:38:00", "09:39:00", "09:40:00", "09:41:00", 
    "09:42:00", "09:43:00", "09:44:00", "09:45:00", "09:46:00", 
    "09:47:00", "09:48:00", "09:49:00", "09:50:00", "09:51:00", 
    "09:52:00", "09:53:00", "09:54:00", "09:55:00", "09:56:00", 
    "09:57:00", "09:58:00", "09:59:00", "10:00:00", "10:01:00", 
    "10:02:00", "10:03:00", "10:04:00", "10:05:00", "10:06:00", 
    "10:07:00", "10:08:00", "10:09:00", "10:10:00", "10:11:00", 
    "10:12:00", "10:13:00", "10:14:00", "10:15:00", "10:16:00", 
    "10:17:00", "10:18:00", "10:19:00", "10:20:00", "10:21:00", 
    "10:22:00", "10:23:00", "10:24:00", "10:25:00", "10:26:00", 
    "10:27:00", "10:28:00", "10:29:00", "10:30:00", "10:31:00", 
    "10:32:00", "10:33:00", "10:34:00", "10:35:00", "10:36:00", 
    "10:37:00", "10:38:00", "10:39:00", "10:40:00", "10:41:00", 
    "10:42:00", "10:43:00", "10:44:00", "10:45:00", "10:46:00", 
    "10:47:00", "10:48:00", "10:49:00", "10:50:00", "10:51:00", 
    "10:52:00", "10:53:00", "10:54:00", "10:55:00", "10:56:00", 
    "10:57:00", "10:58:00", "10:59:00", "11:00:00", "11:01:00", 
    "11:02:00", "11:03:00", "11:04:00", "11:05:00", "11:06:00", 
    "11:07:00", "11:08:00", "11:09:00", "11:10:00", "11:11:00", 
    "11:12:00", "11:13:00", "11:14:00", "11:15:00", "11:16:00", 
    "11:17:00", "11:18:00", "11:19:00", "11:20:00", "11:21:00", 
    "11:22:00", "11:23:00", "11:24:00", "11:25:00", "11:26:00", 
    "11:27:00", "11:28:00", "11:29:00", "11:30:00", "11:31:00", 
    "11:32:00", "11:33:00", "11:34:00", "11:35:00", "11:36:00", 
    "11:37:00", "11:38:00", "11:39:00", "11:40:00", "11:41:00", 
    "11:42:00", "11:43:00", "11:44:00", "11:45:00", "11:46:00", 
    "11:47:00", "11:48:00", "11:49:00", "11:50:00", "11:51:00", 
    "11:52:00", "11:53:00", "11:54:00", "11:55:00", "11:56:00", 
    "11:57:00", "11:58:00", "11:59:00", "12:00:00", "12:01:00", 
    "12:02:00", "12:03:00", "12:04:00", "12:05:00", "12:06:00", 
    "12:07:00", "12:08:00", "12:09:00", "12:10:00", "12:11:00", 
    "12:12:00", "12:13:00", "12:14:00", "12:15:00", "12:16:00", 
    "12:17:00", "12:18:00", "12:19:00", "12:20:00", "12:21:00", 
    "12:22:00", "12:23:00", "12:24:00", "12:25:00", "12:26:00", 
    "12:27:00", "12:28:00", "12:29:00", "12:30:00", "12:31:00", 
    "12:32:00", "12:33:00", "12:34:00", "12:35:00", "12:36:00", 
    "12:37:00", "12:38:00", "12:39:00", "12:40:00", "12:41:00", 
    "12:42:00", "12:43:00", "12:44:00", "12:45:00", "12:46:00", 
    "12:47:00", "12:48:00", "12:49:00", "12:50:00", "12:51:00", 
    "12:52:00", "12:53:00", "12:54:00", "12:55:00", "12:56:00", 
    "12:57:00", "12:58:00", "12:59:00", "13:00:00", "13:01:00", 
    "13:02:00", "13:03:00", "13:04:00", "13:05:00", "13:06:00", 
    "13:07:00", "13:08:00", "13:09:00", "13:10:00", "13:11:00", 
    "13:12:00", "13:13:00", "13:14:00", "13:15:00", "13:16:00", 
    "13:17:00", "13:18:00", "13:19:00", "13:20:00", "13:21:00", 
    "13:22:00", "13:23:00", "13:24:00", "13:25:00", "13:26:00", 
    "13:27:00", "13:28:00", "13:29:00", "13:30:00", "13:31:00", 
    "13:32:00", "13:33:00", "13:34:00", "13:35:00", "13:36:00", 
    "13:37:00", "13:38:00", "13:39:00", "13:40:00", "13:41:00", 
    "13:42:00", "13:43:00", "13:44:00", "13:45:00", "13:46:00", 
    "13:47:00", "13:48:00", "13:49:00", "13:50:00", "13:51:00", 
    "13:52:00", "13:53:00", "13:54:00", "13:55:00", "13:56:00", 
    "13:57:00", "13:58:00", "13:59:00", "14:00:00", "14:01:00", 
    "14:02:00", "14:03:00", "14:04:00", "14:05:00", "14:06:00", 
    "14:07:00", "14:08:00", "14:09:00", "14:10:00", "14:11:00", 
    "14:12:00", "14:13:00", "14:14:00", "14:15:00", "14:16:00", 
    "14:17:00", "14:18:00", "14:19:00", "14:20:00", "14:21:00", 
    "14:22:00", "14:23:00", "14:24:00", "14:25:00", "14:26:00", 
    "14:27:00", "14:28:00", "14:29:00", "14:30:00", "14:31:00", 
    "14:32:00", "14:33:00", "14:34:00", "14:35:00", "14:36:00", 
    "14:37:00", "14:38:00", "14:39:00", "14:40:00", "14:41:00", 
    "14:42:00", "14:43:00", "14:44:00", "14:45:00", "14:46:00", 
    "14:47:00", "14:48:00", "14:49:00", "14:50:00", "14:51:00", 
    "14:52:00", "14:53:00", "14:54:00", "14:55:00", "14:56:00", 
    "14:57:00", "14:58:00", "14:59:00", "15:00:00", "15:01:00", 
    "15:02:00", "15:03:00", "15:04:00", "15:05:00", "15:06:00", 
    "15:07:00", "15:08:00", "15:09:00", "15:10:00", "15:11:00", 
    "15:12:00", "15:13:00", "15:14:00", "15:15:00", "15:16:00", 
    "15:17:00", "15:18:00", "15:19:00", "15:20:00", "15:21:00", 
    "15:22:00", "15:23:00", "15:24:00", "15:25:00", "15:26:00", 
    "15:27:00", "15:28:00", "15:29:00", "15:30:00", "15:31:00", 
    "15:32:00", "15:33:00", "15:34:00", "15:35:00", "15:36:00", 
    "15:37:00", "15:38:00", "15:39:00", "15:40:00", "15:41:00", 
    "15:42:00", "15:43:00", "15:44:00", "15:45:00", "15:46:00", 
    "15:47:00", "15:48:00", "15:49:00", "15:50:00", "15:51:00", 
    "15:52:00", "15:53:00", "15:54:00", "15:55:00", "15:56:00", 
    "15:57:00", "15:58:00", "15:59:00", "16:00:00", "16:01:00", 
    "16:02:00", "16:03:00", "16:04:00", "16:05:00", "16:06:00", 
    "16:07:00", "16:08:00", "16:09:00", "16:10:00", "16:11:00", 
    "16:12:00", "16:13:00", "16:14:00", "16:15:00", "16:16:00", 
    "16:17:00", "16:18:00", "16:19:00", "16:20:00", "16:21:00", 
    "16:22:00", "16:23:00", "16:24:00", "16:25:00", "16:26:00", 
    "16:27:00", "16:28:00", "16:29:00", "16:30:00", "16:31:00", 
    "16:32:00", "16:33:00", "16:34:00", "16:35:00", "16:36:00", 
    "16:37:00", "16:38:00", "16:39:00", "16:40:00", "16:41:00", 
    "16:42:00", "16:43:00", "16:44:00", "16:45:00", "16:46:00", 
    "16:47:00", "16:48:00", "16:49:00", "16:50:00", "16:51:00", 
    "16:52:00", "16:53:00", "16:54:00", "16:55:00", "16:56:00", 
    "16:57:00", "16:58:00", "16:59:00", "17:00:00", "17:01:00", 
    "17:02:00", "17:03:00", "17:04:00", "17:05:00", "17:06:00", 
    "17:07:00", "17:08:00", "17:09:00", "17:10:00", "17:11:00", 
    "17:12:00", "17:13:00", "17:14:00", "17:15:00", "17:16:00", 
    "17:17:00", "17:18:00", "17:19:00", "17:20:00", "17:21:00", 
    "17:22:00", "17:23:00", "17:24:00", "17:25:00", "17:26:00", 
    "17:27:00", "17:28:00", "17:29:00", "17:30:00", "17:31:00", 
    "17:32:00", "17:33:00", "17:34:00", "17:35:00", "17:36:00", 
    "17:37:00", "17:38:00", "17:39:00", "17:40:00", "17:41:00", 
    "17:42:00", "17:43:00", "17:44:00", "17:45:00", "17:46:00", 
    "17:47:00", "17:48:00", "17:49:00", "17:50:00", "17:51:00", 
    "17:52:00", "17:53:00", "17:54:00", "17:55:00", "17:56:00", 
    "17:57:00", "17:58:00", "17:59:00", "18:00:00", "18:01:00", 
    "18:02:00", "18:03:00", "18:04:00", "18:05:00", "18:06:00", 
    "18:07:00", "18:08:00", "18:09:00", "18:10:00", "18:11:00", 
    "18:12:00", "18:13:00", "18:14:00", "18:15:00", "18:16:00", 
    "18:17:00", "18:18:00", "18:19:00", "18:20:00", "18:21:00", 
    "18:22:00", "18:23:00", "18:24:00", "18:25:00", "18:26:00", 
    "18:27:00", "18:28:00", "18:29:00", "18:30:00", "18:31:00", 
    "18:32:00", "18:33:00", "18:34:00", "18:35:00", "18:36:00", 
    "18:37:00", "18:38:00", "18:39:00", "18:40:00", "18:41:00", 
    "18:42:00", "18:43:00", "18:44:00", "18:45:00", "18:46:00", 
    "18:47:00", "18:48:00", "18:49:00", "18:50:00", "18:51:00", 
    "18:52:00", "18:53:00", "18:54:00", "18:55:00", "18:56:00", 
    "18:57:00", "18:58:00", "18:59:00", "19:00:00", "19:01:00", 
    "19:02:00", "19:03:00", "19:04:00", "19:05:00", "19:06:00", 
    "19:07:00", "19:08:00", "19:09:00", "19:10:00", "19:11:00", 
    "19:12:00", "19:13:00", "19:14:00", "19:15:00", "19:16:00", 
    "19:17:00", "19:18:00", "19:19:00", "19:20:00", "19:21:00", 
    "19:22:00", "19:23:00", "19:24:00", "19:25:00", "19:26:00", 
    "19:27:00", "19:28:00", "19:29:00", "19:30:00", "19:31:00", 
    "19:32:00", "19:33:00", "19:34:00", "19:35:00", "19:36:00", 
    "19:37:00", "19:38:00", "19:39:00", "19:40:00", "19:41:00", 
    "19:42:00", "19:43:00", "19:44:00", "19:45:00", "19:46:00", 
    "19:47:00", "19:48:00", "19:49:00", "19:50:00", "19:51:00", 
    "19:52:00", "19:53:00", "19:54:00", "19:55:00", "19:56:00", 
    "19:57:00", "19:58:00", "19:59:00", "20:00:00", "20:01:00", 
    "20:02:00", "20:03:00", "20:04:00", "20:05:00", "20:06:00", 
    "20:07:00", "20:08:00", "20:09:00", "20:10:00", "20:11:00", 
    "20:12:00", "20:13:00", "20:14:00", "20:15:00", "20:16:00", 
    "20:17:00", "20:18:00", "20:19:00", "20:20:00", "20:21:00", 
    "20:22:00", "20:23:00", "20:24:00", "20:25:00", "20:26:00", 
    "20:27:00", "20:28:00", "20:29:00", "20:30:00", "20:31:00", 
    "20:32:00", "20:33:00", "20:34:00", "20:35:00", "20:36:00", 
    "20:37:00", "20:38:00", "20:39:00", "20:40:00", "20:41:00", 
    "20:42:00", "20:43:00", "20:44:00", "20:45:00", "20:46:00", 
    "20:47:00", "20:48:00", "20:49:00", "20:50:00", "20:51:00", 
    "20:52:00", "20:53:00", "20:54:00", "20:55:00", "20:56:00", 
    "20:57:00", "20:58:00", "20:59:00", "21:00:00", "21:01:00", 
    "21:02:00", "21:03:00", "21:04:00", "21:05:00", "21:06:00", 
    "21:07:00", "21:08:00", "21:09:00", "21:10:00", "21:11:00", 
    "21:12:00", "21:13:00", "21:14:00", "21:15:00", "21:16:00", 
    "21:17:00", "21:18:00", "21:19:00", "21:20:00", "21:21:00", 
    "21:22:00", "21:23:00", "21:24:00", "21:25:00", "21:26:00", 
    "21:27:00", "21:28:00", "21:29:00", "21:30:00", "21:31:00", 
    "21:32:00", "21:33:00", "21:34:00", "21:35:00", "21:36:00", 
    "21:37:00", "21:38:00", "21:39:00", "21:40:00", "21:41:00", 
    "21:42:00", "21:43:00", "21:44:00", "21:45:00", "21:46:00", 
    "21:47:00", "21:48:00", "21:49:00", "21:50:00", "21:51:00", 
    "21:52:00", "21:53:00", "21:54:00", "21:55:00", "21:56:00", 
    "21:57:00", "21:58:00", "21:59:00", "22:00:00", "22:01:00", 
    "22:02:00", "22:03:00", "22:04:00", "22:05:00", "22:06:00", 
    "22:07:00", "22:08:00", "22:09:00", "22:10:00", "22:11:00", 
    "22:12:00", "22:13:00", "22:14:00", "22:15:00", "22:16:00", 
    "22:17:00", "22:18:00", "22:19:00", "22:20:00", "22:21:00", 
    "22:22:00", "22:23:00", "22:24:00", "22:25:00", "22:26:00", 
    "22:27:00", "22:28:00", "22:29:00", "22:30:00", "22:31:00", 
    "22:32:00", "22:33:00", "22:34:00", "22:35:00", "22:36:00", 
    "22:37:00", "22:38:00", "22:39:00", "22:40:00", "22:41:00", 
    "22:42:00", "22:43:00", "22:44:00", "22:45:00", "22:46:00", 
    "22:47:00", "22:48:00", "22:49:00", "22:50:00", "22:51:00", 
    "22:52:00", "22:53:00", "22:54:00", "22:55:00", "22:56:00", 
    "22:57:00", "22:58:00", "22:59:00", "23:00:00", "23:01:00", 
    "23:02:00", "23:03:00", "23:04:00", "23:05:00", "23:06:00", 
    "23:07:00", "23:08:00", "23:09:00", "23:10:00", "23:11:00", 
    "23:12:00", "23:13:00", "23:14:00", "23:15:00", "23:16:00", 
    "23:17:00", "23:18:00", "23:19:00", "23:20:00", "23:21:00", 
    "23:22:00", "23:23:00", "23:24:00", "23:25:00", "23:26:00", 
    "23:27:00", "23:28:00", "23:29:00", "23:30:00", "23:31:00", 
    "23:32:00", "23:33:00", "23:34:00", "23:35:00", "23:36:00", 
    "23:37:00", "23:38:00", "23:39:00", "23:40:00", "23:41:00", 
    "23:42:00", "23:43:00", "23:44:00", "23:45:00", "23:46:00", 
    "23:47:00", "23:48:00", "23:49:00", "23:50:00", "23:51:00", 
    "23:52:00", "23:53:00", "23:54:00", "23:55:00", "23:56:00", 
    "23:57:00", "23:58:00", "23:59:00"), class = "factor")), .Names = c("wearing", 
"weekday", "days", "participant_id", "date", "time"), row.names = c(NA, 
6L), class = "data.frame")


update <- function(i) {
      drv <- dbDriver("PostgreSQL")
      con <- dbConnect(drv, dbname = "", host = "",port <-  , user = "", password = "") ##connect w/username and password
      txt <- paste("UPDATE ucsd.sage_choi SET wearing=",choifnl$wearing[i],",weekday=",
      choifnl$weekday[i],",days=",choifnl$days[i],",where participant_id=",
      choifnl$participant_id[i],",AND date_id=", choifnl$date[i],
      "AND gps_time=", choifnl$time[i])
      dbGetQuery(con, txt)
      dbDisconnect(con)
    }

    library("foreach")

    registerDoMC()

    foreach(i = 1:length(choifnl$wearing), .inorder=FALSE,.packages="RPostgreSQL")%dopar%{
      update(i)
    }

####EDIT

This is the final solution (connection info omitted) that works well. I had some issues with encoding and also including the right kinds of quotes (single).

 update <- function(con,i) {
      txt <- paste("UPDATE ucsd.sage_final_choi SET wearing=", paste("'",choifnl$wearing[i],"'",sep=""), 
                   ", weekday=", paste("'",choifnl$weekday[i],"'",sep=""),
                   ", days=", choifnl$days[i],
                   "WHERE participant_id=",paste("'",choifnl$participant_id[i],"'",sep=""),
                   "AND date_id=", paste("'",choifnl$date[i],"'",sep=""),
                   "AND gps_time=", paste("'",choifnl$time[i],"'",sep=""), ";")
      dbSendQuery(con, txt) # edit 2

    }


 drv <- dbDriver("PostgreSQL")
 con <- dbConnect(drv, dbname = "", host = "",
 port <-  5432, user = "", password = "",options="-c search_path= schemaName") ##connect w/username and password
foreach(i = 1:length(choifnl$wearing), .inorder=FALSE,.packages="RPostgreSQL")%do%{
  update(con,i) # passing in the open connection as an argument
}

aaryno · Accepted Answer · 2015-06-06 02:07:30Z

5

+50

I'm assuming here you are using the CRAN library RPostgreSQL and that your question is,

"How can I update an existing postgresql record using R?"

If I interpreted your question correctly, I have good news and I'll make minor mods to your existing code to get it working below. Now, the good news:

The error is in your SQL and
You do not need doMC (nor foreach, for that matter).
You're already connected to your database--that's usually the more onerous part!

If you really need parallelism you can see the basic format for initializing multiple workers with doSNOW. Either way, it is much easier to debug a single-threaded application so you should just straight up change your loop to a single-threaded loop by changing the %dopar% argument at the end of your foreach statement to %do% and then register your parallel backend AFTER you get the SQL working.

foreach(i = 1:length(choifnl$wearing),.inorder=FALSE,.packages="RPostgreSQL") %do% {
  update(i)
}

Then you might see that your SQL has a syntax error, notably in that "where" and your first "and" erroneously follow commas. I typically break large statements into more human readable form like below so it is easier to spot inconsistencies in form. I removed the inadvertent commas in this snippet below:

### SQL error resolved
update <- function(i) {
  drv <- dbDriver("PostgreSQL")
  con <- dbConnect(drv, dbname = "", host = "",port <-  , user = "", password = "") ##connect w/username and password
  txt <- paste("UPDATE ucsd.sage_choi SET wearing=", choifnl$wearing[i], 
        ", weekday=", choifnl$weekday[i], 
        ", days=", choifnl$days[i],
        "where participant_id=",choifnl$participant_id[i],
        "AND date_id=", choifnl$date[i],
        "AND gps_time=", choifnl$time[i])
  dbGetQuery(con, txt)
  dbDisconnect(con)
}

From a performance standpoint, you are going to do much better if you initialize your connection outside the for loop because you do not need to sink the time and cost to re-establish the connection for each record.

### SQL error resolved, connection made outside loop
update <- function(con,i) {
  txt <- paste("UPDATE ucsd.sage_choi SET wearing=", choifnl$wearing[i], 
        ", weekday=", choifnl$weekday[i], 
        ", days=", choifnl$days[i],
        "where participant_id=",choifnl$participant_id[i],
        "AND date_id=", choifnl$date[i],
        "AND gps_time=", choifnl$time[i])
  dbSendQuery(con, txt) # edit 2

}

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "", host = "",port <-  , user = "", password = "") ##connect w/username and password
foreach(i = 1:length(choifnl$wearing), .inorder=FALSE,.packages="RPostgreSQL")%dopar%{
  update(con,i) # passing in the open connection as an argument
}
dbDisconnect(con)

edited Jun 6, 2015 at 2:07

answered Jun 1, 2015 at 22:47

aaryno

6365 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Misc Over a year ago

This is great Aaryn, and thank you for the extra tips as well!

Misc Over a year ago

Hi Aaryn. After running this with my data and correct connection info I do get this error: Error in { : task 1 failed - "argument "i" is missing, with no default". I'm not sure what I'm missing. I don't have a lot of experience with the foreach() function. Could this also been done with something in the apply family? Also, I'm using %do%, as you suggested, since I don't need parallelism.

aaryno Over a year ago

My bad. I forgot to include the connection as an argument in the function call for update(...). I made the edit. so the update is called with con and i as the arguments

Misc Over a year ago

I'm getting this error now: Error in { : task 1 failed - "unable to find an inherited method for function ‘dbSendQuery’ for signature ‘"character", "missing"’" And sorry it's taking me so long to respond, but this code takes quite some time to run.

aaryno Over a year ago

My fault, I left out the connection, which we need to pass to all the rpostgresql functions. See edit 2.

akhmed · Accepted Answer · 2015-06-03 00:20:57Z

2

If I understood the question correctly, this is fairly easy using dplyr's ability to connect to databases

library(dplyr)
library(RPostgreSQL)

my_db <- src_postgres(host="<HOST>", 
                      user="<USER>",
                      password="<PASS>",
                      db = "ucsd")


sage_choi <- tbl(my_db,"sage_choi")

sage_choi %>%
  select( participant_id, date_id, gps_time) %>%
  left_join( choifnl, copy=TRUE, by=c("participant_id"="participant_id",
                                      "date_id"="date",
                                      "gps_time"="time")) %>% 
  compute(name="sage_choi2", temporary=FALSE)

Once you run the code, table sage_choi2 would contain the populated columns.

edited Jun 3, 2015 at 0:20

answered Jun 3, 2015 at 0:15

akhmed

3,6332 gold badges27 silver badges36 bronze badges

2 Comments

Misc Over a year ago

This seems to work too. Although I do like how @Aaryn Olsson 's answer appends the existing table and doesn't make a new one.

akhmed Over a year ago

Yes, you are correct! dplyr is based on functional-programming paradigm: f(x) never changes x, instead it returns a modified copy of x: y <- f(x). The advantage here is: we do not create a separate UPDATE query for every (participant_id, date_id, gps_time). If choifnl is large, updating each record with a separate query may take a while.

Collectives™ on Stack Overflow

Writing a single column of R object into pre-existing postgres db table

2 Answers 2

"How can I update an existing postgresql record using R?"

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

"How can I update an existing postgresql record using R?"

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related