# Column Defaults

Every way to specify PostgreSQL DEFAULT expressions in Socigy.OpenSource.DB: C# initializers, [Default], DbDefaults constants, and how defaults interact with ExcludeAutoFields() and the convenience-method InsertFields enum / keep selector.

## Overview

Socigy.OpenSource.DB provides three layered mechanisms for column defaults. Understanding all three, and how they interact, prevents the most common DDL and INSERT surprises.

1. **C# property initializer.** The migration CLI reads the initializer value and emits a `DEFAULT` expression automatically.
2. **`[Default]` with no argument.** Flags the column as having a DB-side default without emitting a DDL expression (used with `[AutoIncrement]` and similar).
3. **`[Default("expr")]` or `[Default(DbDefaults.Constant)]`.** Emits an explicit `DEFAULT` expression in the DDL.

## Section 1: C# Initializer as Default

> **NOTE** This is the feature most developers miss. You do not need `[Default]` for simple literal defaults.

When a property carries no `[Default]` attribute, the migration CLI inspects the C# property initializer. A non-zero, non-empty initializer is emitted directly as the `DEFAULT` expression in DDL.

```csharp
[Table("courses")]
public partial class Course
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    // No [Default]. CLI reads the initializer "DEFAULT NAME".
    public string Name { get; set; } = "DEFAULT NAME";

    public int Priority { get; set; } = 5;

    public bool IsPublished { get; set; } = true;
}
```

Generated DDL:

```sql
CREATE TABLE "courses" (
    "id"           UUID    NOT NULL DEFAULT gen_random_uuid(),
    "name"         TEXT    NOT NULL DEFAULT 'DEFAULT NAME',
    "priority"     INTEGER NOT NULL DEFAULT 5,
    "is_published" BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY ("id")
);
```

### What Produces a DEFAULT and What Does Not

| C# initializer | DDL `DEFAULT` emitted |
|---|---|
| `= "Hello"` | `DEFAULT 'Hello'` |
| `= 42` | `DEFAULT 42` |
| `= true` | `DEFAULT TRUE` |
| `= MyEnum.Value` | `DEFAULT 0` (underlying integer) |
| `= MyDbEnum.Value` | `DEFAULT 'Value'` (PostgreSQL enum string, see below) |
| `= 0` | *(ignored: zero value)* |
| `= false` | *(ignored: zero value)* |
| `= ""` | *(ignored: empty string)* |
| `= null` | *(ignored)* |
| `= Guid.Empty` | *(ignored)* |

Zero-values are the C# default for every value type, so the CLI cannot distinguish "you intentionally wrote `= 0`" from "you left the initializer at the default". Use `[Default(DbDefaults.Number.Zero)]` when you want an explicit `DEFAULT 0` in DDL.

### Enum Initializers

The behavior depends on whether the enum carries `[DatabaseEnum]`.

A **regular enum** (stored as `INTEGER`) emits the initializer as the underlying integer:

```csharp
public enum VersionState { Draft, Published, Archived }

[Table("project_versions")]
public partial class ProjectVersion
{
    // Emits: DEFAULT 0
    public VersionState State { get; set; } = VersionState.Draft;
}
```

Generated DDL fragment:

```sql
"state" integer DEFAULT 0
```

A **`[DatabaseEnum]` enum** (stored as a PostgreSQL `ENUM` type) emits the initializer as the quoted member name:

```csharp
[DatabaseEnum]
public enum Priority { Low, Normal, High }

[Table("tasks")]
public partial class Task
{
    // Emits: DEFAULT 'Normal'
    public Priority Priority { get; set; } = Priority.Normal;
}
```

Generated DDL fragment:

```sql
"priority" priority DEFAULT 'Normal'
```

> **NOTE** The zero-value rule still applies to enums. `= VersionState.Draft` is emitted as a `DEFAULT` only if `Draft` is **not** the first member (that is, not `0`). If `Draft = 0`, the initializer is treated as "no default" and no `DEFAULT` clause is emitted. Use `[Default("0")]` or `[Default(DbDefaults.Number.Zero)]` to force an explicit `DEFAULT 0` in that case.

