postgresql - 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

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)