Autoincrement postgre DB id field in RAILS

Question

I am trying to make an autoincrement id field into a postgre DB in rails. I am trying to get the maximum id and then just add +1 and pass it down to create method. I am not quite sure what I am doing wrong, but if I have 4 projects and add the 5th one, that is getting id 5, but if I delete that one and try to add another one, that's getting id 6, not 5 again. Here is my code:

 # GET /admin/projects/new
  def new
    @project = Project.new
    @project.id = Project.maximum(:id).next.to_i

    respond_to do |format|
      format.html # new.html.erb
    end
  end

  # GET /admin/projects/1/edit
  def edit
    @project = Project.find(params[:id])
  end

  # POST /admin/projects
  def create
    @project = Project.create(params[:project])

    respond_to do |format|
      if @project.save
        format.html { redirect_to projects_path, notice: 'Project was successfully created.' }
      else
        format.html { render action: "new" }
      end
    end
  end

I tried to add @project.save to new, but as much as I understand, the .save should only be done in the create method. Any ideas will be kindly appreciated :D

score 1 · Accepted Answer · edited May 23 '17 at 10:30

1

The auto increment fields goes up acordingly to a sequence, if the value 5 has already been taken(even if the entre has been deleted after, but the 5 has been associated to a record) the sequence dont stop, it will give you the 6th and so on...

Thankfully you can reset the sequence by 2 methods.

1- Truncate the table, truncate also resets the sequences to 1.

2- Use the Alter sequence method, you have here the official documentation for this method and here another question regarding this same matter with an example to solve it.

wich is:

ALTER SEQUENCE ${table}_${column}_seq RESTART WITH 1453

Hope it helps.

UPDATE 1:

With the update info on the coments, the awnser changes.

it is needed to create a custom method that handles that.

Project model

before_create :check_max_value

def check_max_value
  self.project_id = Project.last.project_id + 1
end

you need to create a migration to add the "project_id" to the table, that field is the name of that "id you want".

You really shoudn't use the "ID" field created by default by rails.

edited May 23 '17 at 10:30

Community

1
1

answered Nov 11 '16 at 10:20

Riru Isla

105
7

How can I use the first method and truncate the table? Do I need to run a migration? – Vlad Balanescu Nov 11 '16 at 10:46
you should use one or another, but it is purely on the database, not rails interaction, go to the sql editor, like the workbench for mysql, and execute the commands, the rails migrations, don't do anything to the data, just the structure, so migrations wont help here (you can reset all the database by runing rake db:schema:load, but its overkill). – Riru Isla Nov 11 '16 at 11:01
I want to be able to reset that sequence every time a project has been erased, so that resets to the maximum id in the table. This is not just a 1 time operation I want to perform – Vlad Balanescu Nov 11 '16 at 11:04
oooooh, in that case, do not use the ID field, and create a custom method in your model, that see the max value of that field, and do a +1, you should not use the id field for that purpose. something like.... before_create :check_max_value def check_max_value self.autincrement=Model.max(autoincrement)+1 end – Riru Isla Nov 11 '16 at 11:06
I tried that and I get back an error saying `id` is not a method when I call it on self – Vlad Balanescu Nov 11 '16 at 11:08
Because you should not use the ID, you should create a new collum on the table to handle that purpose, i will edit the awnser wit the code – Riru Isla Nov 11 '16 at 11:10
With this one I am getting undefined method project_id – Vlad Balanescu Nov 11 '16 at 11:36
Got it working. I was accessing it before the `.create`. Thanks a lot! – Vlad Balanescu Nov 11 '16 at 12:21

score 1 · Answer 2 · answered Nov 11 '16 at 10:23

A few problems here,

First, is the :id field actually in the view in a hidden field? If not, it's not returned in the params.

Second, if you did explicity set :id to the value of a deleted record, PostgreSQL will complain about a duplicate key value regardless. The id is still being used, you just can't access the record.

Third, you should not be assigning a value to :id, ever. :id has only one purpose and that's to provide a unique key for the record in the database, it should not have any other ancillary meaning. It increments automatically, that's all part of how the PostgreSQL adapter works.

If you want some sort of sequential numbering that can be reset, use a different, new field.

Autoincrement postgre DB id field in RAILS

2 Answers2