Rails + postgres autoincrement not updated on explicit id inserts

Question

I'm with the same problem of this post. But I'm using Rails and don't know how to do this workaround in activerecord.

I'm inserting some data using seeds.rb:

device_platforms = DevicePlatform.create([{id: 1, name: 'Android'}, {id: 2, name: 'IOS'}, {id: 3, name: 'Windows Phone'}])

When I insert another DevicePlatform, I get this exception:

Failure/Error: @device_platform = FactoryGirl.create(:device_platform)
 ActiveRecord::RecordNotUnique:
   PG::UniqueViolation: ERRO:  duplicar valor da chave viola a restrição de unicidade "device_platforms_pkey"                                                                 
   DETAIL:  Chave (id)=(2) já existe.
   : INSERT INTO "device_platforms" ("created_at", "name", "updated_at") VALUES ($1, $2, $3) RETURNING "id"

The message is in portuguese, it says that a row with that ID already exists.

Thanks.

Don't include ids in your seeds. If you must include ids in your seeds then you'll have to connection.execute the SQL in the other question after adding your seeds. — mu is too short
– mu is too short, Commented Sep 12, 2014 at 17:40
Thanks for including the exact text of the error. I'd +100 if I could. — Craig Ringer
– Craig Ringer, Commented Sep 12, 2014 at 17:53
@muistooshort You think include ids on seeds file is a bad practice? — William Weckl
– William Weckl, Commented Sep 12, 2014 at 18:03
Depending on ids being anything in particular is a bad practice. You can do it if you need to but you will need to run some SQL to patch up the sequences that are used to supply id values. — mu is too short
– mu is too short, Commented Sep 12, 2014 at 18:10

Oz Ben-David · Accepted Answer · 2018-07-26 10:53:19Z

6

I believe that although this is a bad habit, many times it is the only resort

You can define this method :

def fix_self_increment(model)
  ActiveRecord::Base.connection.execute(
    "BEGIN; " \
    "LOCK TABLE #{model.table_name} IN EXCLUSIVE MODE; " \
    "SELECT setval('#{model.table_name}_id_seq', COALESCE((SELECT MAX(id)+1 FROM #{model.table_name}), 1), false); " \
    "COMMIT;"
  )
end

and then call

fix_self_increment(DevicePlatform)

Enjoy

answered Jul 26, 2018 at 10:53

Oz Ben-David

1,6671 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MarkJL Over a year ago

I'd put this code in every migration of data that does this kind of insert rather than having a method that does it. It quickly teaches you it's a bad idea to specify id. This is the correct answer btw.

William Weckl · Accepted Answer · 2014-11-05 13:06:14Z

-3

According to the comments, using id in seed is a bad practice. I'm avoiding bad practices and that's enough for me. Changed my code to avoiding this.

answered Nov 5, 2014 at 13:06

William Weckl

2,4654 gold badges29 silver badges44 bronze badges

2 Comments

w ian douglas Over a year ago

There are still valid use cases where this can happen (for example, reading in a CSV file in your rake task), so it'd be nice to see a valid answer for how to solve this.

MarkJL Over a year ago

Please accept the answer above as it does answer the question you have asked, even though it is a bad idea. Sometimes doing bad things is necessary.

Collectives™ on Stack Overflow

Rails + postgres autoincrement not updated on explicit id inserts

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related