45

Does anyone know how to find the OID of a table in Postgres 9.1?

I am writing an update script that needs to test for the existence of a column in a table before it tries to add the column. This is to prevent errors when running the script repeatedly.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Tony Vitabile
  • 8,298
  • 15
  • 67
  • 123

4 Answers4

74

To get a table OID, cast to the object identifier type regclass (while connected to the same DB):

SELECT 'mytbl'::regclass::oid;

This finds the first table (or view, etc.) with the given name along the search_path or raises an exception if not found.

Schema-qualify the table name to remove the dependency on the search path:

SELECT 'myschema.mytbl'::regclass::oid;

In Postgres 9.4 or later you can also use to_regclass('myschema.mytbl'), which doesn't raise an exception if the table is not found:

Then you only need to query the catalog table pg_attribute for the existence of the column:

SELECT TRUE AS col_exists
FROM   pg_attribute 
WHERE  attrelid = 'myschema.mytbl'::regclass
AND    attname  = 'mycol'
AND    NOT attisdropped  -- no dropped (dead) columns
-- AND    attnum > 0     -- no system columns (you may or may not want this)
;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Just want to point out how this answer from 2012 is bang on. Just, you know, you made my life easier, today, @erin-brandstetter! Ya' did well, and much thanks. – Sam Hughes Mar 17 '22 at 18:15
  • @Sam: Good to hear! I did keep it up to date (like just now again). `to_regclass()` didn't even exist, yet, in 2012. – Erwin Brandstetter Mar 17 '22 at 22:58
42

The postgres catalog table pg_class is what you should look at. There should be one row per table, with the table name in the column relname, and the oid in the hidden column oid.

You may also be interested in the pg_attribute catalog table, which includes one row per table column.

See: http://www.postgresql.org/docs/current/static/catalog-pg-class.html and http://www.postgresql.org/docs/current/static/catalog-pg-attribute.html

phemmer
  • 6,882
  • 3
  • 33
  • 31
jmelesky
  • 689
  • 6
  • 9
  • 2
    I had seen the docs on the `pg_class` and `pg_attribute` tables, but I didn't know that there was a hidden column named `oid` in the `pg_class` table. I couldn't figure out where the oid was from the docs. Thanks! – Tony Vitabile Jun 08 '12 at 19:09
  • 9
    This is not quite accurate. Every database has a schema named `pg_catalog`, which contains catalog tables specific to the database. – Jakob Egger May 22 '13 at 14:16
  • 1
    I second @TonyVitabile. It was the hidden column `oid` that I was looking for. – Eliyahu Skoczylas May 22 '17 at 19:54
  • Removed incorrect information regarding the database to connect to. Just noting since @JakobEgger's comment will now seem out of place. – phemmer Feb 22 '21 at 05:47
7
SELECT oid FROM pg_class WHERE relname = 'tbl_name' AND relkind = 'r';
user3132194
  • 2,381
  • 23
  • 17
  • @eirikir Postgres stores information about every table created by user as well as about system tables in the system catalog (actually table) pg_class, so you can get oid using this query. It is funny, but information about pg_class is also stored in pg_class: postgres=# SELECT oid, relname FROM pg_class WHERE relname = 'pg_class'; oid | relname ------+---------- 1259 | pg_class (1 row) – ololobus Dec 06 '18 at 09:47
  • @ololobus No, I believe the comment by eirikir was not a request for further info but a criticism to the author of this Answer. The creators of Stack Overflow intended for this site to be more than a code snippet library. So, Answers are expected to have some explanation along with any example code. – Basil Bourque Aug 03 '19 at 04:51
2

Just to complete the possibilities I'd like to add that there exists a syntax for dropping columns in order to no error out:

ALTER TABLE mytbl DROP COLUMN IF EXISTS mycol

See http://www.postgresql.org/docs/9.0/static/sql-altertable.html

Then you can safely add your column.

Stefan
  • 1,036
  • 1
  • 10
  • 14