> **NOTE** Combined flag values such as `= Test.First | Test.Fourth` are silently ignored and produce no `DEFAULT` clause. A combined value like `9` is not a valid single-row reference in the enum's database table. Use `[Default("9")]` to emit it verbatim if you intentionally store bitmasks in the column.

## Section 2: Naked `[Default]`, Flag Only

`[Default]` with no argument tells the framework that this column has a database-side default, but it does **not** emit any DDL expression. The column is still marked so that `ExcludeAutoFields()` skips it during INSERT.

This form is used with `[AutoIncrement]` (sequence-assigned values) or with a primary key whose value is generated outside the application:

```csharp
[Table("user_login")]
public partial class UserLogin
{
    // Naked [Default]: no DDL expression emitted.
    // ExcludeAutoFields() will skip this column on INSERT.
    [PrimaryKey, Default]
    public Guid Id { get; set; }

    public string Username { get; set; }
    public string? PasswordHash { get; set; }
}
```

The resulting DDL column is `"id" UUID NOT NULL`, with no `DEFAULT` clause, because no expression was given. The application supplies or assigns the value through some other means (a trigger, an application-side call, and so on).

> **TIP** If you want the database to generate the UUID automatically, use `[Default(DbDefaults.Guid.Random)]` instead of bare `[Default]`.

## Section 3: `[Default("expr")]`, Raw SQL Expression

Pass a raw SQL expression as a string to emit it verbatim as the `DEFAULT` clause:

```csharp
[Default("'pending'")]
public string Status { get; set; }

[Default("ARRAY[]::TEXT[]")]
public string[] Tags { get; set; } = [];

[Default("(CURRENT_DATE + INTERVAL '30 days')")]
public DateTime ExpiresAt { get; set; }
```

Generated DDL fragments:

```sql
"status"     TEXT      NOT NULL DEFAULT 'pending',
"tags"       TEXT[]    NOT NULL DEFAULT ARRAY[]::TEXT[],
"expires_at" TIMESTAMP NOT NULL DEFAULT (CURRENT_DATE + INTERVAL '30 days')
```

> **WARNING** The string you pass is inserted verbatim. You are responsible for correct SQL syntax. Strings must include their own single quotes (for example `"'pending'"`, not `"pending"`).

## Section 4: `DbDefaults` Built-in Constants

`DbDefaults` provides named constants for the most common default expressions. Using them instead of raw strings keeps your code DB-engine-agnostic and protected against typos.

| Constant | DDL expression emitted |
|---|---|
| `DbDefaults.Guid.Random` | `DEFAULT gen_random_uuid()` |
| `DbDefaults.Guid.Sequential` | `DEFAULT uuid_generate_v1mc()` |
| `DbDefaults.Time.Now` | `DEFAULT timezone('utc', now())` |
| `DbDefaults.Time.NowLocal` | `DEFAULT now()` |
| `DbDefaults.Time.Date` | `DEFAULT current_date` |
| `DbDefaults.Bool.True` | `DEFAULT TRUE` |
| `DbDefaults.Bool.False` | `DEFAULT FALSE` |
| `DbDefaults.Number.Zero` | `DEFAULT 0` |
| `DbDefaults.Number.One` | `DEFAULT 1` |
| `DbDefaults.Text.Empty` | `DEFAULT ''` |

Usage:

```csharp
[Table("test_items")]
public partial class TestItem
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Name { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }

    [Default(DbDefaults.Bool.True)]
    public bool IsActive { get; set; }

    [Default(DbDefaults.Number.Zero)]
    public int RetryCount { get; set; }
}
```

Generated DDL:

```sql
CREATE TABLE "test_items" (
    "id"          UUID      NOT NULL DEFAULT gen_random_uuid(),
    "name"        TEXT      NOT NULL,
    "created_at"  TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    "is_active"   BOOLEAN   NOT NULL DEFAULT TRUE,
    "retry_count" INTEGER   NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);
```

> **NOTE** `uuid_generate_v1mc()` (used by `DbDefaults.Guid.Sequential`) requires the `uuid-ossp` extension. Run `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";` before applying migrations that use this constant.

