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 create the column. This is to prevent run of the script after the first from erroring out.

Answer 1

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.

The catalog tables are in the postgres database, so make sure to connect to that, rather than your application database.

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

Answer 2

SELECT oid FROM pg_class WHERE relname = 'tbl_name' AND relkind = 'r';

Answer 3

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.

Answer 4

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)