10.3. INSERT

Understanding INSERT in SQL

The INSERT statement adds new rows into a table. As a backend developer you will use it constantly when saving user data, orders, logs, and more.

This chapter focuses only on inserting data. Creating tables, updating, or selecting data are covered in other chapters.

Basic INSERT Syntax

The most common form of INSERT looks like this:

sql

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example:

sql

INSERT INTO users (id, email, is_active)
VALUES (1, 'alice@example.com', true);

This inserts a single row into the users table.

Key points:

table_name is the table where you add data.
Column list (column1, column2, ...) defines which columns you are setting.
VALUES provides the actual data.
The number of columns must match the number of values and the order must match.

Rule: The number of columns in the column list must be equal to the number of values in the VALUES list, and each value must be type compatible with its column.

If the table is:

sql

CREATE TABLE users (
    id         INT,
    email      VARCHAR(255),
    is_active  BOOLEAN
);

Then this is valid:

sql

INSERT INTO users (id, email, is_active)
VALUES (2, 'bob@example.com', false);

But this is not:

sql

-- Wrong: 3 columns, 2 values
INSERT INTO users (id, email, is_active)
VALUES (3, 'charlie@example.com');
-- Wrong: wrong type in is_active
INSERT INTO users (id, email, is_active)
VALUES (4, 'dan@example.com', 'yes');

Your database will raise an error in such cases.

Inserting into All Columns

If you want to insert values for all columns in the table, and you know the exact column order, you can omit the column list:

sql

INSERT INTO users
VALUES (5, 'eve@example.com', true);

The values must match the order that the columns were defined in the table schema.

This can be dangerous if the table schema changes, so in real projects you usually specify column names explicitly.

Comparison:

Style	Example	Pros	Cons
With column list	`INSERT INTO users (id, email) VALUES (1, 'a@b.com');`	Clear, safe, order does not matter	Slightly more to type
Without column list	`INSERT INTO users VALUES (1, 'a@b.com');`	Shorter	Breaks if table structure changes

Best practice for backend code:

Always specify the columns you are inserting into. This makes the code clearer and more robust.

Inserting Multiple Rows at Once

You can insert several rows in one INSERT statement:

sql

INSERT INTO users (id, email, is_active)
VALUES
    (10, 'user1@example.com', true),
    (11, 'user2@example.com', false),
    (12, 'user3@example.com', true);

This is often faster than multiple separate single-row inserts, and it reduces network round trips from your backend to the database.

Typical use cases:

Seeding initial data (for example default roles).
Importing a small batch of rows at once.

Using DEFAULT and NULL

Rows do not always need values for every column. Some columns may have:

A default value defined in the table.
Allow NULL, which represents "no value".

Assume this table:

sql

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    is_active  BOOLEAN NOT NULL DEFAULT true,
    bio        TEXT
);

Omitting columns

If a column has a default or allows NULL, you can simply omit it from the insert:

sql

INSERT INTO users (email)
VALUES ('no-bio@example.com');

Result for that row:

id is auto generated.
email is 'no-bio@example.com'.
is_active uses default true.
bio is NULL because we did not set it and it has no default.

Using DEFAULT explicitly

You can also explicitly say "use the default here":

sql

INSERT INTO users (email, is_active)
VALUES ('default-active@example.com', DEFAULT);

This makes it very clear that you want the column to use its default.

Inserting NULL

If a column allows NULL, you can insert NULL explicitly:

sql

INSERT INTO users (email, bio)
VALUES ('null-bio@example.com', NULL);

Difference:

Omitted column: database decides default or NULL according to table definition.
Explicit NULL: you are clearly saying "no value".

Inserting Specific Data Types

When inserting data you must respect column types.

Assume:

sql

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    in_stock    BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Strings (text)

Wrap inside single quotes.
Escape internal single quotes.

sql

INSERT INTO products (name, price)
VALUES ('Basic T-Shirt', 9.99);
INSERT INTO products (name, price)
VALUES ('Men''s Jacket', 59.90);  -- Note the doubled quote

Numbers

Do not use quotes.
Must be valid numeric values.

sql

INSERT INTO products (name, price)
VALUES ('Coffee Mug', 4.50);

Boolean

Most databases accept these:

true / false
1 / 0 (sometimes)
't' / 'f' (PostgreSQL)

Recommended:

sql

INSERT INTO products (name, price, in_stock)
VALUES ('Notebook', 2.00, true);

Dates and timestamps

Use standard formats:

sql

INSERT INTO products (name, price, created_at)
VALUES ('Limited Edition Poster', 15.00, '2024-01-15 10:30:00');

If the column has a default like NOW(), you can omit:

sql

INSERT INTO products (name, price)
VALUES ('Sticker Pack', 3.00);

INSERT with SELECT (Copying Data)

You can insert rows that come from another query, not from literal values.

Pattern:

sql

INSERT INTO target_table (col1, col2, col3)
SELECT other_col1, other_col2, other_col3
FROM source_table
WHERE some_condition;

Example: Copy all active users into an active_users_archive table.

sql

INSERT INTO active_users_archive (user_id, email, archived_at)
SELECT id, email, NOW()
FROM users
WHERE is_active = true;

Here:

The SELECT produces rows.
The INSERT takes those rows and adds them to active_users_archive.
No VALUES clause is used when you insert from a SELECT.

