# Accessing Postgresql via Julia
**URL:** https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031
**Category:** General Usage
**Tags:** question
**Created:** [November 12, 2017, 8:05pm UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031 "2017-11-12T20:05:27Z")
**Posts on this page:** 18
**Page:** 1
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 12, 2017, 8:05pm UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/1 "2017-11-12T20:05:27Z")
I am currently using R to access a remote Postgresql database, and my code looks like those as below:
```julia
library(RPostgres)
wrds <- dbConnect(Postgres(),
host='wrds-pgdata.wharton.upenn.edu',
port=9737,
user='username',
password='password',
sslmode='require',
dbname='wrds')
```
I would like to know how to do it in Julia.
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [November 13, 2017, 6:32am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/2 "2017-11-13T06:32:26Z")
Have a look at this thread where people helped me a lot:
[https://discourse.julialang.org/t/lack-of-stable-postgresql-communication-a-major-showstopper/](https://discourse.julialang.org/t/lack-of-stable-postgresql-communication-a-major-showstopper/)
---
### Author:  [@quinnj](https://discourse.julialang.org/u/quinnj)
#### Post date: [November 13, 2017, 4:19pm UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/3 "2017-11-13T16:19:12Z")
ODBC.jl provides access to any database that supports an ODBC driver; Postgres has [excellent support](https://www.postgresql.org/ftp/odbc/versions/) for ODBC. For windows, I’d downloads the `msi` folder version for an easy installer, for other unix platforms, just grab the tarball from the `src` directory. You can setup an ODBC dsn file like the one used for [testing](https://github.com/JuliaDB/ODBC.jl/blob/master/test/pgtest.odbc.ini), which can be registered w/ your system ODBC via the command `odbcinst -i -s -h -f ./test/pgtest.odbc.ini`.
As noted elsewhere, it takes a bit more setup, but once you’ve been thru it once or twice, I find it much easier to setup any other database.
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [November 13, 2017, 4:29pm UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/4 "2017-11-13T16:29:23Z")
By the way, on ODBC master on 0.6.1 I’m getting
```julia
ERROR: LoadError: LoadError: LoadError: UndefVarError: @isdefined not defined
Stacktrace:
[1] include_from_node1(::String) at ./loading.jl:576
[2] include(::String) at ./sysimg.jl:14
[3] include_from_node1(::String) at ./loading.jl:576
[4] include(::String) at ./sysimg.jl:14
[5] include_from_node1(::String) at ./loading.jl:576
[6] eval(::Module, ::Any) at ./boot.jl:235
[7] _require(::Symbol) at ./loading.jl:490
[8] require(::Symbol) at ./loading.jl:405
```
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [February 7, 2018, 2:14am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/5 "2018-02-07T02:14:19Z")
Just want to see if there is any update on this
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 29, 2019, 9:27am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/6 "2019-11-29T09:27:22Z")
```julia
using LibPQ: Connection
conn = Connection("""host = wrds-pgdata.wharton.upenn.edu port = port
user='username' password='password'
sslmode = 'require' dbname = wrds
""")
```
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 30, 2019, 5:43am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/7 "2019-11-30T05:43:57Z")
when I tried to install LibPQ on Julia 1.2, I got the error message:
> ERROR: Pkg.TOML.ParserError(275, 278, “redefinition of table `3.0`”)
>
> …and 1 more exception(s).
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 30, 2019, 5:56am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/8 "2019-11-30T05:56:29Z")
There seem to be an issue with your active environment `Project.toml`. Use a new environment for that project or alternatively, clean up that `Project.toml` to figure out the bas entries.
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 30, 2019, 6:07am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/9 "2019-11-30T06:07:21Z")
how to clean up the Project.toml
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 30, 2019, 6:27am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/10 "2019-11-30T06:27:34Z")
You can just delete it (rename it to `_Project.toml` if you want to keep a copy) and create a new one by
```julia
using Pkg
Pkg.activate("path/to/prj/dir")
Pkg.add("MyFirstDep")
```
That should do it.
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 30, 2019, 6:54am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/11 "2019-11-30T06:54:33Z")
Thank you. I got another problem
> res = execute(wrds, “select column\_name
> from information\_schema.columns
> where table\_schema=‘crsp’
> and table\_name=‘msf’
> order by column\_name”)
>
> crsp\_msf\_var = DataFrame(columntable(res))
i need to specify the number of rows to be downloaded. sometimes the data could be too large, and i just need to see a part of it. for example, something like this:
> crsp\_msf\_var = DataFrame(columntable(res, nrow = 10))
would be great
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 30, 2019, 7:24am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/12 "2019-11-30T07:24:11Z")
You can do so directly in SQL by adding
```julia
res = execute(wrds, “select column_name
from information_schema.columns
where table_schema=‘crsp’
and table_name=‘msf’
order by column_name
limit 10”)
```
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 30, 2019, 7:31am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/13 "2019-11-30T07:31:00Z")
thank you so much! this may be unrelated to this post, just want to know if DataFrame is optimized for good performance or if there is any other fast data structure for tabular data in Julia.
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 30, 2019, 7:40am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/14 "2019-11-30T07:40:02Z")
DataFrames.jl is one of the oldest packages in the data ecosystem and is currently the flagship when it comes to in-memory tabular data. It is quite efficient and has tons of features. However, for efficiency the optimal course would be to run operations in the performant database when possible (e.g., a relational database such as Postgres with relevant indices). For example, run any select/filter/joins/sort operations in the database when possible and work with the results with DataFrames. Same advice applies to most tabular ecosystem no matter how efficient those are (e.g., same applies to R’s data.table).
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 30, 2019, 7:57am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/15 "2019-11-30T07:57:59Z")
Is there a a package in julia that is similar to data.base?
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 30, 2019, 9:37am UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/16 "2019-11-30T09:37:11Z")
> [@Yifan\_Liu](#):
>
> data.base
What do you mean by that?
---
### Author:  [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)
#### Post date: [November 30, 2019, 1:55pm UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/17 "2019-11-30T13:55:08Z")
I meam data.table, sorry sbout the typo
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [November 30, 2019, 8:26pm UTC](https://discourse.julialang.org/t/accessing-postgresql-via-julia/7031/18 "2019-11-30T20:26:44Z")
In general DataFrames.jl offers efficient in-place operations à la `data.table`. As for indices, IndexTables.jl does a bit of that.