How to fix "ERROR: column c.relhasoids does not exist" in Postgres?

Question

I’m trying to CREATE TABLE command in Postgresql. After creating a table, if I punch in TABLE table name, it works.

But I punch in \d table name, I keep getting an error below.

ERROR: column c.relhasoids does not exist LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...

I attempted DROP DATABASE table name recreated a database and recreated a table again several times. But it didn't work.

Any suggestions would be appreciated! Thank you.

It was solved! Thank you so much everyone! locate pg_hba.conf createdb Nao Then it worked. — Nao, Nov 05 '19 at 12:47
Simple solution that worked is here - https://stackoverflow.com/a/58462270/984471 — Manohar Reddy Poreddy, Nov 21 '19 at 14:53
What do you mean by " if I punch in TABLE table name, it works. " — Adelin, Jan 07 '20 at 08:35

richyen · Answer 1 · 2019-10-19T16:31:53.423

I am able to reproduce your error if I am using Postgres v.12 and an older client (v.11 or earlier):

[root@def /]# psql -h 172.17.0.3
psql (11.5, server 12.0)
WARNING: psql major version 11, server major version 12.
         Some psql features might not work.
Type "help" for help.

postgres=# create table mytable (id int, name text);
CREATE TABLE
postgres=# table mytable;
 id | name 
----+------
(0 rows)

postgres=# \d mytable;
ERROR:  column c.relhasoids does not exist
LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...
                                                             ^
postgres=#

This is because in v. 12, table OIDs are no longer treated as special columns, and hence the relhasoids column is no longer necessary. Please make sure you're using a v. 12 psql binary so you don't encounter this error.

You may not necessarily be using psql, so the more general answer here is to make sure you’re using a compatible client.

Thank you so much! I am using v11.5... I'll try to uninstall and reinstall postgress again. — Nao, Oct 24 '19 at 22:56
This answer led me to the culprit in my case: I have multiple PgAdmin installations, and Windows search was only giving me an old one to use. Therefore I was using an old PgAdmin with a Postgres v12. — funforums, Mar 09 '20 at 10:42

score 49 · Answer 2 · answered Nov 07 '19 at 11:52

49

For anyone running Postgres as a Docker container:

Instead of running psql from the host, run it from inside the container e.g.

docker exec -it postgres_container_name psql your_connection_string

The Postgres image always ships with the corresponding—and thus always updated—version of psql so you don't have to worry about having the correct version installed on the host machine.

answered Nov 07 '19 at 11:52

joakim

3,533
2
23
28

That really misses the point. You need to be able to access a container independently. – Jon M Dec 20 '19 at 10:23
I’m not sure what “accessing a container independently” means in this context or why the command in my reply is somehow not allowing that? – joakim Dec 20 '19 at 13:04
Because having to directly access the container may not always be possible. The problem here seems to require a separate postgres service. Downgrading the psql client, or upgrading the server is the solution; not simply bypassing the problem and running it together. That is not always possible due to other architectural constraints. – Jon M Dec 21 '19 at 14:24
3

Of course; I did not suggest using Docker *as a solution*. That’s why I led with “For anyone running Postgres as a Docker container”. – joakim Dec 21 '19 at 16:45
Can you please post an example of "your_connection_string"? – Olle Härstedt Feb 12 '20 at 16:45
1

Hey Olle. That would be whatever options you would pass to psql. For example, today I had to run the above with “... psql -U testuser” to authenticate as a specific user – joakim Feb 12 '20 at 22:00

score 31 · Answer 3 · answered Nov 20 '20 at 16:25

If you're using DataGrip, there's an easy fix:

Try using "Introspect using JDBC metadata". This fixed it for me when (I think) I had a version mismatch between postgresql server and DataGrip client.

Under your connection settings -> Options tab -> check Introspect using JDBC metadata

According to https://www.jetbrains.com/help/datagrip/data-sources-and-drivers-dialog.html#optionsTab :

Switch to the JDBC-based introspector.

To retrieve information about database objects (DB metadata), DataGrip uses the following introspectors:

A native introspector (might be unavailable for certain DBMS). The native introspector uses DBMS-specific tables and views as a source of metadata. It can retrieve DBMS-specific details and produce a more precise picture of database objects.

A JDBC-based introspector (available for all the DBMS). The JDBC-based introspector uses the metadata provided by the JDBC driver. It can retrieve only standard information about database objects and their properties.

Consider using the JDBC-based intorspector when the native introspector fails or is not available.

The native introspector can fail, when your database server version is older than the minimum version supported by DataGrip.

You can try to switch to the JDBC-based introspector to fix problems with retrieving the database structure information from your database. For example, when the schemas that exist in your database or database objects below the schema level are not shown in the Database tool window.

Works for me, when I checked the Introspect using JDBC metadata! — Mohammed Réda OUASSINI, Mar 04 '21 at 16:19
The "Introspect using JDBC metadata" hack is working, but you loose some aspects of the architecture, e.g. you see materialized views as regular tables... perhaps other too, but I haven't tried more for now... — Adam G., May 07 '21 at 21:09

score 16 · Answer 4 · answered Jul 16 '20 at 16:08

The issue is the client (psql) is a different version from the postgres server. I have seen this issue with psql version 11 talking to postgres version 12. To solve this issue upgrade the psql version to 12.

If you are running a docker postgres, you can exec into the container then use the psql client installed there.

# get the container id with this
docker ps
# Then exec into the container, please note the host will now be 120.0.0.1
docker exec -it c12e8c6b8eb5 /bin/bash

