Is it possible to run Postgres backslash commands via RPostgresql?

Question

Postgres has many convenient "backslash" commands (e.g \dt, \du, \l, etc). I would like to run these via the RPostgresql interface. I have tried the following:

drv = dbDriver("PostgreSQL")
con <- dbConnect(drv, 
                 dbname = "my_database", 
                 host = "**********", 
                 port = ****, 
                 user = "******", 
                 password = "******")

dbExecute(con, "\\dt")

# Close PostgreSQL connection  
dbDisconnect(con)

However, I get the following error:

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or 
near "\"
LINE 1: \dt
        ^
)

Is it possible to run these from RPostgresql? Or are backslash commands restricted to command line psql only?

At least on problem is the way that you have entered the command. As the error message says, it did not recognize `\d`. That is because R is trying to interpret `\d` as having a special meaning. Instead, to pass in the string `\d`, you need to "escape" the backslash with another backslash. Please try `\\dt` — G5W, Aug 13 '18 at 19:31

score 6 · Accepted Answer · edited Aug 14 '18 at 01:25

The backslash-commands are interpreted by the psql CLI tool, the underlying client libraries don't know what they are are nor does the PostgreSQL server. RPostgresql will be using the client libraries (or perhaps directly speaking the PostgreSQL protocol to communicate with the server) so the backslash-commands won't be available.

However, the backslash-commands are mostly just convenient wrappers for queries that access the PostgreSQL system tables. psql has a -E switch that will let you see these queries:

-E
--echo-hidden
Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

So you can run psql -E ... to see how, for example, \dt is implemented:

=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

                       List of relations
...

and then run the SQL like any other query in RPostgreSQL.

While this may be true I'm positive it's irrelevant since the issue is with the way R interprets strings. The comment by @G5W is the answer here. — Dason, Aug 13 '18 at 20:02
@Dason Not at all irrelevant, the backslash commands are only known by `psql` so fixing the escaping will just lead to another error but this time from PostgreSQL. The question is "Is it possible to run these from RPostgresql? Or are backslash commands restricted to command line psql only?" and the answer is "no but ...". — mu is too short, Aug 13 '18 at 20:23
@muistooshort, So the psql command line program is what interprets the backslash commands? That is what I needed to know. — ichbinallen, Aug 13 '18 at 21:43
Yes but you can find out the SQL behind those commands if you need the information outside of `psql`. — mu is too short, Aug 13 '18 at 22:05

Is it possible to run Postgres backslash commands via RPostgresql?

1 Answers1