1

I am new to connecting to Redshift via R, I have read other questions but am still getting an error when I attempt to create a table. I have successfully set up a connection and I thought set up the table successfully:

redshiftcon <- dbConnect(mm, user="username", password="secret_password",
                    dbname="dbtable", host="hostname", port="portnumber")


dbSendQuery(redshiftcon,
"create table ss_playground.test_table (unique_id VARCHAR,
category VARCHAR,
name VARCHAR,
number_min float);")

<PostgreSQLResult:(70214,5,1)>

However, when I attempt to check if the table exists and if the fields are there, I get the following message:

dbExistsTable(redshiftcon, ss_playground.test_table)

Error in is(object, Cl) : 
error in evaluating the argument 'name' in selecting a method for function
'dbExistsTable': Error: object 'ss_playground.test_table' not found

> dbExistsTable(redshiftcon, 'ss_playground.test_table')
[1] FALSE

I am confused because I thought the table was created successfully, but also cannot find it in the database itself. When I attempt to send it and create it again, I get the following:

> dbSendQuery(redshiftcon,
         "create table ss_playground.test_table (unique_id VARCHAR,
category VARCHAR,
name VARCHAR,
number_min float);")

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  Relation   
'test_table' already exists)

Is there something I am missing?

Please help! Thank you

RCN
  • 671
  • 1
  • 5
  • 17
  • 1
    I am not sure, but try adding `commit;` after you create table. It is possible that your changes are session specific and you might wanna commit them first. – rohitkulky Jan 13 '16 at 14:49

1 Answers1

2

I think that ss_playground is not the default schema for this user/role. You can have a go at setting the schema as default. Have a peek here.

To fix your code quickly you can try: 

dbExistsTable(redshiftcon, c("ss_playground","test_table"))

or hack it up as 

any(grepl("test_table",dbListTables(redshiftcon)))
fahad daniyal
  • 269
  • 4
  • 14
  • Thank you that was very helpful! with this code, I see that in fact the table exists. `> dbExistsTable(redshiftcon, c("ss_playground","test_table")) [1] TRUE` Will I need to follow up to grant permissions ? – RCN Jan 13 '16 at 15:12
  • 1
    Assuming your user ``username`` has access to schema, simply run ``SET search_path = ss_playground, public;``. So looks like ``rs <- dbSendQuery(conn =redshiftcon, "SET search_path =ss_playground, public;")``. Then you don't need to do ``c(schema, tabl_name)`` and can get by just asking by ``dbExistsTable(redshiftcon, test_table)``. – fahad daniyal Jan 13 '16 at 15:19
  • I dont particularly want to make that entire schema public, is there a way to make only that particular table public instead? I was playing around with `> dbSendQuery(redshiftcon, "grant select on test_table in schema ss_playground to readonly;")` where readonly is another user – RCN Jan 13 '16 at 15:44
  • well its not making this public, its just saying each query should be first tries on schema ``ss_playground`` and then on ``public``. Have a go at reading through the redshift docs http://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html – fahad daniyal Jan 13 '16 at 15:51