Distinct in R while connecting to PostgreSQL using DBI Package

Question

The below code prints:

SELECT "district_code" FROM sd_stage.table1 GROUP BY "district_code"

but I am expecting:

select distinct(district_code) from sd_stage.table1

Code:

library(DBI)
library(tidyverse)
library(dbplyr)

conn_obj <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), 
                           host = "127.0.0.1",
                           user = "testingdb",
                           password = "admin@123")
on.exit(DBI::dbDisconnect(conn_obj))

tbl_oil_root_segment <- dplyr::tbl(conn_obj, 
       dbplyr::in_schema('sd_stage','table1'))

tbl_oil_root_segment %>% distinct(oil_district) %>% show_query()

Output is correct but the query which is generated seems to be not 100%. So is there anyway I can implement the query?

score 1 · Answer 1 · answered Sep 27 '17 at 10:56

1

tbl_oil_root_segment %>% select(oil_district) %>% distinct %>% show_query()

will create the query you expect.

However, note that in SQL select distinct a from t is the same as select a from t group by a (see this question).

answered Sep 27 '17 at 10:56

Scarabee

5,437
5
29
55

how can I use order_by in the above code you have mentioned so oil_district is in sorted order... – Sharath Sep 28 '17 at 07:17
You can use `arrange` from dplyr. – Scarabee Sep 28 '17 at 10:04

Distinct in R while connecting to PostgreSQL using DBI Package

1 Answers1