Group Abstract Group Abstract

Message Boards Message Boards

PostgreSQL connection via Unix sockets in DatabaseLink?

Przemyslaw K.
Posted 2 years ago

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.
POSTED BY: Przemyslaw K.
2 Replies
Sort By:
Przemyslaw K.
Przemyslaw K.
Posted 2 years ago

My dbHost is a unix socket, i.e. /var/lib/postgres. The connection string postgresql:///var/lib/postgres/somedb doesn't work apparently.
POSTED BY: Przemyslaw K.
Rohit Namjoshi
Rohit Namjoshi
Posted 2 years ago

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]
POSTED BY: Rohit Namjoshi
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard