TRUNCATE - potatoscript/sqlite GitHub Wiki

🍬 What is TRUNCATE?

Think of TRUNCATE as a super vacuum cleaner 🧹
It clears out a whole table in one big whoooosh 🌪️

It’s like saying:

“Remove ALL the toys from the toy box, FAST!” 🧸🪀🪁🧼

---

❗ Important: SQLite Does NOT Support TRUNCATE

Wait what?! 😱
That’s right — SQLite does not use TRUNCATE like MySQL or PostgreSQL do.

But don’t worry — I’ll still explain it (because you might use MySQL or PostgreSQL someday).
And I’ll show you what to use instead in SQLite 💡

---

🧠 What does TRUNCATE do (in other DBs)?

TRUNCATE TABLE table_name;

• Deletes ALL rows ✅
• But does not log each row (faster than DELETE) 🚀
• Auto-resets IDs (like AUTO_INCREMENT) to 1 🔄

⚡ Faster than:

DELETE FROM table_name;

---

🍓 Example (in MySQL/PostgreSQL)

You have a table called fruits:

TRUNCATE TABLE fruits;

💥 Boom! All your fruits are gone. 🍎🍌🍇
Table is empty, but still exists (unlike DROP).

---

🧪 What about in SQLite?

🚫 SQLite doesn't support TRUNCATE.

Instead, you do:

DELETE FROM table_name;

✅ Same result: all rows deleted
❌ IDs not reset by default

---

🍒 How to Reset the IDs in SQLite?

Let’s say you have a table with AUTOINCREMENT.

After deleting everything:

DELETE FROM my_table;

You then reset the sequence like this:

DELETE FROM sqlite_sequence WHERE name = 'my_table';

✨ That resets the auto-increment counter back to 1!

---

💡 Summary Table

Feature	DELETE	TRUNCATE (Not in SQLite)
Deletes all rows	✅	✅
Logs each row	✅	❌
Faster	❌	✅
Resets auto-increment ID	❌ (need extra step)	✅
Supported in SQLite?	✅	❌

---