Donnerstag, 11. Dezember 2008

How to get a table's OID

I often get questions from customers trying to work with OIDs of relations they need to add somewhere (three times just this week). For example, someone wants to add a special autovacuum configuration for a specific table. He then needs to find out the OID of the relation. People often tend to use the following query then:


SELECT oid FROM pg_class WHERE relname = 'foo' AND relkind = 'r';
oid
-------
16385
(1 row)


This returns the current OID of the table and its relation name. The OID can then be used for everything you need it to. However, there's an much easier way to get that information: just use a cast of the relation name to regclass:


SELECT 'foo'::regclass::oid;
oid
-------
16385
(1 row)


Or, to get back to the pg_autovacuum example above:


INSERT INTO pg_autovacuum
VALUES('foo'::regclass, 't', -1, 0.05, -1, -1, -1, -1, -1, -1);


If you are going to assign regclass to an OID type, you don't need the extra cast to OID, since there's an existing implicit cast to OID. There are other reg* casts available as well, for examaple:


SELECT 'now'::regproc::oid;
oid
------
1299
(1 row)

SELECT 'int4'::regtype::oid;
oid
-----
23

1 Kommentar:

Matt hat gesagt…

Thank you for this post. It has helped me figure out this OID thing. Glad it is in English too :)