Nothing
Code
(expect_error(test_translate_sql(quantile(x, 0.3, na.rm = TRUE), window = TRUE))
)
Output
<error/rlang_error>
Error in `quantile()`:
! Translation of `quantile()` in `mutate()` is not supported for PostgreSQL.
i Use a combination of `summarise()` and `left_join()` instead:
`df %>% left_join(summarise(<col> = quantile(x, 0.3, na.rm = TRUE)))`.
Code
(expect_error(test_translate_sql(median(x, na.rm = TRUE), window = TRUE)))
Output
<error/rlang_error>
Error in `median()`:
! Translation of `median()` in `mutate()` is not supported for PostgreSQL.
i Use a combination of `summarise()` and `left_join()` instead:
`df %>% left_join(summarise(<col> = median(x, na.rm = TRUE)))`.
Code
left_join(lf, lf, by = "x", na_matches = "na")
Output
<SQL>
SELECT `df_LHS`.`x` AS `x`
FROM `df` AS `df_LHS`
LEFT JOIN `df` AS `df_RHS`
ON (`df_LHS`.`x` IS NOT DISTINCT FROM `df_RHS`.`x`)
Code
copy_inline(con, tibble(x = integer(), y = character())) %>% remote_query()
Output
<SQL> SELECT CAST(NULL AS INTEGER) AS `x`, CAST(NULL AS TEXT) AS `y`
WHERE (0 = 1)
Code
copy_inline(con, tibble(x = 1:2, y = letters[1:2])) %>% remote_query()
Output
<SQL> SELECT CAST(`x` AS INTEGER) AS `x`, CAST(`y` AS TEXT) AS `y`
FROM ( VALUES (1, 'a'), (2, 'b')) AS drvd(`x`, `y`)
sql_query_insert()
worksCode
(sql_query_insert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
lvl = 1), insert_cols = colnames(df_y), by = c("a", "b"), conflict = "error",
returning_cols = c("a", b2 = "b")))
Condition
Error in `sql_query_insert()`:
! `conflict = "error"` isn't supported on database backends.
i It must be "ignore" instead.
Code
sql_query_insert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
lvl = 1), insert_cols = colnames(df_y), by = c("a", "b"), conflict = "ignore",
returning_cols = c("a", b2 = "b"))
Output
<SQL> INSERT INTO `df_x` (`a`, `b`, `c`, `d`)
SELECT *
FROM (
SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
FROM `df_y`
) AS `...y`
ON CONFLICT (`a`, `b`)
DO NOTHING
RETURNING `df_x`.`a`, `df_x`.`b` AS `b2`
sql_query_upsert()
with method = 'on_conflict' is correctCode
sql_query_upsert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
lvl = 1), by = c("c", "d"), update_cols = c("a", "b"), returning_cols = c("a",
b2 = "b"), method = "on_conflict")
Output
<SQL> INSERT INTO `df_x` (`c`, `d`, `a`, `b`)
SELECT `c`, `d`, `a`, `b`
FROM (
SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
FROM `df_y`
) AS `...y`
WHERE true
ON CONFLICT (`c`, `d`)
DO UPDATE
SET `a` = `excluded`.`a`, `b` = `excluded`.`b`
RETURNING `df_x`.`a`, `df_x`.`b` AS `b2`
Code
db %>% mutate(y = x + 1) %>% explain()
Output
<SQL>
SELECT "test".*, "x" + 1.0 AS "y"
FROM "test"
<PLAN>
QUERY PLAN
1 Seq Scan on test (cost=0.00..1.04 rows=3 width=36)
Code
db %>% mutate(y = x + 1) %>% explain(format = "json")
Output
<SQL>
SELECT "test".*, "x" + 1.0 AS "y"
FROM "test"
<PLAN>
QUERY PLAN
1 [\n {\n "Plan": {\n "Node Type": "Seq Scan",\n "Parallel Aware": false,\n "Async Capable": false,\n "Relation Name": "test",\n "Alias": "test",\n "Startup Cost": 0.00,\n "Total Cost": 1.04,\n "Plan Rows": 3,\n "Plan Width": 36\n }\n }\n]
Code
rows_insert(x, y, by = c("a", "b"), in_place = TRUE, conflict = "ignore",
returning = everything(), method = "on_conflict")
Condition
Error in `rows_insert()`:
! Can't modify database table "df_x".
i Using SQL: INSERT INTO "df_x" ("a", "b", "c", "d") SELECT * FROM ( SELECT "a", "b", "c" + 1.0 AS "c", "d" FROM "df_y" ) AS "...y" ON CONFLICT ("a", "b") DO NOTHING RETURNING "df_x"."a", "df_x"."b", "df_x"."c", "df_x"."d"
Caused by error:
! dummy DBI error
Code
rows_upsert(x, y, by = c("a", "b"), in_place = TRUE, returning = everything(),
method = "on_conflict")
Condition
Error in `rows_upsert()`:
! Can't modify database table "df_x".
i Using SQL: INSERT INTO "df_x" ("a", "b", "c", "d") SELECT "a", "b", "c", "d" FROM ( SELECT "a", "b", "c" + 1.0 AS "c", "d" FROM "df_y" ) AS "...y" WHERE true ON CONFLICT ("a", "b") DO UPDATE SET "c" = "excluded"."c", "d" = "excluded"."d" RETURNING "df_x"."a", "df_x"."b", "df_x"."c", "df_x"."d"
Caused by error:
! dummy DBI error
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.