17

I am using pgAdmin-4 and created a database with a single table, but select returns an error message: 'table oid'

I'm using a normal select query.

SELECT * FROM escola

This happens with PostgreSQL 11.4.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Maykon Morais
  • 171
  • 1
  • 6
  • 1
    What was the command you typed and what are the details of the error message? Akso what is the version number of Postgres? – Amit Jul 26 '19 at 20:24
  • Hello, im using a normal select ( select * from escola) and im using PostgreSQL 11.4 – Maykon Morais Jul 26 '19 at 20:27
  • This is not an issue with pgAdmin4 but with the Debian/Ubuntu packaging, Debian/Ubuntu repo has older version of psycopg2 library. – Murtuza Z Jul 30 '19 at 09:38

2 Answers2

21

The problem is due to python3-psycopg2. The latest pgadmin4 version requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt installed v2.7. So you need to update it with pip :

sudo pip3 install -U psycopg2

Then update the pgadmin4 config to add the local python path:

nano ~/.config/pgadmin/pgadmin4.conf 
# or with a right click on the system tray icon
# add /usr/local/lib/python3.6/dist-packages to the PythonPath.

Same thing with python3.7 (just change the lib path)

Hope this helps.

vidlb
  • 361
  • 1
  • 5
  • 11
    This answer gave me a very good direction. Installing or updating `psycopg2` didn't work for me since it required `pg_config` which I couldn't find. Instead I did: `pip3 install psycopg2-binary` which succeeded without asking any config. Then, I modified proposed above `pgadmin4.conf` file by setting `PythonPath` in the following way: `PythonPath="/home/rustam/.local/lib/python3.6;/home/rustam/.local/lib/python3.6/site-packages"` . After that my `SELECT` queries stopped returning `table_oid` and I can see results fine. – Rustam Aliyev Jul 30 '19 at 10:26
  • Thank you very much, the instructions worked. I only had to install setuptools "sudo pip3 install -U setuptools" before installing psycopg2. – grand Aug 01 '19 at 16:12
  • 1
    @Rustam Aliyev In our case, when setting the PythonPath the devider had to be a `:` instead of `;` `PythonPath="/home/rustam/.local/lib/python3.6:/home/rustam/.local/lib/python3.6/site-packages"` – Maarkoize Aug 07 '19 at 06:47
  • I want to add something for this. if `sudo pip3 install -U psycopg2` didn't work install pip3 by `sudo apt install python-pip` and then run `sudo pip3 install -U psycopg2` again. running `pip3 install -U psycopg2` to me didn't solve the problem, I have to install pip3 in sudo and run `sudo pip3 install -U psycopg2` and then don't forget to update the pgadmin4 conf – bxorcloud Aug 07 '19 at 17:25
  • Followed Rustam's approach, but used `PythonPath=/usr/local/lib/python3.6/dist-packages` in the pgadmin4.conf, and restarted the pgadmin server process. – MikeMurko Aug 08 '19 at 14:31
  • I can't find pgadmin4.conf. My initial version of postgresql was 9.4. Indeed, psql -V returns version 9.4.23. However, the command pg_config --version returns 11.4. I have the same issue as the question and I don't know what to do... – Digao Aug 13 '19 at 14:48
  • ok, after suffering with this issue and trying to fix python libs, I just installed DBeaver and everything looks good now ! – Digao Aug 13 '19 at 16:02
-3

after I updated my pgAdmin 4 today I'm getting the same message 'table_oid' from select queries I execute in pgAdmin 4.

The odd thing is when I execute the view data command from GUI it works perfectly.

When I execute a select search from query tool I got the 'table_oid' message, as same as Maykon Morais.

I tried to add a comment on Maykon Morais' question (telling I got the same problem) but i don't have enough reputation yet :-\

The table_oid message appear, in my case, when running a simple select from the query tool window of pgadmin4.

Postgres version: psql (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.04+1)

pgAdmin4 version: Versión 4.11 Derechos de Autor Copyright (C) 2013 - 2019, The pgAdmin Development Team Versión de Python 3.6.8 (default, Jan 14 2019, 11:02:34) [GCC 8.0.1 20180414 (experimental) [trunk revision 259383]] Versión de Flask 0.12.2 Modo de Aplicación Escritorio Usuario Actual pgadmin4@pgadmin.org

Trying to give a properly answer

Well the workaround that I see (and I'm using myself right now) meanwhile this situation is fixed, securely soon, is to execute select queries via console:

  1. login as postgres user: sudo su postgres
  2. psql -h dbhostname -U dbusername -d dbname
  3. SELECT * FROM dbtable;

=)

Dovahkiin
  • 48
  • 1
  • 6