I have a table that was created by code first and I need to insert data into it through Postgres tools. My problem is that EF is the one that is creating the unique key because of code first.

So how can I insert data in the table using postgres tools and not relying on EF? I do see that Entity Framework is using a sequence that is defined in the database. Is there a way I can use that in my insert statements?

I have a table that was created by code first and I need to insert data into it through Postgres tools. My problem is that EF is the one that is creating the unique key because of code first.

So how can I insert data in the table using postgres tools and not relying on EF? I do see that Entity Framework is using a sequence that is defined in the database. Is there a way I can use that in my insert statements?

• postgresql
• entity-framework

• Assuming you mean it has set up autoincrement then do not include the field in the INSERT query. The database will fill in that value. – Adrian Klaver Commented Jan 30 at 22:50
• this is false, the database requires a value – Micah Armantrout Commented Jan 30 at 23:37
• @MicahArmantrout That's not false, that is what I'm showing below. It's just that the column needs to be skipped both in the target column list as well as the values list of the query. You might have observed an error if you tried to only skip it in one of these, or you had no target column list specified at all, which makes it expect all fields to be present. – Zegarek Commented Jan 30 at 23:45
• It is not false if you are indeed using an autoincrement field, hence the auto in the name. – Adrian Klaver Commented Jan 31 at 1:17

1 Answer 1

You can inspect your entire db schema that EF generated with pg_dump -s, or ask psql to describe individual objects with \d meta-command.
_{demo at db<>fiddle}

testdb=# create table test(  id int primary key generated by default as identity
                           , id2 serial);
CREATE TABLE
testdb=# \d test
                             Table "public.test"
 Column |  Type   | Collation | Nullable |              Default
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | generated always as identity
 id2    | integer |           | not null | nextval('test_id2_seq'::regclass)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

I'm mentioning these two tools because they're both bundled with a standard PostgreSQL build but others like pgAdmin, DBeaver or DataGrip, all offer similar features.

If all you need is to use the default without looking it up, insert..values accepts a default keyword in place of a value:

insert into test(id)values(default)returning *;

Or just skip the column: in the example above I only targeted id, so id2 used the default on its own. Note that I had to skip it both in the target list in the first parentheses, as well as in the values list in the second.

You can also look it up with pg_get_serial_sequence() as long as it's a serial or generated [always|by default] as identity column, or the sequence was attached to it via [create|alter] sequence..owned by:

insert into test(id,id2)
values(  nextval(pg_get_serial_sequence('test','id'))
       , nextval(pg_get_serial_sequence('test','id2')))
returning *;