DevLane Knowledge Hub • 45+ Verified Recipes

Battle-Tested Problems & Production Solutions

Master high-frequency database interview queries, DDL schema alterations, and backend systems recipes. Interact with live parameter controls and simulate queries right in your browser.

45 Technical Recipes26 ALTER TABLE Operations25 Date & Time Solutions100% In-Browser Execution

Recipe Catalog (45)

Showing 45 recipes

Data Definition Language (DDL): CREATE, ALTER, DROP, TRUNCATE

🎯Problem & Goal:

Learn how to define, modify, and deallocate database structures using Data Definition Language (DDL) commands: CREATE, ALTER, DROP, and TRUNCATE.

#PostgreSQL#DDL#CREATE#TRUNCATE

PostgreSQL 16

SQL FundamentalsBeginner

1-- 1. Create structure

2CREATE TABLE students (

3 id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

4 name VARCHAR(100) NOT NULL,

5 age INT

6);

8-- 2. Modify structure without recreating

9ALTER TABLE students ADD COLUMN email VARCHAR(255);

11-- 3. Fast truncate (keeps schema, removes rows without row-level logging)

12TRUNCATE TABLE students RESTART IDENTITY;

Interactive Browser Sandbox

psql output consoleReady

2.1ms3 rows

CREATE TABLE / ALTER TABLE / TRUNCATE completed in 2.1msPostgreSQL 16.2 (Debian)

command	target_object	status
CREATE TABLE	students	CREATED
ALTER TABLE	students.email	COLUMN ADDED
TRUNCATE	students	PAGES DEALLOCATED

Level Up Your Database & System Skills

Want to master database architecture & high-scale backend engineering?

Join our live courses to build production-grade distributed systems, master indexing, query optimization, connection pooling, and multi-tenant database designs.

Explore Live Courses

Battle-Tested Problems & Production Solutions

Data Definition Language (DDL): CREATE, ALTER, DROP, TRUNCATE

Transaction Control Language (TCL): BEGIN, COMMIT & ROLLBACK

1. Add a Column to Existing Table

2. Add Multiple Columns in Single Statement

3. Drop (Delete) a Column

4. Rename a Column

5. Rename the Entire Table

6. Change Column Data Type

7. Set a Default Value for a Column

8. Remove Default Value from Column

9. Add NOT NULL Constraint

10. Remove NOT NULL Constraint

11. Add PRIMARY KEY Constraint

12. Remove PRIMARY KEY Constraint

13. Add UNIQUE Constraint

14. Remove UNIQUE Constraint

15. Add CHECK Constraint

16. Remove CHECK Constraint

17. Add FOREIGN KEY Relationship

18. Remove FOREIGN KEY

19. Add Multiple Constraints in Single Query

20. Drop Multiple Columns in One Statement

21. Add an Identity (Auto-Increment) Column

22. Remove Identity (Auto-Increment) from Column

23. Change Identity Behavior (BY DEFAULT vs ALWAYS)

24. Move Table to Another Schema

25. Change Table Owner

26. Enable or Disable Table Triggers

NULL Comparison Rules: IS NULL, IS NOT NULL & IS TRUE

Safe NULL Comparison Using IS DISTINCT FROM

Current Date & Time Functions: NOW(), CURRENT_DATE, LOCALTIME

Extracting Parts of Date/Time Using EXTRACT()

Extracting Parts with DATE_PART()

Calculate Time Difference Using AGE()

Truncate Timestamps with DATE_TRUNC()

Convert Timestamps to Text Using TO_CHAR()

Convert Text to Date and Timestamp (TO_DATE & TO_TIMESTAMP)

Adding Date & Time with INTERVAL

Subtracting Date & Time with INTERVAL

Date Difference in Days

Is Date Between Two Dates (BETWEEN Operator)

Find Records Created Today

Find Upcoming Events in Next 30 Days

Find Unix Epoch Time (Seconds since 1970)

Async PostgreSQL Connection Pool with asyncpg & Context Manager

Data Definition Language (DDL): CREATE, ALTER, DROP, TRUNCATE

Want to master database architecture & high-scale backend engineering?