Use cases:

Archiving data.
Copying data between tables with similar structure.
Creating backup snapshots of some rows.

The number and order of columns in the INSERT column list must match the number and order of expressions in the SELECT list.

Handling Auto-Increment / SERIAL Columns

Many tables have auto-generated primary keys, such as SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL, or identity columns.

Example table:

sql

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL
);

You normally do not insert into the id column:

sql

INSERT INTO users (email)
VALUES ('generated-id@example.com');

The database will choose the id for you.

If you try to insert your own id that conflicts with an existing one, you will get an error:

sql

INSERT INTO users (id, email)
VALUES (1, 'duplicate-id@example.com');  -- Fails if id 1 exists

In backend code, the common pattern is:

Do not set the auto-increment primary key in your insert.
Let the database generate it.
Retrieve the generated id if needed.

How to get the generated id depends on language and driver. For PostgreSQL you can also use:

sql

INSERT INTO users (email)
VALUES ('returning-id@example.com')
RETURNING id;

This returns the newly generated id as part of the query result.

INSERT and Constraints

Tables often have constraints such as:

PRIMARY KEY
UNIQUE
NOT NULL
FOREIGN KEY

When you insert rows, these constraints are checked.

Examples:

sql

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE posts (
    id        SERIAL PRIMARY KEY,
    user_id   INT NOT NULL REFERENCES users(id),
    title     VARCHAR(255) NOT NULL
);

PRIMARY KEY and UNIQUE

If a column is UNIQUE or a primary key, inserting a duplicate value causes an error:

sql

INSERT INTO users (email)
VALUES ('unique@example.com');
INSERT INTO users (email)
VALUES ('unique@example.com');  -- Error: duplicate key value

NOT NULL

You cannot insert NULL or omit a required column without default:

sql

INSERT INTO users (email)
VALUES (NULL);  -- Error: email cannot be null

FOREIGN KEY

A foreign key must refer to an existing row in another table:

sql

INSERT INTO posts (user_id, title)
VALUES (9999, 'Post by non-existent user');  -- Error

User with id 9999 does not exist, so the insert fails.

From a backend perspective this means:

You often need to insert rows in the correct order, for example users before posts.
You must handle errors when constraints are violated.

INSERT with RETURNING (Database Specific)

Some databases support RETURNING to return inserted rows. This is particularly useful in backend applications where you want to get the inserted row without doing a second query.

Example (PostgreSQL):

sql

INSERT INTO users (email)
VALUES ('returning@example.com')
RETURNING id, email;

The result might look like:

id	email
42	returning@example.com

As a backend developer you can:

Insert the row.
Get the generated id and other fields immediately.
Return them from your API response.

This is more efficient than:

Insert the row.
Do another SELECT with some unique key to fetch it.

Note: RETURNING is not part of standard SQL, but similar features exist in many databases, or in the database driver / ORM.

INSERT in Backend Code and Security

When you use INSERT from code, you must never build SQL strings by concatenating user input directly. That exposes you to SQL injection attacks.

Unsafe example (Python, do not do this):

python

email = input_email_from_user  # Untrusted
sql = f"INSERT INTO users (email) VALUES ('{email}');"
cursor.execute(sql)  # Dangerous

Safe example with parameterized query:

python

email = input_email_from_user  # Untrusted
cursor.execute(
    "INSERT INTO users (email) VALUES (%s);",
    (email,)
)

The database driver will safely escape the value.

Always use parameterized queries for INSERT statements that include user input. Never put raw user input into SQL strings.

In most backend frameworks and ORMs, parameterization is handled for you, but you should still understand why it matters.

Practical Examples

Example 1: Simple user registration

Table:

sql

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Insert when a new user registers:

sql

INSERT INTO users (email, password_hash)
VALUES ('newuser@example.com', 'hashed_password_here');

With RETURNING to get id:

sql

INSERT INTO users (email, password_hash)
VALUES ('newuser@example.com', 'hashed_password_here')
RETURNING id;

Example 2: Seeding roles

sql

CREATE TABLE roles (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

Insert multiple rows:

sql

INSERT INTO roles (name)
VALUES
    ('admin'),
    ('editor'),
    ('viewer');

Example 3: Copy active products to a sale table

sql

CREATE TABLE products (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    price   NUMERIC(10, 2) NOT NULL,
    active  BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE sale_products (
    id           SERIAL PRIMARY KEY,
    original_id  INT NOT NULL,
    name         VARCHAR(100) NOT NULL,
    sale_price   NUMERIC(10, 2) NOT NULL
);

Insert with SELECT:

sql

INSERT INTO sale_products (original_id, name, sale_price)
SELECT id, name, price * 0.8
FROM products
WHERE active = true;

This copies all active products with a 20% discount into sale_products.

Summary

INSERT adds new rows to a table.
Use INSERT INTO table_name (columns) VALUES (...); for single or multiple rows.
Omit columns that have defaults or allow NULL, or use DEFAULT explicitly.
Respect data types and constraints such as NOT NULL, UNIQUE, and FOREIGN KEY.
Use INSERT ... SELECT ... to copy data from one table to another.
For auto-increment keys, normally omit the id column and optionally use RETURNING where supported.
In backend code, always use parameterized queries to avoid SQL injection.

Understanding INSERT is a core skill. You will use it whenever your backend saves new data to the database.