Object Identification Numbers (OIDs)

 Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb , ^11.1 a counter is created and set to approximately seventeen-thousand. ^11.2 The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase. It is used by all databases, so identification numbers are always unique. No two rows in any table or in any database will ever have the same object ID. ^11.3

 You have seen object identification numbers already--they are displayed after every INSERT statement. If you look back at Figure  on page , you will see the line INSERT 19053 1. INSERT is the command that was executed, 19053 is the object identification number assigned to the inserted row, and 1 is the number of rows inserted. A similar line appears after every INSERT statement. Figure  on page  shows sequential object identification numbers assigned by consecutive INSERT statements. 

Normally, a row's object identification number is displayed only by INSERT queries. However, if the OID is specified by a non-INSERT query, it will be displayed, as shown in Figure .  

        test=> CREATE TABLE oidtest(age INTEGER);
        CREATE
        test=> INSERT INTO oidtest VALUES (7);
        INSERT 21515 1
        test=> SELECT oid, age FROM oidtest;
          oid  | age 
        -------+-----
         21515 |   7
        (1 row)  

In that example, the SELECT has accessed the normally invisible OID column. The OID displayed by the INSERT and the OID displayed by the SELECT are the same.

Even though no OID column is mentioned in CREATE TABLE statements, every POSTGRESQL table includes an invisible column called OID. This column appears only if you specifically access it. ^11.4 The query SELECT * FROM table_name does not display the OID column. However, SELECT oid, * FROM table_name will display it.

Object identification numbers can be used as primary and foreign key  values in joins. Since every row has a unique object ID, a separate column is not needed to hold the row's unique number.

For example, in Chapter  we used a column called customer.customer_id. This column held the customer number and uniquely identified each row. Alternatively, we could have used the row's object identification number as the unique number for each row, eliminating the need to create the column customer.customer_id. In that case, customer.oid would be the unique customer number.

With this change, a similar change should be made in the salesorder table. We could rename salesorder.customer_id to salesorder.customer_oid because the column now refers to an OID. The column type should be changed as well. The salesorder.customer_id was defined as type INTEGER. The new salesorder.customer_oid column would hold the OID of the customer who placed the order. For this reason, we should change the column type from INTEGER to OID. Figure  shows a new version of the salesorder table using each row's OID as a join key.   

        test=> CREATE TABLE salesorder (
        test(>             order_id      INTEGER,
        test(>             customer_oid   OID,  -- joins to customer.oid
        test(>             employee_oid   OID,  -- joins to employee.oid
        test(>             part_oid       OID,  -- joins to part.oid

 

A column of type OID is similar to an INTEGER column, but defining it as a type OID documents that the column holds OID values. Do not confuse a column of type OID with a column named OID. Every row has a column named OID, which is normally invisible. A row can have zero, one, or more user-defined columns of type OID.

A column of type OID is not automatically assigned any special value from the database. Only the column named OID is specially assigned during INSERT.

Also, the order_id column in the salesorder table could be eliminated. The salesorder.oid column would then represent the unique order number.