What does regclass mean in Postgresql
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 mean 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.
Solution 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