What does regclass signify in Postgresql

Question

I have the following line in a CREATE TABLE statement:

field1_id bigint DEFAULT nextval('table1_field1_id_seq'::regclass) NOT NULL,

What does regclass signify in the above? Is it an absolute requirement to add ::regclass?

N.B: I had seen the Postgresql documentation link which tells about regclass, but couldn't understand it.

Answer 1

No, you do not need the cast to regclass when calling a function like nextval that accepts a regclass parameter, as there is an implict cast from text to regclass. In some other contexts an explicit cast to regclass may be required.

Explanation:

::regclass is a cast, like ::integer.

regclass is a "magic" data type; it's actually an alias for oid, or "object identifier". See Object identifier types in the documentation. Casting to regclass is a shortcut way of saying "this the name of a relation, please convert it to the oid of that relation". Casts to regclass are aware of the search_path, unlike querying pg_class for a relation's oid directly, so casting to regclass isn't exactly equivalent to subquerying pg_class.

Tables are relations. So are sequences, and views. So you can get the oid of a view or sequence by casting to regclass too.

There are implicit casts defined for text to regclass, so if you omit the explicit cast and you're calling a function that accepts regclass the cast is done automatically. So you do not need it in, for example, nextval calls.

There are other places where you may. For example you can't compare text directly with oid; so you can do this:

regress=> select * from pg_class where oid = 'table1'::regclass;

but not this:

regress=> select * from pg_class where oid = 'table1';
ERROR:  invalid input syntax for type oid: "table1"
LINE 1: select * from pg_class where oid = 'table1';

---

Just for fun I tried to write a query that performed the equivalent operation of casting to regclass. Don't use it, it's mostly for fun, and as an attempt to demo what's actually happening. Unless you're really interested in how Pg's guts work you can stop reading here.

As I understand it, 'sequence_name'::regclass::oid is roughly equivalent to the following query:

WITH sp(sp_ord, sp_schema) AS (
  SELECT 
    generate_series(1, array_length(current_schemas('t'),1)),
    unnest(current_schemas('t'))
)
SELECT c.oid
FROM pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
INNER JOIN sp ON (n.nspname = sp.sp_schema)
WHERE c.relname = 'sequence_name'
ORDER BY sp.sp_ord
LIMIT 1;

except that it's a lot shorter and a lot faster. See System information functions for the definition of current_schemas(...), etc.

In other words:

• Get a ab array listing all schemas we have access to and pair each entry up with an ordinal number for its position in the array
• Search pg_class for relations with matching names and associate each with its namespace (schema)
• Sort the list of remaining relations by the order in which their schemas appeared in search_path
• and pick the first match