Table of Contents

Value Generation

Caution

In 3.0.0, the default value generation strategy has changed from the older SERIAL columns to the newer IDENTITY columns. Read the information below carefully if you are migrating an existing database.

Note

It's recommended that you start by reading the general Entity Framework Core docs on generated properties.

Identity and serial columns (auto-increment)

Introduction

Since PostgreSQL 10, the standard way to define auto-incrementing columns is "identity columns". Prior to version 10, "serial columns" were used, which are less SQL-compliant and generally more difficult to manage. For more information on these, see this blog post. Note that since PostgreSQL 10, both support smallint, integer and bigint as their data type.

The Npgsql EF Core provider allows you to choose which of the above you want on a property-by-property basis, or globally on your model. The following "value generation strategies" are available:

  • Identity by default: an identity column whose values are by default generated at the database, but you can still override this behavior by providing values from your application. This will generate the clause GENERATED BY DEFAULT AS IDENTITY on your column, and is the default value generation strategy.
  • Identity always: an identity column whose values are always generated at the database - you cannot provide values from your application. This will generate the clause GENERATED ALWAYS AS IDENTITY on your column.
  • Serial: the traditional PostgreSQL serial column. This will create the column with the serial datatype. Recommended only if you are using an older PostgreSQL version.
  • Sequence HiLo: See below

The default value generation strategy is "identity by default". In other words, when EF decides that an int property should be value generated (e.g. because it's named Id, or because you explicitly specified ValueGeneratedOnAdd on it), the Npgsql provider will automatically map it to an identity column.

Defining the default strategy for the entire model

You can easily control the value generation strategy for the entire model. For example, to opt out of the change to identity columns, simply place the following in your context's OnModelCreating():

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.UseIdentityByDefaultColumns();

Defining the strategy for a single property

Regardless of the model default, you can define a value-generation strategy on a property-by-property basis:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.Entity<Blog>().Property(b => b.Id).UseIdentityAlwaysColumn();

Identity sequence options

Identity columns have a standard sequence, managed behind the scenes by PostgreSQL; you can customize the sequence options for these. For example, the following makes the column values start at 100:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.Entity<Blog>().Property(b => b.Id)
        .HasIdentityOptions(startValue: 100);

This can be especially useful when seeding data. Seeded data must explicitly specify all columns - including database-generated ones - but the backing sequence for identity columns isn't aware that the values are in use, and will generate conflicting values. This technique allows to start your identity sequence at a value higher than all seeded data values. Another strategy is to seed negative values only, allowing your identity column to start at 1.

It is not possible to specify sequence options for serial columns, but you can set up a sequence separately and configure the column's default value (see sequence-driven columns).

Standard sequence-driven columns

While identity and serial columns set up a sequence for you behind the scenes, sometimes you may want to manage sequence creation yourself. For example, you may want to have multiple columns drawing their default values from a single sequence. Adding a sequence to your model is described in the general EF Core documentation; once the sequence is specified, you can simply set a column's default value to extract the next value from that sequence. Note that the SQL used to fetch the next value from a sequence differs across databases (see the PostgreSQL docs). Your models' OnModelCreating should look like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence<int>("OrderNumbers")
                .StartsAt(1000)
                .IncrementsBy(5);

    modelBuilder.Entity<Order>()
                .Property(o => o.OrderNo)
                .HasDefaultValueSql("nextval('\"OrderNumbers\"')");
}

HiLo Autoincrement Generation

One disadvantage of database-generated values is that these values must be read back from the database after a row is inserted. If you're saving multiple related entities, this means you must perform multiple round-trips as the first entity's generated key must be read before writing the second one. One solution to this problem is HiLo value generation: rather than relying on the database to generate each and every value, the application "allocates" a range of values, which it can then populate directly on new entities without any additional round-trips. When the range is exhausted, a new range is allocated. In practical terms, this uses a sequence that increments by some large value (100 by default), allowing the application to insert 100 rows autonomously.

To use HiLo, specify UseHiLo on a property in your model's OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.Entity<Blog>()
                   .Property(b => b.Id)
                   .UseHiLo();

You can also make your model use HiLo everywhere:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.UseHiLo();

GUID/UUID Generation

By default, for GUID key properties, a GUID is generated client-side by the EF provider and sent to the database. From version 9.0 and onwards, these GUIDs are sequential (version 7), which are more optimized for database indexes (before version 9.0, these GUIDs were random).

To have the provider generate GUIDs client-side for non-key properties, configure them as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Blog>()
        .Property(b => b.SomeGuidProperty)
        .HasValueGenerator<NpgsqlSequentialGuidValueGenerator>();
}

If you prefer to generate values in the database instead, you can do so by specifying HasDefaultValueSql on your property, and call the function to generate the value in the SQL expression. Which function to use depends on your PostgreSQL version:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Blog>()
        .Property(e => e.SomeGuidProperty)
        .HasDefaultValueSql("gen_random_uuid()");
}

See the PostgreSQL docs on UUID for more details.

Note

Generating Guid values in the database causes an additional network roundtrip when a principal and a dependent are inserted in the same SaveChanges, as the principal's key needs to be fetched before inserting the dependent's.

Timestamp generation

In many scenarios, it's useful to have a column containing the timestamp when the row was originally created. To do this, add a DateTime property to your entity type (or Instant if using NodaTime) , and configure its default with HasDefaultValueSql as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Blog>()
        .Property(e => e.SomeDateTimeProperty)
        .HasDefaultValueSql("now()");
}

In other scenarios, a "last updated" is needed, which is automatically updated every time is modified. Unfortunately, while PostgreSQL supports generated columns, the use of functions such as now() isn't supported. It's still possible to use database trigger to set this up; triggers can be managed by adding raw SQL to your migrations, as follows:

CREATE FUNCTION "Blogs_Update_Timestamp_Function"() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
    NEW."Timestamp" := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER "UpdateTimestamp"
    BEFORE INSERT OR UPDATE
    ON "Blogs"
    FOR EACH ROW
    EXECUTE FUNCTION "Blogs_Update_Timestamp_Function"();

Computed Columns

Note

This feature works only on PostgreSQL 12 or above.

PostgreSQL 12 added support for stored generated columns, and Npgsql feature supports that feature as well:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
      .Property(p => p.DisplayName)
      .HasComputedColumnSql(@"""FirstName"" || ' ' || ""LastName""", stored: true);
}

This will cause the following migration SQL to be generated:

ALTER TABLE ""Person"" ADD ""DisplayName"" text GENERATED ALWAYS AS (""FirstName"" || ' ' || ""LastName"") STORED;

Note that this is a stored column - it is computed once when the row is updated, and takes space on disk. Virtual computed columns, which are computed on each select, are not yet supported by PostgreSQL.

Edit this page