1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.19. Object Identifier Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="domains.html" title="8.18. Domain Types" /><link rel="next" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.19. Object Identifier Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="domains.html" title="8.18. Domain Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type">Next</a></td></tr></table><hr /></div><div class="sect1" id="DATATYPE-OID"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.19. Object Identifier Types</h2></div></div></div><a id="id-1.5.7.27.2" class="indexterm"></a><a id="id-1.5.7.27.3" class="indexterm"></a><a id="id-1.5.7.27.4" class="indexterm"></a><a id="id-1.5.7.27.5" class="indexterm"></a><a id="id-1.5.7.27.6" class="indexterm"></a><a id="id-1.5.7.27.7" class="indexterm"></a><a id="id-1.5.7.27.8" class="indexterm"></a><a id="id-1.5.7.27.9" class="indexterm"></a><a id="id-1.5.7.27.10" class="indexterm"></a><a id="id-1.5.7.27.11" class="indexterm"></a><a id="id-1.5.7.27.12" class="indexterm"></a><a id="id-1.5.7.27.13" class="indexterm"></a><a id="id-1.5.7.27.14" class="indexterm"></a><a id="id-1.5.7.27.15" class="indexterm"></a><a id="id-1.5.7.27.16" class="indexterm"></a><a id="id-1.5.7.27.17" class="indexterm"></a><a id="id-1.5.7.27.18" class="indexterm"></a><p>
Object identifiers (OIDs) are used internally by
<span class="productname">PostgreSQL</span> as primary keys for various
system tables.
Type <code class="type">oid</code> represents an object identifier. There are also
several alias types for <code class="type">oid</code>, each
named <code class="type">reg<em class="replaceable"><code>something</code></em></code>.
<a class="xref" href="datatype-oid.html#DATATYPE-OID-TABLE" title="Table 8.26. Object Identifier Types">Table 8.26</a> shows an
overview.
</p><p>
The <code class="type">oid</code> type is currently implemented as an unsigned
four-byte integer. Therefore, it is not large enough to provide
database-wide uniqueness in large databases, or even in large
individual tables.
</p><p>
The <code class="type">oid</code> type itself has few operations beyond comparison.
It can be cast to integer, however, and then manipulated using the
standard integer operators. (Beware of possible
signed-versus-unsigned confusion if you do this.)
</p><p>
The OID alias types have no operations of their own except
for specialized input and output routines. These routines are able
to accept and display symbolic names for system objects, rather than
the raw numeric value that type <code class="type">oid</code> would use. The alias
types allow simplified lookup of OID values for objects. For example,
to examine the <code class="structname">pg_attribute</code> rows related to a table
<code class="literal">mytable</code>, one could write:
</p><pre class="programlisting">
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
</pre><p>
rather than:
</p><pre class="programlisting">
SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
</pre><p>
While that doesn't look all that bad by itself, it's still oversimplified.
A far more complicated sub-select would be needed to
select the right OID if there are multiple tables named
<code class="literal">mytable</code> in different schemas.
The <code class="type">regclass</code> input converter handles the table lookup according
to the schema path setting, and so it does the <span class="quote">“<span class="quote">right thing</span>”</span>
automatically. Similarly, casting a table's OID to
<code class="type">regclass</code> is handy for symbolic display of a numeric OID.
</p><div class="table" id="DATATYPE-OID-TABLE"><p class="title"><strong>Table 8.26. Object Identifier Types</strong></p><div class="table-contents"><table class="table" summary="Object Identifier Types" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>References</th><th>Description</th><th>Value Example</th></tr></thead><tbody><tr><td><code class="type">oid</code></td><td>any</td><td>numeric object identifier</td><td><code class="literal">564182</code></td></tr><tr><td><code class="type">regclass</code></td><td><code class="structname">pg_class</code></td><td>relation name</td><td><code class="literal">pg_type</code></td></tr><tr><td><code class="type">regcollation</code></td><td><code class="structname">pg_collation</code></td><td>collation name</td><td><code class="literal">"POSIX"</code></td></tr><tr><td><code class="type">regconfig</code></td><td><code class="structname">pg_ts_config</code></td><td>text search configuration</td><td><code class="literal">english</code></td></tr><tr><td><code class="type">regdictionary</code></td><td><code class="structname">pg_ts_dict</code></td><td>text search dictionary</td><td><code class="literal">simple</code></td></tr><tr><td><code class="type">regnamespace</code></td><td><code class="structname">pg_namespace</code></td><td>namespace name</td><td><code class="literal">pg_catalog</code></td></tr><tr><td><code class="type">regoper</code></td><td><code class="structname">pg_operator</code></td><td>operator name</td><td><code class="literal">+</code></td></tr><tr><td><code class="type">regoperator</code></td><td><code class="structname">pg_operator</code></td><td>operator with argument types</td><td><code class="literal">*(integer,integer)</code>
or <code class="literal">-(NONE,integer)</code></td></tr><tr><td><code class="type">regproc</code></td><td><code class="structname">pg_proc</code></td><td>function name</td><td><code class="literal">sum</code></td></tr><tr><td><code class="type">regprocedure</code></td><td><code class="structname">pg_proc</code></td><td>function with argument types</td><td><code class="literal">sum(int4)</code></td></tr><tr><td><code class="type">regrole</code></td><td><code class="structname">pg_authid</code></td><td>role name</td><td><code class="literal">smithee</code></td></tr><tr><td><code class="type">regtype</code></td><td><code class="structname">pg_type</code></td><td>data type name</td><td><code class="literal">integer</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
All of the OID alias types for objects that are grouped by namespace
accept schema-qualified names, and will
display schema-qualified names on output if the object would not
be found in the current search path without being qualified.
For example, <code class="literal">myschema.mytable</code> is acceptable input
for <code class="type">regclass</code> (if there is such a table). That value
might be output as <code class="literal">myschema.mytable</code>, or
just <code class="literal">mytable</code>, depending on the current search path.
The <code class="type">regproc</code> and <code class="type">regoper</code> alias types will only
accept input names that are unique (not overloaded), so they are
of limited use; for most uses <code class="type">regprocedure</code> or
<code class="type">regoperator</code> are more appropriate. For <code class="type">regoperator</code>,
unary operators are identified by writing <code class="literal">NONE</code> for the unused
operand.
</p><p>
The input functions for these types allow whitespace between tokens,
and will fold upper-case letters to lower case, except within double
quotes; this is done to make the syntax rules similar to the way
object names are written in SQL. Conversely, the output functions
will use double quotes if needed to make the output be a valid SQL
identifier. For example, the OID of a function
named <code class="literal">Foo</code> (with upper case <code class="literal">F</code>)
taking two integer arguments could be entered as
<code class="literal">' "Foo" ( int, integer ) '::regprocedure</code>. The
output would look like <code class="literal">"Foo"(integer,integer)</code>.
Both the function name and the argument type names could be
schema-qualified, too.
</p><p>
Many built-in <span class="productname">PostgreSQL</span> functions accept
the OID of a table, or another kind of database object, and for
convenience are declared as taking <code class="type">regclass</code> (or the
appropriate OID alias type). This means you do not have to look up
the object's OID by hand, but can just enter its name as a string
literal. For example, the <code class="function">nextval(regclass)</code> function
takes a sequence relation's OID, so you could call it like this:
</p><pre class="programlisting">
nextval('foo') <em class="lineannotation"><span class="lineannotation">operates on sequence <code class="literal">foo</code></span></em>
nextval('FOO') <em class="lineannotation"><span class="lineannotation">same as above</span></em>
nextval('"Foo"') <em class="lineannotation"><span class="lineannotation">operates on sequence <code class="literal">Foo</code></span></em>
nextval('myschema.foo') <em class="lineannotation"><span class="lineannotation">operates on <code class="literal">myschema.foo</code></span></em>
nextval('"myschema".foo') <em class="lineannotation"><span class="lineannotation">same as above</span></em>
nextval('foo') <em class="lineannotation"><span class="lineannotation">searches search path for <code class="literal">foo</code></span></em>
</pre><p>
</p><div class="note"><h3 class="title">Note</h3><p>
When you write the argument of such a function as an unadorned
literal string, it becomes a constant of type <code class="type">regclass</code>
(or the appropriate type).
Since this is really just an OID, it will track the originally
identified object despite later renaming, schema reassignment,
etc. This <span class="quote">“<span class="quote">early binding</span>”</span> behavior is usually desirable for
object references in column defaults and views. But sometimes you might
want <span class="quote">“<span class="quote">late binding</span>”</span> where the object reference is resolved
at run time. To get late-binding behavior, force the constant to be
stored as a <code class="type">text</code> constant instead of <code class="type">regclass</code>:
</p><pre class="programlisting">
nextval('foo'::text) <em class="lineannotation"><span class="lineannotation"><code class="literal">foo</code> is looked up at runtime</span></em>
</pre><p>
The <code class="function">to_regclass()</code> function and its siblings
can also be used to perform run-time lookups. See
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE" title="Table 9.71. System Catalog Information Functions">Table 9.71</a>.
</p></div><p>
Another practical example of use of <code class="type">regclass</code>
is to look up the OID of a table listed in
the <code class="literal">information_schema</code> views, which don't supply
such OIDs directly. One might for example wish to call
the <code class="function">pg_relation_size()</code> function, which requires
the table OID. Taking the above rules into account, the correct way
to do that is
</p><pre class="programlisting">
SELECT table_schema, table_name,
pg_relation_size((quote_ident(table_schema) || '.' ||
quote_ident(table_name))::regclass)
FROM information_schema.tables
WHERE ...
</pre><p>
The <code class="function">quote_ident()</code> function will take care of
double-quoting the identifiers where needed. The seemingly easier
</p><pre class="programlisting">
SELECT pg_relation_size(table_name)
FROM information_schema.tables
WHERE ...
</pre><p>
is <span class="emphasis"><em>not recommended</em></span>, because it will fail for
tables that are outside your search path or have names that require
quoting.
</p><p>
An additional property of most of the OID alias types is the creation of
dependencies. If a
constant of one of these types appears in a stored expression
(such as a column default expression or view), it creates a dependency
on the referenced object. For example, if a column has a default
expression <code class="literal">nextval('my_seq'::regclass)</code>,
<span class="productname">PostgreSQL</span>
understands that the default expression depends on the sequence
<code class="literal">my_seq</code>, so the system will not let the sequence
be dropped without first removing the default expression. The
alternative of <code class="literal">nextval('my_seq'::text)</code> does not
create a dependency.
(<code class="type">regrole</code> is an exception to this property. Constants of this
type are not allowed in stored expressions.)
</p><p>
Another identifier type used by the system is <code class="type">xid</code>, or transaction
(abbreviated <abbr class="abbrev">xact</abbr>) identifier. This is the data type of the system columns
<code class="structfield">xmin</code> and <code class="structfield">xmax</code>. Transaction identifiers are 32-bit quantities.
In some contexts, a 64-bit variant <code class="type">xid8</code> is used. Unlike
<code class="type">xid</code> values, <code class="type">xid8</code> values increase strictly
monotonically and cannot be reused in the lifetime of a database cluster.
</p><p>
A third identifier type used by the system is <code class="type">cid</code>, or
command identifier. This is the data type of the system columns
<code class="structfield">cmin</code> and <code class="structfield">cmax</code>. Command identifiers are also 32-bit quantities.
</p><p>
A final identifier type used by the system is <code class="type">tid</code>, or tuple
identifier (row identifier). This is the data type of the system column
<code class="structfield">ctid</code>. A tuple ID is a pair
(block number, tuple index within block) that identifies the
physical location of the row within its table.
</p><p>
(The system columns are further explained in <a class="xref" href="ddl-system-columns.html" title="5.5. System Columns">Section 5.5</a>.)
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="domains.html" title="8.18. Domain Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.18. Domain Types </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.20. <code class="type">pg_lsn</code> Type</td></tr></table></div></body></html>
|