score 9 · Answer 5 · answered Jul 30 '21 at 11:51

I had this issue because my psql was 9.2 and the server version was 12.7. So ... clearly the psql client needs to be updated. But how?

Before you go downloading/installing anything though you may already have the right version. In my case I did.

I executed which psql which showed my version was coming from /usr/bin/psql. I then checked /usr/pgsql-12/bin and found there was a psql in there. So all I needed to do was ensure psql was picked up from there. There are a number of places that could be controlling this; in my case I just added this line to my .pgsql_profile (in the postgres user's home directory):

export PATH="/usr/pgsql-12/bin:$PATH"

Logging out and back in as postgres and executing which psql showed the change had been successful:

 which psql
/usr/pgsql-12/bin/psql

score 4 · Answer 6 · answered Oct 25 '20 at 10:28

This answer is specific to pgcli

If you are using pgcli you may be encountering this issue. It's solved by updating the python package pgspecial.

If you installed pgcli using pip, you can simply do, depending on your python version:

pip install -U pgspecial

or

pip3 install -U pgspecial

If you are using Ubuntu and intalled pgcli using apt, you can either switch it to pip with:

sudo apt remove --purge pgcli
pip3 install pgcli

or update the distribution package python-pgspecial or python3-pgspecial from the Ubuntu packages web site. In that case you may need to update its dependencies as well.

score 3 · Answer 7 · answered Oct 19 '19 at 09:32

I had this issue today, was unable to continue work due to this, strangely the application code is working fine.

Later, found this issue is only occurring if I use OmniDb client I use to connect to DB.

I have switched client to default pgAdmin 4 that comes with postgres installation & issue is not occurring anymore pgAdmin 4. Link: https://www.pgadmin.org/download/pgadmin-4-windows/

Its possible that OmniDb client might be older, but no time to troubleshoot it, using pgAdmin 4 for now.

Hope that helps.

For everyone looking at this answer: Since version 3.0.3 Postgres 12 support was added. — RCRalph, Mar 03 '21 at 20:25

score 3 · Answer 8 · answered Oct 02 '20 at 17:10

3

Just update DataGrip solved this issue, Datagrip updated to version DataGrip 2019.3.3, Build #DB-193.6494.42, built on February 12, 2020, Now working :)

Just for DataGrip users!

answered Oct 02 '20 at 17:10

DariusV

2,645
16
21

Thanks bro. Updated datagrip from 2017 and problem solved! – Gökçer Gökdal Oct 19 '20 at 14:15

score 2 · Answer 9 · answered Oct 20 '19 at 00:23

2

I had the same issue today too. In my case, the problem was solved when I deleted the version 12 and installed the version 11. Seems that v12 has some features that must be create along the others columns.

answered Oct 20 '19 at 00:23

Danilo Silva

41
3

1

You were probably using `psql` from your Postgres 11 installation to connect to Postgres 12 - you should have used `psql` for Postgres 12 – Nov 07 '19 at 12:10

score 0 · Answer 10 · answered Oct 23 '19 at 20:46

0

I had the same problem. But I found the solution by downloading the latest build on 14/10/2019 Follow the link: https://postbird.paxa.kuber.host/2019_10_14.06_42-master-7a9e949

I hope it helps

answered Oct 23 '19 at 20:46

Bruno Machado

29
2

score 0 · Answer 11 · answered Jun 20 '20 at 06:15

To fix this, edit Postgres.php file and comment the lines from hasObjectID function as shown below.

function hasObjectID($table) {
    $c_schema = $this->_schema;
    $this->clean($c_schema);
    $this->clean($table);

    /*
    $sql = "SELECT relhasoids FROM pg_catalog.pg_class WHERE relname='{$table}'
        AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname='{$c_schema}')";

    $rs = $this->selectSet($sql);
    if ($rs->recordCount() != 1) return null;
    else {
        $rs->fields['relhasoids'] = $this->phpBool($rs->fields['relhasoids']);
        return $rs->fields['relhasoids'];
    }
    */
}

score 0 · Answer 12 · answered Sep 13 '20 at 17:19

0

I had the same issue when using PgAdmin to query the database.

Once I installed the newest version of PgAdmin the error disappeared!

answered Sep 13 '20 at 17:19

sanwall

63
5

score 0 · Answer 13 · answered Dec 16 '21 at 18:43

You might also try restarting pgadmin.

After upgrading from postgres96 to postgres12 I had the same issue. My pgadmin was running psql v12.0 so that wasn't the issue. I restarted pgadmin for a separate issue and the relhasoids issue went away.

If anyone could explain to me why this worked that would be appreciated.

score -3 · Answer 14 · answered Jul 16 '20 at 04:01

-3

Just use version 11.

how to install version 11 https://websiteforstudents.com/how-to-install-postgresql-11-on-ubuntu-16-04-18-04-servers/

answered Jul 16 '20 at 04:01

Oscar

47
1
7

score -3 · Answer 15 · edited Nov 25 '20 at 07:30

-3

I also got same issue with my postgresql tables. I have fixed this issue by below query.

ALTER Table MyDataBase.table_name add column column_name data_type default 0 not null;
commit;

edited Nov 25 '20 at 07:30

Suraj Rao

29,388
11
94
103

answered Nov 25 '20 at 07:20

Naresh Ayanavilli

1
1

How to fix "ERROR: column c.relhasoids does not exist" in Postgres?

15 Answers15

Linked