## Section 5: `ExcludeAutoFields()` and Inserts

The INSERT builder's `ExcludeAutoFields()` method skips any property that carries a `[Default]` attribute in any form. This lets the database apply the `DEFAULT` expression instead of receiving an explicit value from the application.

```csharp
// Id has [Default(DbDefaults.Guid.Random)], excluded by ExcludeAutoFields().
// CreatedAt has [Default(DbDefaults.Time.Now)], excluded by ExcludeAutoFields().
// Name has no [Default], included.
var item = new TestItem { Name = "Hello" };

await item.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()   // skips Id and CreatedAt
    .ExecuteAsync();

// Resulting SQL (approximate):
// INSERT INTO "test_items" ("name") VALUES (@name)
// The DB fills "id" and "created_at" from their DEFAULT expressions.
```

> **WARNING** A `[Default]` column only uses its server default when the column is **excluded** from the INSERT. On any path that includes it, the property's **current value** is written, so a `[Default(DbDefaults.Time.Now)] CreatedAt` left unset is stored as `default(DateTime)` (`0001-01-01`), not `now()`. Exclude it to get the server default.

On the convenience (context, static, and bulk) insert paths, pass `InsertFields.ServerDefaults` for the same effect as the fluent `ExcludeAutoFields()`: it omits both auto-increment and `[Default]` columns so the server fills them (new in 0.3.5, replacing the 0.3.4 `excludeDbDefaults` / `includeAutoFields` booleans). These symbols live in `Socigy.OpenSource.DB.Core.CommandBuilders`:

```csharp
using Socigy.OpenSource.DB.Core.CommandBuilders;

await db.Items.InsertAsync(item, InsertFields.ServerDefaults);                       // database context
await TestItem.InsertMultipleAsync(items, conn, fields: InsertFields.ServerDefaults); // static batch
await BulkCopy.InsertMultipleCopyAsync(items, conn, fields: InsertFields.ServerDefaults); // binary COPY
```

