Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint

Question

In Postgresql, You can obtain information about the constraints on a table by using a query such as:

SELECT * FROM information_schema.table_constraints WHERE table_name='table_name';

When I do this, columns that have a NOT NULL constraint will show up something like this:

2200_77911989_1_not_null

The not_null part is obviously showing a NOT NULL constraint. The last number seems to be the ordinal position of the column. But the first two numbers I haven't been able to verify what they are. My guess is that they are a reference to possibly the schema and the table, but I can't find anything that tells me one way or another. The Postgres manual entry on this isn't very useful in this regard.

So what are the first two numbers then? How can I tie these numbers to pertinent information?

Answer 1

Postgres stores its information about tables, types, columns, constraints, etc. in the pg_catalog schema.

The top level is namespace. In your example above, the 2200 refers to the namespace. You can see this by evaluating: select oid,* from pg_catalog.pg_namespace where oid=2200;

The second number from your example above (77911989) refers to the oid (object id) of the table. You can see the related information with: select oid,* from pg_catalog.pg_class where oid=77911989;

The third number (1) refers to the column number within the table. You can see the relevant information by using: select * from pg_catalog.pg_attribute where attrelid=77911989 and attnum=1;

Many of the returned values (like atttypmod) are OIDs to other tables (like pg_type) in the pg_catalog schema.