INSERT Command

Overview

The INSERT statement is used to add new records to a database table. You must specify the target table, the columns you wish to populate, and the corresponding values for those columns.

Syntax

MaazDB supports three primary ways to insert data:

INSERT INTO <table_name> (column1, column2) VALUES (value1, value2);

INSERT INTO <table_name> (column1, column2) VALUES 
(value1, value2), 
(value3, value4);

INSERT INTO <table_name> (column1, column2) 
SELECT columnA, columnB FROM <other_table> [WHERE condition];

Basic Examples

Simple Insert

Adding a user with a name and age:

INSERT INTO users (username, age) VALUES ('Alice', 30);

Insert with All Supported Types

Inserting a product with text, double, boolean, and timestamp data:

INSERT INTO products (name, price, is_active, created_at) 
VALUES ('Gaming Mouse', 59.99, TRUE, '2024-01-15 14:30:00');

Inserting Multiple Rows

You can insert multiple records in a single query by providing comma-separated groups of values enclosed in parentheses. This is highly recommended for bulk inserts as it significantly improves performance by reducing network round-trips.

INSERT INTO products (name, price, is_active) VALUES 
('Mechanical Keyboard', 120.00, TRUE),
('4K Monitor', 399.99, TRUE),
('Mousepad', 15.50, FALSE);

Insert from SELECT

You can populate a table using the results of a SELECT query from another table. This is incredibly useful for archiving data, creating backups, or transforming data between tables.

-- Archive inactive users into the 'archived_users' table
INSERT INTO archived_users (user_id, name, email)
SELECT id, name, email FROM users WHERE is_active = FALSE;

Handling Data Types

MaazDB supports specific formatting for different data types during insertion.

Strings (TEXT)

Enclose strings in single (') or double (") quotes.

INSERT INTO logs (message) VALUES ('System started successfully');

Numbers (INT, DOUBLE)

Enter numbers directly without quotes.

INSERT INTO measurements (temperature, pressure) VALUES (23.5, 1013);

Booleans (BOOL)

Use TRUE or FALSE (case-insensitive).

INSERT INTO tasks (completed) VALUES (FALSE);

Timestamps (TIMESTAMP)

Must follow the exact format 'YYYY-MM-DD HH:MM:SS' enclosed in quotes.

INSERT INTO events (event_time) VALUES ('2023-12-31 23:59:59');

Handling SERIAL Columns

If a table has a primary key defined as SERIAL (auto-incrementing integer), you must not include it in the INSERT statement.

-- Table 'users' has columns: id (SERIAL), name (TEXT)
INSERT INTO users (name) VALUES ('Bob'); 
-- 'id' will be automatically generated (e.g., 1, 2, 3...)

Foreign Key Constraints

When inserting into a column that is a FOREIGN KEY, the value must already exist in the referenced parent table.

-- 1. Parent Table: users (id=1 exists)
-- 2. Child Table: orders (user_id references users.id)

-- ✅ SUCCESS: User 1 exists
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);

-- ❌ FAILURE: User 999 does not exist
INSERT INTO orders (user_id, amount) VALUES (999, 50.00);
-- Error: Foreign key constraint violation

Common Pitfalls

❌ Column Count Mismatch

The number of columns must match the number of values.

INSERT INTO users (name, age) VALUES ('Alice'); 
-- Error: Expected 2 values, got 1

❌ Type Mismatch

Trying to insert text into a numeric field.

INSERT INTO users (age) VALUES ('twenty'); 
-- Error: Cannot parse 'twenty' as INT

DML Comparison