0

I have this data model in ORM.

class Region(db.Model):
    __tablename__ = 'region_master'
    region_master_id = db.Column(
        db.Integer,
        primary_key=True,
        autoincrement=True
      )
    region_name = db.Column(db.Text)

Now suppose if I insert data in region_master by orm

db.Region(region_name="APAC") --> database allocates region_master_id = 1 by using region_master_region_master_id_seq (this is created by default by postgres as I am using autoincrement=True)

but now suppose I insert data using sql query then it skips the sequence(region_master_region_master_id_seq) and inserts data by given id.

INSERT INTO public.region_master (region_master_id,  region_name)  VALUES (2, N'Europe');
INSERT INTO public.region_master (region_master_id,  region_name)  VALUES (3, N'USA');

Now sequence returns 1 as last_value:

select last_value from region_master_seq

Now again if I insert using ORM I get unique key constraints error.

dm.Region(region_name="EMEA")

Here DB tries to allocate region_master_id=2 and it fails with unique key constraints.

So is there any way that ORM/database will take 4 as next id.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Girish Gupta
  • 1,241
  • 13
  • 27
  • 1
    You need to tell your obfuscation layer to **not** provide a value for `region_master_id`, so it should do `INSERT INTO region_master (region_name) VALUES (N'Europe');` –  Apr 26 '18 at 14:04
  • in this case it will work . But is there any hack if I specify the primary key as well in query explicitly? – Girish Gupta Apr 26 '18 at 14:08
  • Related: https://stackoverflow.com/questions/896274/select-multiple-ids-from-a-postgresql-sequence. But in the end, don't specify it explicitly, if it is a generated primary key (sequence). You might be able to pull off a batch insert with predefined keys by first setting the sequence's nextvalue to point past your batch, but what if some other process manages to reserve one of the ids in your batch just before you update the nextvalue? In the end this is really not something an ORM even should be concerned about. – Ilja Everilä Apr 26 '18 at 14:15

0 Answers0