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 reying 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 statments ?
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 *;
| id | id2 |
|---|---|
| 1 | 1 |
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 *;
| id | id2 |
|---|---|
| 2 | 2 |