Why am I getting 'Error type "jsonb" does not exist' in psycopg2?

Question

When I run the following:

import psycopg2
#...
cur.execute("DROP TABLE IF EXISTS Song CASCADE") 
cur.execute("CREATE TABLE Song "
    "(songId INT PRIMARY KEY, "
    "data jsonb, "
    "lastUpdate TIMESTAMP)")
#...

I get

    Error type "jsonb" does not exist`

psycopg2 is installed on virtualenv, and pip show psycopg2 gives Version: 2.6.1

PostgreSQL version is 9.4.4. I can use psql and manually create a table with jsonb type column. What is causing the error when I run it through python2 and psycopg2?

I am not sure if this matters, but I just updated from PostgreSQL 9.3 to 9.4. I tried re-installing psycopg2 with pip but it did not help. I have tried restarting PostgreSQL server a couple times.

Edit:
When I SELECT Version() through psycopg2, it shows returns PostgreSQL version 9.3. How can I have psycopg2 connect to the Postgres 9.4 server?

Answer 1

The problem was that there was a config file left from Postgres version 9.3, and psycopg2 was probably reading that.

    :dpkg -l | grep postgresql-9.3  
    rc  postgresql-9.3                    9.3.9-1.pgdg14.04+1 

I purged everything related to Postgres, deleted all config files, and reinstalled only version 9.4.

After some fiddling, psycopg2 correctly found the server and was able to connect to it.

[('PostgreSQL 9.4.4 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 32-bit',)]