Column Defaults

Every way to specify PostgreSQL DEFAULT expressions in Socigy.OpenSource.DB v0.3.2: C# initializers, [Default], DbDefaults constants, and how defaults interact with ExcludeAutoFields().

updated 5 Jun 20268 min readv0.3.2View as Markdown

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.

C# property initializer. The migration CLI reads the initializer value and emits a DEFAULT expression automatically.
[Default] with no argument. Flags the column as having a DB-side default without emitting a DDL expression (used with [AutoIncrement] and similar).
[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.

[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:

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:

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:

"state" integer DEFAULT 0

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

[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:

"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:

[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:

[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:

"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:

[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:

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.

// 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.

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

[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:

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.

// [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

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; }
}

← previousColumns & types

next →Constraints