PostgreSQL SYSTEM Columns

PostgreSQL SYSTEM Columns is a SQL function in the PostgreSQL Specific category. PostgreSQL: hidden system columns that provide internal metadata about each row. The syntax is SELECT ctid, xmin, xmax, tableoid FROM table_name;. It accepts see syntax. It returns system column values. A typical example: SELECT ctid, xmin, xmax, cmin, cmax FROM employees; -- ctid: physical row location (page, tuple) -- (0,1) = page 0, tuple 1 -- Useful for finding duplicates or row location -- xmin: transaction ID that created this row version -- xmax: transaction ID that deleted/locked this row version -- (or 0 if not deleted) -- cmin, cmax: command IDs within transaction -- Practical uses: -- Find duplicate rows: SELECT ctid,… A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).