# INSERT

Insert rows with the generated Insert() builder. Choose your fields, propagate DB-generated values back, and batch thousands of rows in a few round-trips.

## Overview

Every generated entity instance exposes an `.Insert()` method that returns a `PostgresqlInsertCommandBuilder<T>`. You choose which fields to include, optionally request that DB-generated values be propagated back to the instance, then execute. `ExecuteAsync()` returns `Task<bool>`.

## Basic Insert

The simplest insert includes all columns, including any `[Default]` columns (those whose values the database generates on insert, such as `id`, `created_at`, or sequence numbers).

```csharp
// insert a new user, including all fields
var user = new User { Id = Guid.NewGuid(), Name = "Alice", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

## Field Selection Strategies

The builder supports five mutually exclusive strategies for choosing which columns appear in the `INSERT`. Pick exactly one per insert.

### ExcludeAutoFields: Skip All [Default] Columns

Omits every column marked `[Default]` (columns whose value the database supplies automatically). This is the most common strategy, because it lets PostgreSQL handle `id`, `created_at`, sequences, and so on.

```csharp
// let the DB generate Id and CreatedAt
var user = new User { Name = "Bob", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();
```

### ExcludeAutoFields With Exceptions

Skips all `[Default]` columns **except** those you list explicitly. Useful when you want to override one default (supply your own `Id`, say) while still letting the database control the others.

```csharp
// supply Id manually, let DB handle CreatedAt
var user = new User { Id = Guid.NewGuid(), Name = "Carol", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields(include => new object?[] { include.Id })
    .ExecuteAsync();
```

### WithAllFields: Include Everything

Forces all columns (including `[Default]` ones) into the `INSERT`. Use when you want to supply every value yourself.

```csharp
// insert with all fields, including Id and timestamps
var user = new User
{
    Id = Guid.NewGuid(),
    Name = "Dave",
    Status = "active",
    CreatedAt = DateTimeOffset.UtcNow
};

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

> **WARNING** `WithAllFields()` and `ExcludeAutoFields()` cannot appear in the same chain. Combining them is a programming error and throws at runtime.

### WithFields: Include Only Listed Columns

Inserts only the columns you specify. Every other column is omitted from the statement.

```csharp
// insert only Name and Status
var user = new User { Name = "Eve", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Name, x.Status })
    .ExecuteAsync();
```

### ExcludeFields: Exclude Listed Columns

Includes all columns except those you specify. The inverse of `WithFields`.

```csharp
// insert everything except AuditLog
bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeFields(x => new object?[] { x.AuditLog })
    .ExecuteAsync();
```

## Value Propagation (RETURNING *)

`.WithValuePropagation()` appends `RETURNING *` to the statement. The database returns the complete row after insert, generated values filled in, and the library writes those values back onto the instance. This is the cleanest way to obtain `Id`, `CreatedAt`, sequence numbers, and any other DB-generated columns.

```csharp
// write generated values back to the instance
var user = new User { Name = "Frank", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

// user.Id and user.CreatedAt are now populated from the database
Console.WriteLine(user.Id);
Console.WriteLine(user.CreatedAt);
```

## Returning a Specific Column Value

When you only need one DB-generated value (most often the new primary key), use `ExecuteReturningAsync<T>()` instead of `ExecuteAsync()`. It returns `Task<T?>` and **requires the database column name** as a string argument.

```csharp
// return the generated Id
var user = new User { Name = "Grace", Status = "active" };

Guid? newId = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteReturningAsync<Guid>("id");

if (newId is not null)
    Console.WriteLine($"Created user {newId}");
```

The string argument is the column name in the database (snake_case), not the C# property name.

## Static Shorthand

`User.InsertAsync(instance, conn)` is a static convenience that inserts the row over a plain connection and returns `Task<bool>`. It is the quickest path when you do not need field selection or value propagation.

```csharp
// static shorthand
bool ok = await User.InsertAsync(user, conn);
```

> **NOTE** The static shorthand exposes no field selection or value propagation. If you need `ExcludeAutoFields`, `WithValuePropagation`, or any other option, use the instance builder.

## Using With Transactions

Pass a `DbTransaction` instead of a `DbConnection` when the insert is part of a larger unit of work.

```csharp
// insert inside a transaction
await using var tx = await conn.BeginTransactionAsync();

bool ok = await user.Insert()
    .WithTransaction(tx)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

await tx.CommitAsync();
```

## Bulk Insert with InsertMultipleAsync

To insert a whole collection, use the static `InsertMultipleAsync` instead of a command per row. It builds **batched multi-row `INSERT ... VALUES (…),(…),…` commands**, one command per chunk, which is dramatically faster than looping. It returns `Task<int>`, the total number of rows inserted.

```csharp
// insert many rows in one (or a few) round-trips
var users = new List<User>
{
    new() { Name = "Alice", Status = "active" },
    new() { Name = "Bob",   Status = "active" },
    // ... thousands more ...
};

int inserted = await User.InsertMultipleAsync(users, conn);
```

The signature is `InsertMultipleAsync(rows, conn, transaction = null, includeAutoFields = false, cancellationToken = default)`.

- Returns the **total number of rows inserted**.
- By default (`includeAutoFields = false`), **`[Default]` and sequence columns are skipped** so the database generates them, the same as `ExcludeAutoFields()`. Pass `includeAutoFields: true` to send those columns yourself.
- Automatically **chunks** so each command stays under PostgreSQL's 65,535-parameter limit (`maxRowsPerBatch ≈ 65535 / columnCount`). A 10,000-row insert of a 6-column table becomes a handful of commands rather than 10,000.
- `[Encrypted]` and JSON columns are handled (bound as `bytea` / `jsonb`).
- Each command is traced through the diagnostics pipeline as a single `INSERT`.

Pass a transaction to make the whole batch atomic with other work:

```csharp
await using var tx = await conn.BeginTransactionAsync();
await User.InsertMultipleAsync(users, conn, tx);
await tx.CommitAsync();
```

> **NOTE** `InsertMultipleAsync` uses the default insert plan (no per-row field selection or value propagation). If you need `WithValuePropagation` or custom field selection, use the per-instance `.Insert()` builder.

> **TIP** For extreme volumes (hundreds of thousands of rows) PostgreSQL's binary `COPY` protocol via `NpgsqlBinaryImporter` is still the throughput champion. `InsertMultipleAsync` is the sweet spot for everyday batches up to tens of thousands.

## See Also

- [UPDATE](/database/0.3.2/querying/writing/update) for updating existing rows
- [DELETE](/database/0.3.2/querying/writing/delete) for removing rows
- [SELECT / Query](/database/0.3.2/querying/reading/select) for reading rows