Delete vs Truncate vs Drop

Explain the difference between DELETE, TRUNCATE, and DROP in SQL.

Use PostgreSQL-compatible SQL to demonstrate the expected effect of each operation without permanently modifying the supplied sample tables. Report the affected rows, remaining rows, and whether the table object remains available.

Output

1. One row for each operation: DELETE, TRUNCATE, and DROP
2. Columns: operation, rows_affected, rows_remaining, and object_state
3. Order rows in the sequence DELETE, TRUNCATE, DROP; use NULL for values that do not apply

Output details

• Include a fifth column named sort_order after object_state, containing 1, 2, and 3 for DELETE, TRUNCATE, and DROP.
• For DELETE, treat rows with marker NULL as affected and rows with non-NULL marker as remaining.
• For TRUNCATE, report all existing rows as affected and 0 as remaining.
• For DROP, report all existing rows as affected and NULL as remaining.
• Use table remains for DELETE and TRUNCATE, and table removed for DROP in object_state.
• Return all row counts as bigint values.