Determining the OID of a table in Postgres 9.1?

Question

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.

Answer 1

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:

• How to check if a table exists in a given schema

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) ; 

Answer 2

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