New SQL autocompletion feature doesn't work with schemas e. g. postgresql public."any_table"


#1

The new sql autocompletion feature introduced in R Studio 1.2 is not working for me with schemas in postgresql, only works if you ommit to specify the schema, but this doesn't scale well if you have schemas other than the default "public".

Also I haven't been able to find the new dialog box for postgresql connections (the one with keyring integration that asks for a password) I just have the same old one that asks for parameters.


#2

Re: autocompletion, any chance you can provide us with a reproducible example? For example, the following works with this connection:

```{r}
library(RPostgres)
conn <- dbConnect(RPostgres::Postgres())
dbWriteTable(conn, "mtcars", mtcars)
```

```{sql, connection=conn}
select mpg, cyl from mtcars
```

Is the notion of a Postgres 'catalog' unique compared to the traditional notion of a database? Is there a way I can bootstrap a database so I can try testing locally?


#3

I'm sorry I have confused catalogs with schemas (there are so many postgresql objects that some times I get confused).

Schemas are part of the ANSI SQL standard. They are the immediate next level of organization within each database... Most database objects first belong to a schema, wich belongs to a database. (Regina O. Obe 2017)

In your example the mtcarstable gets written to the default schema "public" so if I explicitly specify the schema (wich is a necessary practice for postgresql) I can't use autocompletion.

select mpg, cyl from public.mtcars

#4

Got it! Thanks for the clarification; I've filed this at https://github.com/rstudio/rstudio/issues/3593. We'll try to get this working properly in time for the v1.2 final release.


#5

It's working now on build 1.2.1032, but only if the names are unquoted


#6

You're right -- we'll get this fixed up. Thanks again for testing and reporting back with the issues you're encountering!


#7

No, thank to you! for your work on this amazing tool that is RStudio


#8

Its working now with quoted names but just up to the schema level, so is it possible not to include the database name by default?