Fails to insert entity with auto-increment in Postgres

[MargaretKrutikova]

I tried inserting an entity into a table with auto-increment primary key (serial) in Postgres and followed the example from the docs where you just set 0 on the id-field, but it ignores the auto-increment sequence and just inserts the value as it is, which of course fails the second time when there already exists a row with id 0.

Is there something I am missing from the example? My query looks like this:

// auto-generated types
module my_schema =
      [<CLIMutable>]
      type my_table =
          { id: int
            name: string
        }

// code inserting an entity
let myRecord: my_schema.my_table = { id = 0; name = "Test" } 
use ctx = openContext dbConnection
    insert {
        for e in myTable do
        entity myRecord
    }
    |> ctx.InsertAsync

The sql schema looks like this:

create table my_table (
    id serial primary key,
    name text not null
);

Thanks!

[JordanMarr]

You have two options when using identity/sequence columns -- you must use either getId or excludeColumn in your insert; otherwise, id will be included in the insert columns.

1) If you want to return the next id sequence, use getId.

use ctx = openContext dbConnection
let! nextId = 
    insert {
        for e in myTable do
        entity { id = 0; name = "Test" }
        getId e.id
    }
    |> ctx.InsertAsync

2) If you don't want to auto query and return the next id sequence, then you must ignore it with excludeColumn:

use ctx = openContext dbConnection
let! _ =
    insert {
        for e in myTable do
        entity myRecord
        excludeColumn e.id
    }
    |> ctx.InsertAsync

[MargaretKrutikova]

Great, getId worked! Would you be able to update the readme so that it says that it is required to use getId or otherwise it won't work? It doesn't seem to be mandatory if you just follow the docs 😄 Thanks a lot for your help!

[JordanMarr]

I have added some description to the insert builder docs that should help to clarify this for others. Thank you for your feedback. 😊

[MargaretKrutikova]

Thank you for the lightning speed responses! ❤️