Definition of 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 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 by using the clause without OID.
Syntax:
Below is the syntax of OID in PostgreSQL:
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 syntax of OID in PostgreSQL
- Select – It is used to select the OID column from the table. We can select an OID column with another table column at one time.
- Update – Update operation is used to update the table data. 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 is used to display a table name that is uniquely specified.
- Data type –This is defined as assign the data type of column at the time of table creation. We can define any data type to the column.
- With OIDS –This is defined as creating the table by using OIDS, if we define OIDs value as true then OID will be generated to every row in PostgreSQL.
- Without OIDS – This is defined as creating the table without using OIDS, if we define OIDs value as false then OID will not generate to the row in PostgreSQL.
- OID –This is defined as an object identifier is defined to every row in PostgreSQL. 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 from the table, we need to enable at the time of creating the table.
- The below example shows that by default OID is disabled in PostgreSQL. We need to define the same at the time of table creation.
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;
- In stud1 table we have not to define OID at the time of 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.
- By default OID column is hidden in PostgreSQL, we can select this by specifying the name of the OID column.
The below example shows that by default OID column is hidden in PostgreSQL.
select * from OID_Test;
select OID, * from OID_Test;
- In the first example, we have not defined the OID column, so it will not shows 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
The below example shows that OID in PostgreSQL.
1. Create 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.
create table OID_Test1 (id int, name varchar, address varchar, phone int) with (oids = true);
\d+ OID_Test1;
2. Create Table without using OID
The below example shows that create a table without using OID. We have created a table name as OID_test2.
create table OID_Test2 (id int, name varchar, address varchar, phone int) with (oids = false);
\d+ OID_Test2;
3. Select the Data from Table using OID
The below example shows that select data from the table by using OID.
select OID, * from OID_Test1 where OID = 303176;
select OID, * from OID_Test1;
4. Delete Data from Table using OID
The below example shows that delete data from the table by using OID. We have deleted the “303179” OID from table.
select * from OID_Test1;
delete from OID_Test1 where OID = 303179;
select OID, * from OID_Test1;
5. Update Data from Table by using OID
The below example shows that update data from the table by using OID.
select OID, * from OID_Test1;
update OID_Test1 set name = 'PQR' where OID = 303178;
select OID, * from OID_Test1;
Recommended Articles
This is a guide to PostgreSQL OID. Here we discuss the definition, syntax, and its Parameters along with practical examples and How OID works in PostgreSQL?. You can also go through our suggested articles to learn more –
