Pseudocolumn in a database table

A pseudocolumn in Oracle is a special built-in column-like feature that behaves like a column but isn’t actually stored in the table. It can be queried just like a regular column but cannot be updated or inserted into. Examples include ROWNUM, ROWID, SYSDATE, LEVEL, and NEXTVAL/CURRVAL (from sequences). For instance, ROWNUM gives the sequence of rows returned, while ROWID provides the unique address of a row in the database. Pseudocolumns are often used in pagination, hierarchical queries, and data sequencing. They're particularly useful in filtering results, identifying specific records, or auto-generating sequence numbers. Though virtual, pseudocolumns are powerful tools in SQL querying and performance tuning.

---

A pseudocolumn in a database table is a special, read-only column that is not stored as part of the table data but is automatically available by the database for each row in a query.

---

🔍 Characteristics of a Pseudocolumn:

Not actually stored in the table.

Cannot be inserted or updated.

Acts like a regular column when you query.

Provided by the database engine, not the user.

---

✅ Common Examples in Oracle:

Pseudocolumn Description

ROWNUM Returns the row number in the result set. Useful for limiting rows.
ROWID Returns the unique address (physical location) of a row in the table.
LEVEL Used in hierarchical (tree-structured) queries with CONNECT BY.
SYSDATE Returns the current system date and time (technically a function, but often used like a pseudocolumn).
NEXTVAL, CURRVAL Used to fetch the next or current value of a sequence.

---

📘 Example Usage:

🔹 ROWNUM:

SELECT * FROM employees WHERE ROWNUM <= 5;

✅ Fetches the first 5 rows from the table (not necessarily ordered).

---

🔹 ROWID:

SELECT ROWID, employee_id FROM employees;

✅ Useful for identifying or deleting specific physical rows quickly.

---

🔹 LEVEL with hierarchical data:

SELECT employee_id, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

✅ Returns the hierarchy of employees (org chart).

---

🔹 Sequence pseudocolumns:

SELECT my_seq.NEXTVAL FROM dual;

✅ Fetches the next value from a sequence for primary key generation.

---

🔒 Limitations:

You cannot insert, update, or delete from pseudocolumns.

Not all databases support the same pseudocolumns (e.g., ROWNUM is Oracle-specific).

---

🧠 Why use pseudocolumns?

Help in pagination, filtering, hierarchical data queries, sequence generation, debugging physical rows, etc.

Improve performance in certain situations (e.g., using ROWID for fast deletes).