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

Prior to version 3.0, the Npgsql provider generates "serial" columns for ID columns; starting with version 3.0, it generates "identity by default" instead. In other words, when ValueGeneratedOnAdd is specified on a short, int or long property, the Npgsql provider will automatically map it to a serial or identity column. Note that EF Core will automatically recognize key properties by convention (e.g. a property called Id in your entity) and will implicitly set them to ValueGeneratedOnAdd; a standard model with ID columns should automatically get created with the appropriate column type.

Caution

Since the default strategy has changed, if you have an existing database with migrations, the the first migration created after upgrading to version 3.0 will alter your tables and convert serial columns to identity ones. This is a sensitive, one-time migration operation that should be done with care, and carefully tested before deployment to production. Changing a value generation strategy is a significant change to an existing database.

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.UseSerialColumns();

Note that if you are using PostgreSQL 9.6 or older, identity columns will not work. It is recommended to place the provider in compatibility mode with your specific version - this will also affect the default value generation strategy:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseNpgsql("...", o => o.SetPostgresVersion(9, 6));

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, if you specify ValueGeneratedOnAdd on a Guid property, a random Guid value will be generated client-side and sent to the database.

If you prefer to generate values in the database instead, you can do so by specifying HasDefaultValueSql on your property. Note that PostgreSQL doesn't include any Guid/UUID generation functions, you must add an extension such as uuid-ossp or pgcrypto. This can be done by placing the following code in your model's OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    => modelBuilder.HasPostgresExtension("uuid-ossp")
                   .Entity<Blog>()
                   .Property(e => e.SomeGuidProperty)
                   .HasDefaultValueSql("uuid_generate_v4()");

See the PostgreSQL docs on UUID for more details.

Computed Columns

Note

This feature works only on PostgreSQL 12 or above, and was introduced in version 3.0.0 of the provider.

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

Version 5.0

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

Version 3.x

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

---

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.