# PostgreSQL.jl no longer works in v0.6

**URL:** <https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581>\
**Category:** Web Stack\
**Created:** [July 1, 2017, 7:49am UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581 "2017-07-01T07:49:22Z")\
**Posts on this page:** 8\
**Page:** 2

<div class="post-metadata">

**Author:** ![johann.spies](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/johann.spies/32/8805_2.png) [@johann.spies](https://discourse.julialang.org/u/johann.spies)\
**Post date:** [October 30, 2017, 12:26pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/21 "2017-10-30T12:26:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [October 30, 2017, 2:27pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/22 "2017-10-30T14:27:57Z")

</div>

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](https://github.com/ExpandingMan/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.

---

<div class="post-metadata">

**Author:** ![Palli](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/palli/32/3380_2.png) [@Palli](https://discourse.julialang.org/u/Palli)\
**Post date:** [November 24, 2017, 11:38am UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/23 "2017-11-24T11:38:20Z")

</div>

> [@swt30](#):
>
> I see the issue but unfortunately I don’t know anything about what an OID is

[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:

> **[8.19. Object Identifier Types](https://www.postgresql.org/docs/current/datatype-oid.html)**
>
> 8.19. Object Identifier Types # Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. Type …

“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:

> **[8.19. Object Identifier Types](https://www.postgresql.org/docs/8.0/datatype-oid.html)**
>
> 8.19. Object Identifier Types # Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. Type …

---

<div class="post-metadata">

**Author:** ![Sashank786monitrahea](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sashank786monitrahea/32/3793_2.png) [@Sashank786monitrahea](https://discourse.julialang.org/u/Sashank786monitrahea)\
**Post date:** [March 23, 2018, 12:41pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/24 "2018-03-23T12:41:44Z")

</div>

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:

```julia
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]`

> **[Julia - Language - Connect to Postgres DB - select statement](http://sashankexpresstech.blogspot.com/2018/03/julia-language-connect-to-postgres-db.html)**
>
> Below is the connection script, we can use to retrieve data from a PostGres Database using Julia Language: $ julia                \_    ...

---

<div class="post-metadata">

**Author:** ![essenciary](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/essenciary/32/210469_2.png) [@essenciary](https://discourse.julialang.org/u/essenciary)\
**Post date:** [March 23, 2018, 12:52pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/25 "2018-03-23T12:52:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 23, 2018, 2:39pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/26 "2018-03-23T14:39:33Z")

</div>

I want to ask how to insert

`sslmode='require'`

to your command.

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [March 26, 2018, 1:33pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/27 "2018-03-26T13:33:39Z")

</div>

For those still looking at this thread be advised that [JDBC.jl](https://github.com/JuliaDatabases/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.)

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 26, 2018, 4:05pm UTC](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581/28 "2018-03-26T16:05:44Z")

</div>

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.

[Previous page](https://discourse.julialang.org/t/postgresql-jl-no-longer-works-in-v0-6/4581.md?page=1)