does postgresql 10 have something similar to sql server's set identity_insert

[john snow]

to temporarily allow explicit values to be inserted into the identity
column of a table to facilitate the generation of test data from
application code?

using sql, i know it's possible to issue INSERTs with OVERRIDING SYSTEM
VALUE clause to fill identity columns with user-specified values. but i'm
using an ORM (object-relation mapper) Framework from Microsoft (Entity
Framework Core 2.1) and a C# test data generator library, and i'm
generating "data aggregates" that have foreign key relationships so it
would be a great convenience if I could save test data with known primary
and foreign key values to the database as this would make it easier for me
to make assertions about my data.

ideally, i'm looking for something like this:

myDbContext.Database.ExecuteSqlCommand("..."); //ask postgresql to allow
explicit id values for inserts from here on
var testData = CreateTestData();
myDbContext.MyEntity.AddRange(testData);
myDbContext.SaveChanges();


thanks in advance for any tips or help!

[Laurenz Albe]

to temporarily allow explicit values to be inserted into the identity column of a table
to facilitate the generation of test data from application code?
using sql, i know it's possible to issue INSERTs with OVERRIDING SYSTEM VALUE clause to fill
identity columns with user-specified values. but i'm using an ORM (object-relation mapper)
Framework from Microsoft (Entity Framework Core 2.1) and a C# test data generator library,
and i'm generating "data aggregates" that have foreign key relationships so it would be a
great convenience if I could save test data with known primary and foreign key values to
the database as this would make it easier for me to make assertions about my data.
myDbContext.Database.ExecuteSqlCommand("..."); //ask postgresql to allow explicit id values
for inserts from here on
var testData = CreateTestData();
myDbContext.MyEntity.AddRange(testData);
myDbContext.SaveChanges();

You could create the identity column as

GENERATED BY DEFAULT AS IDENTITY

Then you simply insert DEFAULT for this column when you
want the generated value (or you don't specify the column
in the INSERT statement).

Yours,
Laurenz Albe

[Thomas Kellerer]

Post by john snow
to temporarily allow explicit values to be inserted into the identity
column of a table to facilitate the generation of test data from
application code?
using sql, i know it's possible to issue INSERTs with OVERRIDING
SYSTEM VALUE clause to fill identity columns with user-specified
values. but i'm using an ORM (object-relation mapper) Framework from
Microsoft (Entity Framework Core 2.1) and a C# test data generator
library, and i'm generating "data aggregates" that have foreign key
relationships so it would be a great convenience if I could save test
data with known primary and foreign key values to the database as
this would make it easier for me to make assertions about my data.
myDbContext.Database.ExecuteSqlCommand("...");  //ask postgresql to allow explicit id values for inserts from here on
var testData = CreateTestData();
myDbContext.MyEntity.AddRange(testData);
myDbContext.SaveChanges();

If you use a serial or identity column you can just insert your rows.
There is no need to "turn on" (or off) anything.

But you _have_ to synchronize the underlying sequence after you do that:

select setval(pg_get_serial_sequence('the_table', 'id'), (select max(id) from the_table));

Thomas