PostgresException: 23505: duplicate key value violates unique constraint "PK_country"

Question

I am using EF Core 2.0 and Postgres 9.6. Whenever I insert data I get the error

PostgresException: 23505: duplicate key value violates unique constraint "PK_country"

By tracing it looks like EF doesnt generate AutoIncrement column

My Model

public partial class Country
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CountryId { get; set; }
        [Display(Name="Country Name")]
        public string CountryName { get; set; }
    }

My Controller

if (ModelState.IsValid)
            {
                _context.Add(country);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }

Exception

> PostgresException: 23505: duplicate key value violates unique
> constraint "PK_country"
> 
>     Npgsql.NpgsqlConnector+<DoReadMessage>d__148.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     System.Runtime.CompilerServices.TaskAwaiter.GetResult()
>     System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult()
>     Npgsql.NpgsqlConnector+<ReadMessage>d__147.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     Npgsql.NpgsqlConnector+<ReadMessage>d__147.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     System.Runtime.CompilerServices.TaskAwaiter.GetResult()
>     System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult()
>     Npgsql.NpgsqlDataReader+<NextResult>d__32.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     Npgsql.NpgsqlDataReader+<<NextResultAsync>b__31_0>d.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     Npgsql.NpgsqlCommand+<Execute>d__71.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     System.Runtime.CompilerServices.TaskAwaiter.GetResult()
>     System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult()
>     Npgsql.NpgsqlCommand+<ExecuteDbDataReader>d__92.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     System.Runtime.CompilerServices.TaskAwaiter.GetResult()
>     System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult()
>     Npgsql.NpgsqlCommand+<>c__DisplayClass90_0+<<ExecuteDbDataReaderAsync>b__0>d.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     System.Runtime.CompilerServices.TaskAwaiter.GetResult()
>     Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand+<ExecuteAsync>d__17.MoveNext()
>     System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
>     System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
> task)
>     System.Runtime.CompilerServices.TaskAwaiter.GetResult()
>     Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch+<ExecuteAsync>d__32.MoveNext()

What could be the problem?

Answer 1

I found the solution. The issue was I was seed the database with the script from the sql file.

context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir + "\\data.sql"));

The insert contains

ALTER TABLE country DISABLE TRIGGER ALL;
INSERT INTO country .......

So the sequence current value was not updating. Current value remained 1.So when I inserted with application. Autoincrment become 1 hence PostgresException: 23505: duplicate key value violates unique constraint "PK_country".

Resolution

After ALTER TABLE country ENABLE TRIGGER ALL; add SELECT pg_catalog.setval(pg_get_serial_sequence('country', 'country_id'), MAX(country_id)) FROM country;

Now it application run successifully.

Hope help someone else.