Writing to Specific Schemas with Rpostgresql

Writing to specific schemas with RPostgreSQL

The default schema where objects are created is defined by the search_path. One way would be to set it accordingly. For instance:

SET search_path = myschema, public;

I quote the manual:

When objects are created without specifying a particular target
schema, they will be placed in the first schema listed in the search
path. An error is reported if the search path is empty.

You can also make this the default for a role, so it is set automatically for every connection made by this role. More:

• How does the search_path influence identifier resolution and the "current schema"

Using r sf::st_write to non-public schema in PostgreSQL

This happens because you are connecting to the db via package RPostgreSQL, but the syntax used for specifying table and schema is that used with connections made with package RPostgres. You can solve this using:

    require(RPostgres)
    conn <- dbConnect(Postgres(), dbname = dbname, host = host, port = port, 
                      user = username, password = password)
    st_write(obj = cycle_hire, dsn = conn, Id(schema="roads_spatial", table = "myCycle"))

List tables within a Postgres schema using R

Reading this answer https://stackoverflow.com/a/15644435/2773500 helped. I can use the following to get the tables associated with a specific schema:

dbGetQuery(db,
           "SELECT table_name FROM information_schema.tables
                   WHERE table_schema='sch2014'")

writing tables to Postgresql using rPostgreSQL when the database name is all capital letters

There were definitely issues with tables in upper-case. In think we handle that now:
Try quoting it as "DATA" and it should go through. Unquoted table identifier all get lower-cased.

Your issue is having the entire database in uppercase. It may also work with quoting, maybe even with '\"DATA\"' as an argument to dbConnect.

Otherwise, reproducible examples on the list are best, and with some luck, Tomoaki will find a fix for your problem.

Oh, and we spell it like the package: RPostgreSQL with capital arrrrrrr, especially today on talk like a piRate day.

Edit: Looks like there is simply no issue with current versions on Ubuntu 11.04:

First, create DATA

edd@max:~$ createdb DATA
edd@max:~$ psql DATA
psql (8.4.8)
Type "help" for help.

DATA=# \q
edd@max:~$ 

Second, and in R, connect and save some data:

R> library(RPostgreSQL)
R> con <- dbConnect(PostgreSQL(), host="localhost", user= "edd", 
+                   password=".....", dbname="DATA")
R> con
<PostgreSQLConnection:(21936,0)> 
R> dbWriteTable(con, "quicktest", cars)
[1] TRUE
R> 

Third, check for content in DATA:

DATA=# select * from quicktest limit 5;
 row_names | speed | dist 
-----------+-------+------
 1         |     4 |    2
 2         |     4 |   10
 3         |     7 |    4
 4         |     7 |   22
 5         |     8 |   16
(5 rows)

DATA=# 

Looking good to me.

R - RPostgreSQL Package - dbWriteTable to non-default schema where target table contains more fields than dataframe

Add the missing null fields before the query :

df$extr_col1 <- NA
df$extr_col2 <- NA
...

then run your original dbWriteTable()...

Grant select on schema in Postgres

With event triggers you can handle that :

CREATE OR REPLACE FUNCTION auto_grant_func()
RETURNS event_trigger AS $$
BEGIN
    GRANT CONNECT ON DATABASE postgres TO readonly;
    GRANT USAGE ON SCHEMA schema1 TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO readonly;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER auto_grant_trigger
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE PROCEDURE auto_grant_func();

Inserting to schema-specific table with python's odo

Hidden deep in a mailing list for blaze is a mention of the schema parameter

d = Data(resource('postgresql://localhost/db::t', schema='myschema'))

which can be used with odo with the following format:

from odo import odo, drop
drop(db_uri, schema='my_schema') # to drop table in specific schema
odo(data, db_uri, schema='my_schema')

working code

import pandas as pd
from odo import odo
db_uri = 'postgresql://localhost/postgres::my_table'
odo(pd.DataFrame([{'a': 1}, {'a': 1}]), db_uri, schema='my_schema')

---

Related Topics