How to get the OID of a Postgres table?

by

To get a table OID, cast to the object identifier type regclass (while connected to the same DB!). This finds the first table (or view etc.) with the given (unqualified) name along the search_path or raises an exception if not found:

SELECT 'mytbl'::regclass::oid;

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

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

Since Postgres 9.4 we can also use to_regclass(), which returns null instead of raising an exception if the table is not found:

SELECT to_regclass('myschema.mytbl');

See:

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

Then query the catalog table pg_attribute for the existence of a given column:

SELECT EXISTS (
   SELECT 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
   );
;

This way you get:

  • an error if no table (or view etc.) of that name exists
  • false if the column does not exist
  • true if the column exists (incl. system columns)

Leave a Comment