PostgreSQL.jl no longer works in v0.6

Specific Domains Web Stack
21

I have found a solution, but it is not such that you can use it in the source code:

For the server producing the error referred to above, I added

newpgtype(:text, 107980182, ()) to types.jl

but

on another computer the oid (object id) for citext is 427189 and on a third one 30652.

The next step would be to let the driver looks up the oid for citext with a query like this:

select oid from pg_type where typname = 'citext';

and add the correct newpgtype to the code.

Johann

22

I’ve been having trouble with lack of SQL support in Julia for a while. Part of this is just a result of the fact that SQL is actually a million different things masquerading as one thing. I have updated the SQLAlchemy.jl to work with 0.6 in case anyone is interested. Wrapping C in Python in Julia is ridiculous, but a huge amount of work has gone into sqlalchemy to get it to work reasonably well with a huge number of different SQL back-ends, so it’s very difficult to reproduce.

2 Likes
23

[I didn’t look up context. In case it matters, what is it?]

OID is the same(?) as row_id in other databases, but note:

“OIDs are not added to user-created tables, unless […]
So, using a user-created table’s OID column as a primary key is discouraged. OIDs are best used only for references to system tables.”

This applies to all currently supported versions, and is changed from the ancient 8.0 version:

24

To connect to postgres and retrieve data we can now use LibPQ.jl package. Unfortunately, the documentation is not good for this package. I took some time to figure out how to retrieve data. Below code snippet works well:

using NamedTuples;
using DataStreams;
using LibPQ;

v_postgres_host = "my.postgresServer.us-west-1.rds.amazonaws.com"; # Input the hostname here
v_postgres_dbname = "myPostGresDB"; # input the db name here 
v_postgres_user = "mahesh";   # input the postgres user
v_postgres_password = "myPass";  # input the password for the postgres user here 
v_port ="5432"; # input the port number of postgres db here
#create the connection here 
conn_string = "host=" *v_postgres_host *" port=" *v_port *" dbname=" *v_postgres_dbname *" user=" *v_postgres_user *" password=" *v_postgres_password *"";
conn = LibPQ.Connection(conn_string);
data = fetch!(NamedTuple, execute(conn, "SELECT department_id FROM school_table WHERE subject_name = \$1", ["physics"]));
close(conn);

To retrieve information from the data variable use the following:
data[:department_id]

5 Likes
25

Thanks - I’ve seen the update on the PostgreSQL.jl README. This is very useful. Maybe you could add it to the LibPQ.jl README as well? I checked it and indeed, it is lite on details.

26

I want to ask how to insert

sslmode='require'

to your command.

27

For those still looking at this thread be advised that JDBC.jl has been recently updated and, as far as I know, is the simplest solution for working with a wide variety of databases in Julia at the moment. Of course for Postgres you should still use LibPQ. (I had an earlier post mentioning that I updated SQLAlchemy.jl, but that’s much worse than JDBC, don’t use that.)

28

I used to use the JDBC style to connect to a Postgresql server in R. The issue is that I need to change the file path of database driver when I run my code on another machine.