PostgreSQL connection via Unix sockets in DatabaseLink?

Questions
, ,
1

Hello,
Is it possible to connect to a PostgreSQL database via unix sockets?

I see that DatabaseReference does allow sockets to be specified in the "Host" parameter.

However, I want to execute raw SQL queries and apparently this is possible only with Needs["DatabaseLink"]

The option

OpenSQLConnection[JDBC["org.postgresql.Driver", "jdbc:postgresql://" <> dbHost"]]

doesn’t work (I get Connection to :5432 refused). There is no "Database" option there as well.

2

There are many reasons why a connection can be refused. Is dbHost a localhost?

Try

conn = OpenSQLConnection[
    JDBC["PostgreSQL", "<host_name>/<db_name>"],
    "Username" -> "<user_name>", "Password" -> "<password>"
    ]

E.g to connect to a public RNA sequence PostgreSQL database named pfmegrnargs hosted by RNACentral

conn = OpenSQLConnection[
    JDBC["PostgreSQL", "hh-pgsql-public.ebi.ac.uk/pfmegrnargs"],
    "Username" -> "reader", "Password" -> "NWDMCE5xdipIjRrp"
    ]

tables = SQLTableNames[conn]

Execute query using SQL string (WL style SQL queries would be an alternative)

SQLExecute[conn, "select * from xref where ac = 'OTTHUMT00000106564.1'"]

Close connection

CloseSQLConnection[conn]
3

My dbHost is a unix socket, i.e. /var/lib/postgres. The connection string postgresql:///var/lib/postgres/somedb doesn’t work apparently.