> **NOTE** The static methods take `transaction` before `fields` positionally, so call them with the `fields:` named argument (as above). Use `InsertFields.IncludeAutoIncrement` to also write auto-increment columns yourself (the equivalent of the fluent `WithAllFields()`), and `InsertFields.Default` for the default behavior (auto-increment skipped, `[Default]` columns written from the property's current value).

### Keeping Some Defaults While the Server Fills the Rest

`InsertFields.ServerDefaults` omits **every** `[Default]` column. When you want to write some of them yourself (a manually-assigned id, say) while still letting the server fill the others, pass a `keep` selector naming the columns to write. It is the convenience-method equivalent of the fluent `ExcludeAutoFields(include)`, and works on both the batched `INSERT` and the binary `COPY` paths.

Consider a `User` whose `Id` and `CreatedAt` both carry a `[Default]`:

```csharp
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public required string Username { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
```

To set `Id` yourself but let the database fill `CreatedAt`, keep `Id` and leave the rest to the server:

```csharp
using Socigy.OpenSource.DB.Core.CommandBuilders;

var user = new User { Id = Guid.NewGuid(), Username = "alice" };

// keep Id (write our value); server fills the other [Default] column (CreatedAt)
await db.Users.InsertAsync(user, InsertFields.ServerDefaults, keep: u => new object?[] { u.Id });
```

> **WARNING** With plain `InsertFields.ServerDefaults` (no `keep`), `Id` would **also** be omitted and the database would generate a fresh Guid from its `DEFAULT gen_random_uuid()`. List a column in `keep` whenever you need your own value for it while the rest of the `[Default]` columns are server-filled.

### Adding `[Default]` to an existing column changes every ServerDefaults insert

`InsertFields.ServerDefaults` omits a column because it carries `[Default]`, not because the row left it
unset. So putting `[Default]` on a column that already exists silently changes the behaviour of every
`ServerDefaults` insert that does not name it in `keep` — the row is written with the database default instead
of the value the application assigned, with no compile error, no runtime error and no log line.

This is the main reason to reach for **`InsertFields.ServerDefaultsWhenUnset`** (new in 0.3.8) instead. It
omits a `[Default]` column only where the property still holds its CLR type default, so a value you assigned
survives and `keep` becomes unnecessary in the common case:

```csharp
var item = new TestItem { CreatedAt = importedAt };   // a value we mean to keep

await db.Items.InsertAsync(item, InsertFields.ServerDefaults);            // created_at = now(); importedAt lost
await db.Items.InsertAsync(item, InsertFields.ServerDefaultsWhenUnset);   // created_at = importedAt
```

The generator also reports [`SCGDB027`](/database/0.3.8/advanced/generator-diagnostics#scgdb027-serverdefaults-omits-a-default-column)
at each `ServerDefaults` insert that omits a `[Default]` column — at **Info** severity, so it stays out of
build output until you promote it to audit your call sites. See
[INSERT → Letting the server fill columns](/database/0.3.8/querying/writing/insert#letting-the-server-fill-columns).

See [INSERT](/database/0.3.8/querying/writing/insert#bulk-insert-with-insertmultipleasync) for the full batch signature.

The naked `[Default]` (no argument) form is designed for `[AutoIncrement]` columns where the sequence generates the value:

```csharp
[Table("test_counters")]
public partial class TestCounter
{
    [PrimaryKey]
    public Guid Id { get; set; }

    [AutoIncrement]          // implies the column has a DB-side sequence default
    public int Seq { get; set; }

    public string Label { get; set; }
}

var counter = new TestCounter { Id = Guid.NewGuid(), Label = "first" };

// Seq is excluded because [AutoIncrement] marks it as having a DB default.
// The sequence nextval() expression in DDL assigns the value.
await counter.Insert()
    .WithConnection(conn)
    .ExecuteAsync();
```

### Reading Back the DB-Assigned Value

Use `WithValuePropagation()` to execute the INSERT with `RETURNING *` and populate the instance's excluded fields from the returned row:

```csharp
var counter = new TestCounter { Id = Guid.NewGuid(), Label = "VP" };

await counter.Insert()
    .WithConnection(conn)
    .WithValuePropagation()
    .ExecuteAsync();

// counter.Seq is now populated with the sequence value assigned by PostgreSQL.
Console.WriteLine(counter.Seq);  // e.g. 42
```

## Section 6: Precedence of `[Default]` over the C# Initializer

When both a `[Default]` attribute and a C# initializer are present on the same property, the `[Default]` attribute always wins. The initializer is ignored for DDL purposes.

```csharp
// [Default] wins. DDL emits DEFAULT gen_random_uuid(), not DEFAULT 'some-fixed-guid'.
[Default(DbDefaults.Guid.Random)]
public Guid Id { get; set; } = Guid.Parse("00000000-0000-0000-0000-000000000001");

// [Default] wins. DDL emits DEFAULT timezone('utc', now()), not DEFAULT '2020-01-01'.
[Default(DbDefaults.Time.Now)]
public DateTime CreatedAt { get; set; }
```

The C# initializer is consulted only when no `[Default]` attribute is present at all.

## Complete Example

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("orders")]
public partial class Order
{
    // DbDefaults constant: gen_random_uuid()
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    // C# initializer: DEFAULT 'pending'
    public string Status { get; set; } = "pending";

    // C# initializer: DEFAULT 1
    public int Priority { get; set; } = 1;

    // C# initializer: DEFAULT TRUE
    public bool RequiresShipping { get; set; } = true;

    // C# initializer on a regular enum: DEFAULT 1 (underlying integer of Urgent).
    // (assumes: public enum OrderUrgency { Normal, Urgent, Critical })
    public OrderUrgency Urgency { get; set; } = OrderUrgency.Urgent;

    // DbDefaults constant: DEFAULT timezone('utc', now())
    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }

    // Raw SQL expression: DEFAULT (CURRENT_DATE + INTERVAL '7 days')
    [Default("(CURRENT_DATE + INTERVAL '7 days')")]
    public DateTime DueDate { get; set; }

    // Naked [Default]: no DDL expression; skipped by ExcludeAutoFields().
    [Default]
    public int InternalToken { get; set; }

    public string? Notes { get; set; }
}
```