Tuesday, October 12, 2021

In PostgreSQL, what does "regclass" mean?

"regclass" is an alias for an Object Identifier, which are used internally by PostgreSQL as primary keys for several data dictionary tables.

It can be used as a translator when querying the dictionary tables in postgres.

For example, the following query produces an error, since the predicate expects an integer ("oid"):
select relname,relowner,reltuples,relkind  from pg_class where oid='myuser.mytable_1';
ERROR:  invalid input syntax for type oid: "myuser.mytable_1"
LINE 1: ...lowner,reltuples,relkind  from pg_class where oid='myuser.mytabl...

However, if you add "::regclass", the alias for the object is used instead of the literal string:
 select relname,relowner,reltuples,relkind  from pg_class where oid='myuser.mytable_1'::regclass;
  relname  | relowner | reltuples | relkind
-----------+----------+-----------+---------
 mytable_1 |    69094 |         0 | p

Note that there are several types of OIDs. For example, for roles (users), you should use "regrole" instead of "regclass":
select relname,relowner,reltuples,relkind  from pg_class where relowner='myuser'::regrole;
        relname        | relowner | reltuples | relkind
-----------------------+----------+-----------+---------
 unique_idx_2          |    69094 |         0 | I
 pg_toast_191274       |    69094 |         0 | t
 mytable_1_id_seq      |    69094 |         1 | S
 mytable_1_pkey        |    69094 |         0 | I
 unique_idx_1          |    69094 |         0 | i
 mytable_2             |    69094 |         0 | p
 mytable_3             |    69094 |         0 | r
 

If you use them together, translate to different types of OID. In the below case, it's really not necessary, but illustrates the point nevertheless:
 select relname,relowner,reltuples,relkind  from pg_class where oid='myuser.mytable_1'::regclass and relowner='myuser'::regrole;
More information about OIDs
More information about pg_class

No comments:

Post a Comment