[DE Zoomcap] Troubleshooting pgcli issues

pgcli_issue.md

-- Read about DataTalks.Club Data Engineering Zoomcamp --

How to fix errors appearing after running pgcli

First week of the data engineering Zoomcamp by DataTalks.Club was a gentle introduction to writing and executing SQL queries against the PostgreSQL database.

Intro

Although pgcli is not 100% necessary for the Week 1 completion, but it makes writing SQL queries more pleasant, so I decided to try reproducing errors that MAY appear while installing pgcli and / or using it.

I've set-up my env using WSL2 (Ubuntu-20.04) in Windows 10 (21H1), Python 3.9.10.

Dependency conflicts

Check your pgcli version (pip list). If it's below 3.0 (should be 3.3.1 at the time of writing this) then:

• you most probably had the Error: pg_config executable not found issue while installing pgcli, thus psycopg2 was not installed (couldn't build from source)
• but you should notice psycopg2-binary package installed, as well as some very very old version of prompt-toolkit (~1.0), which conflicts with jupyter requirements btw.

This combination of package versions should still work but you might notice frequent errors after running the pgcli, like:

psycopg2.errors.UndefinedColumn: column def.adsrc does not exist
LINE 7:                         def.adsrc as default

or after typing this one it will bleed as well:

decamp@localhost:ny_taxi> \d yellow_taxi_data
column c.relhasoids does not exist
LINE 2: ...                 c.relhasrules, c.relhastriggers, c.relhasoi...
                                                             ^

Solution

Fortunately there is a one-line solution which can be found on pgcli's github but one need to scroll a little bit ;)

$ sudo apt-get install python-dev libpq-dev libevent-dev

Then you need to update your Python environment:

(decamp) nervuzz@DELL:~/venvs$ pip install --upgrade pgcli
[...]
Successfully installed pendulum-2.1.2 pgcli-3.3.1 pgspecial-1.13.0 prompt-toolkit-3.0.24 psycopg2-2.9.3 pytzdata-2020.1 sqlparse-0.4.2

All packages with correct version in place. Enjoy writing SQL queries with pgcli!