Полезная информация

next up previous contents index
Next: Object Id Limitations Up: Unique Row Identifiers Previous: Unique Row Identifiers

Object Id's (OID'S)

Every row in the POSTGRESQL database is assigned a unique number called an object id or OID. When the software is first initialized, with initdb9.1, a counter is set to approximately 17,100. All rows inserted after that are assigned ever-increasing object id's. Databases can be created and destroyed, but the counter continues to increase. Because the counter is shared by all databases, assigned object id's is quite unique. There is no other row in any other table or in any other database with the same object id.9.2

You may not know that you have already seen object id's in this book. If you look back at figure [*], you will see the line INSERT 18720 1. The INSERT is the command that was executed, the 18720 is the object id of the row that was inserted, and the 1 is the number of rows inserted. A similar line appears after every INSERT statement.

You can even try the small experiment shown in figure [*].

  
Figure: OID test
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...-{}-+-{}-{}-{}-{}-
\par ~18697~\vert~~~7
\par (1~row)\end{list}\par
\end{figure}

Notice the OID displayed from the INSERT and the OID returned by the SELECT are the same. The SELECT has accessed the usually invisible OID column. Though there is no OID column mentioned in the CREATE statement, every POSTGRESQL table has an invisible column called OID. The column only appears if you specifically access it.9.3 The common SELECT *... does not display the oid column, though SELECT OID, *... will display it.

Object id's can be used as key values. Since every row has a unique object id, there is no need to create a separate column to hold it. Just use the object id anytime you need to reference a row. From the example in the previous chapter, there would be no need to create the column customer.customer_id because there is already a column customer.oid. That could used as the unique id for every customer. The column salesorder.customer_id would hold the OID of the customer who made the order.

The column salesorder.customer_id was originally defined as type INTEGER. If you are going to be placing OID's in that column, you should define the column to be of type OID. A column of type oid is similar to an INTEGER column. Defining it as OID makes it clear the column holds OID values and not ordinary integers. Also, you could rename salesorder.customer_id to salesorder.customer_oid because the column refers to an OID. Figure [*] shows a new version of the salesorder table using each row's OID as a join key.

  
Figure: Columns with OID's
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\_oid~~~~~~~OID,~~-{}-~joins~to~part.oid
\par\ldots{}\end{list}\par
\end{figure}

A column of type OID is not automatically assigned any special value from the database. Only a column named OID is specially assigned during an INSERT. The order_id column in the example could be eliminated. The salesorder.oid column could represent the unique order number.


next up previous contents index
Next: Object Id Limitations Up: Unique Row Identifiers Previous: Unique Row Identifiers
Bruce Momjian
1999-11-21

Дешевые проститутки москвы выезд.