Updated May 18, 2023
Introduction to PostgreSQL OID
PostgreSQL OID is defined as a 32-bit positive number, every row in the PostgreSQL database will contain the object identifier. By default in PostgreSQL, the OID column is hidden, we can see the row OID by specifying column name as OID in the table selection operation. OID is very useful and important in PostgreSQL to define the unique value of a row because every row contains its specific OID. We can neglect the OID from the table using the clause without OID.
Syntax
The following syntax is provided:
1. Create the Table using OID
Create table name_of_table (name_of_column1 data_type, name_of_column2 data_type, name_of_column3 data_type, …, name_of_columnN data_type) with (OIDS = TRUE);2. Create the Table without using OID
Create table name_of_table (name_of_column1 data_type, name_of_column2 data_type, name_of_column3 data_type, …, name_of_columnN data_type) with (OIDS = FALSE);3. Select Table Data using OID
Select OID, name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN from name_of_table where [condition];4. Delete Table Data using OID
Delete from name_of_table where OID = (OID_number);5. Update Table Data using OID
Update name_of_table set name_of_column = (value_of_column) where OID = (OID_number);Below is the parameter description:
- Select: The select statement retrieves the OID column from the table. We can select an OID column with another table column at one time.
- Update: The update operation is used for updating the data in a table. We can also update table data by using the OID column.
- Delete: It is used to delete the table data. We can also delete table data by using the OID column in PostgreSQL.
- Name of the table: The name of the table uniquely identifies and displays the table in PostgreSQL.
- Data type: Assigning the data type of a column at the time of table creation defines the data type. We can define any data type in the column.
- With OIDS: Using OIDS is defined as the method to create the table. If we set the OIDs value to true, PostgreSQL will generate an OID for every row.
- Without OIDS: When creating a table in PostgreSQL, specifying “Without OIDS” means that the table will be created without using OIDs (Object IDs). In PostgreSQL, when you set the OIDs value to false, the system does not generate an OID for each row.
- OID: In PostgreSQL, an object identifier (OID) is assigned to every row, defining it as a unique identifier. This is a unique identifier of every row.
How OID Works in PostgreSQL?
Below is the working of OID in PostgreSQL:
- We can define a unique identifier for each row in PostgreSQL. By default, OID is disabled for tables in PostgreSQL. To enable it, we need to specify it during table creation.
- By default, PostgreSQL disables the OID, as demonstrated in the following example. We need to define the same at the time of table creation.
Code:
select OID, * from stud1;
create table OID_Test (id int, name varchar, address varchar, phone int) with (oids = true);
insert into OID_Test (id, name, address, phone) values (1, 'ABC', 'Mumbai', 1234567890);
insert into OID_Test (id, name, address, phone) values (2, 'ABC', 'Mumbai', 1234567890);
select OID, * from OID_Test;Output:
- In the stud1 table, we do not have to define OID during table creation, so it will issue the error while selecting data from the table.
- In the second example, we have created table OID_Test, at the time of table creation, we have to define with OIDS as true. After defining the value of OID as true, it will show the OID values from the table.
- PostgreSQL hides the OID column by default, but we can select it by explicitly specifying the name of the OID column.
By default, PostgreSQL hides the OID column, as demonstrated in the following example.
Code:
select * from OID_Test;
select OID, * from OID_Test;Output:
- In the first example, we have not defined the OID column, so it will not show the data from the OID column.
- In the second example, we have defined the OID column, after defining the OID column, it will show the data from the OID column.
Examples
Given below are the examples mentioned:
Example #1
Create a Table by using OID.
The below example shows that create a table by using OID. We have to create a table name as OID_test1.
Code:
create table OID_Test1 (id int, name varchar, address varchar, phone int) with (oids = true);
\d+ OID_Test1;Output:
Example #2
Create a Table without using OID.
The below example shows that create a table without using OID. We have created a table name OID_test2.
Code:
create table OID_Test2 (id int, name varchar, address varchar, phone int) with (oids = false);
\d+ OID_Test2;Output:
Example #3
Select the Data from the Table using OID.
The below example shows that select data from the table by using OID.
Code:
select OID, * from OID_Test1 where OID = 303176;
select OID, * from OID_Test1;Output:
Example #4
Delete Data from the Table using OID.
The below example shows that delete data from the table by using OID. We have deleted the “303179” OID from the table.
Code:
select * from OID_Test1;
delete from OID_Test1 where OID = 303179;
select OID, * from OID_Test1;Output:
Example #5
Update Data from the Table by using OID.
The below example shows that update data from the table by using OID.
Code:
select OID, * from OID_Test1;
update OID_Test1 set name = 'PQR' where OID = 303178;
select OID, * from OID_Test1;Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL OID” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
