Comments (8)
dbWriteTable has only a single argument to set for the tablename, so
I think its primary meaning is creating "foo.tmp" in current schema.
If you want to create "tmp" table in "foo" schema
you could try
dbGetQuery(con, "SET search_path TO foo")
dbWriteTable(con, "tmp", data)
Another way I can imagine is to create an optional argument of schema for
dbWriteTable().
Original comment by [email protected]
on 2 Nov 2010 at 5:40
- Added labels: Type-Enhancement
- Removed labels: Type-Defect
from rpostgresql.
I tested my change and it works as expected. Having to change the search_path
to get the right schema is a pretty painful and error prone way to do it (too
easy to leave it set wrong). The sql standard is to reference tables as
database.schema.table so I think its more reasonable to treat the dots this
way.
It is also how RMySQL works (although mysql does not use schemas - it is
database.table there).
Original comment by [email protected]
on 2 Nov 2010 at 9:20
from rpostgresql.
I agree that changing the search_path doesn't look nice.
What do you think is the reasonable handling of dbWriteTable(con, "a.b.c.d.e",
data)?
What is the proper syntax to direct to access "sche.ma"."ta.b.le"?
So, there seems to be no simple way to handle every possibility.
I now have three possible interface in mind
1. optional schema="schema" argument
2. passing a vector of strings c('schema', 'table')
3. optional argument quotetablename=FALSE and ensure the table name supplied
contains no special characters or is quoted properly at the user side.
Original comment by [email protected]
on 2 Nov 2010 at 11:24
from rpostgresql.
I think it should fail if you give it "a.b.c.d.e" as a table name. Thats what
ROracle and RMySQL do (and up until 2 weeks ago so did RPostgreSQL). Adding
optional arguments unsupported by the other db drivers would be a mistake when
supporting the standard sql syntax for schema.table makes interoperating or
porting much easier.
Original comment by [email protected]
on 2 Nov 2010 at 12:01
from rpostgresql.
Interoperability to other PostgreSQL application is more important than
portability of the R code between different database backend that may have
different features.
That is; I think fail for "a.b.c.d.e" without preparing means to access to
"a.b.c.d.e" is a unacceptably bad design.
The conclusion for the capitals consistency discussuion was that arguments for
dbExistsTable, dbReadTable, dbWriteTable are not a SQL, but the name itself
before quoting.
If you stick on the portability you will only use capital alphabet for
identifier and quote always in the SQL statement you write.
The 4th idea is adding an option not to automatically quote identifiers in the
connection for compatibility. This option need not specified every time, but
just specified when the connection is established. But, I don't want
personally implement this as this complicates the code with little benefit. I
am just saying that I don't object to include if someone implements this.
Note, none of DBI, ROracle, RMySQL, and RPostgreSQL have version number > 1 and
ROracle and RMySQL may also change in the future, if the authors thinks it
reasonable.
Perl DBI module defines quote and quote_identifier
http://search.cpan.org/~timb/DBI/DBI.pm
while the R DBI is yet to define these.
I assume these are just because the time was not enough to define and implement
the detail of what and how to quote.
So, I will leave this issue open for someone might have a good
idea/implementation. But, assign low priority.
Original comment by [email protected]
on 3 Nov 2010 at 1:48
- Added labels: Priority-Low, OpSys-All
- Removed labels: Priority-Medium
from rpostgresql.
My idea is to add a postgresTableQuoteName function as
postgresqlTableQuoteName <- function(identifier){
names <- strsplit(identifier, ".", fixed=TRUE)[[1]]
if (length(names) == 2){
res <- paste(postgresqlQuoteId(names[1]),".", postgresqlQuoteId(names[2]),sep="");
}else{
res <- postgresqlQuoteId(identifier);
}
return res;
}
then change the following lines of code in the postgresqlWriteTable as
1.
change
sql1 <- paste("create table ", postgresqlQuoteId(name), "\n(\n\t", sep="")
to
sql1 <- paste("create table ", postgresqlTableQuoteName(name), "\n(\n\t", sep="")
2.
change
sql4 <- paste("COPY", postgresqlQuoteId(name), "FROM STDIN")
to
sql4 <- paste("COPY", postgresqlTableQuoteName(name), "FROM STDIN")
Original comment by [email protected]
on 6 Mar 2011 at 4:47
from rpostgresql.
add if length(names) > 2 we should fail the function.
Original comment by [email protected]
on 6 Mar 2011 at 4:52
from rpostgresql.
Close this issue as with the new spec,
dbWriteTable(con, c("foo","tmp"), data)
should be used to write to a table tmp in schema foo.
Original comment by [email protected]
on 10 Oct 2011 at 7:35
- Changed state: Fixed
from rpostgresql.
Related Issues (20)
- Get autogenerated keys
- Bug linker flag when build with/link to exist postgresql library in custom path HOT 3
- Usage of internal libpq distribution is broken
- Outdated config.guess and config.sub HOT 2
- Error connecting to postgres 13 using RPostgres package in R. “authentication method 10 not supported” HOT 6
- Error loading RPostgreSQL HOT 6
- 'dbWriteTable()' does not write data out of public schema HOT 4
- configure: error: cannot guess build type; you must specify one in [install.packages("RPostgreSQL")] HOT 2
- Issue installing RPostgreSQL on linux HOT 6
- Unable to install in R HOT 6
- Create Roles and users from RPostgreSQL HOT 4
- dbSendQuery segfault on bad SQL query HOT 1
- How to find headers in /usr/include/psql? HOT 2
- How to pass binary_parameters="yes"? HOT 1
- please support PQconnectdb() interface HOT 2
- `dbConnect()` crashes R Studio instead of erroring HOT 4
- CRAN NEWS and Changelog are out of date
- libpq version 10 or above required for authentication HOT 6
- Old problem: libpg version problems HOT 5
- RPostgreSQL 0.7-4 isn't working with libpq 15.1? HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from rpostgresql.