I'm working with a Ruby on Rails project that includes a table with a serial column:

create_table "fast_answer_templates", id: :serial, force: :cascade do |t|
  t.integer "company_id"
  t.string "title"
  t.text "body"
  t.datetime "created_at", precision: nil, null: false
  t.datetime "updated_at", precision: nil, null: false
  t.serial "position", null: false
  t.index ["company_id"], name: "fast_answer_templates_company_id_idx"
  t.index ["title"], name: "index_fast_answer_templates_on_title_trigram", opclass: :gin_trgm_ops, using: :gin
end

I'm trying to understand how the serial column handles value collisions when sorting. Does it rely on some kind of secondary sorting factor (like created_at or id)?

Additionally, if this behavior is configurable, where in the project should I look for such settings?

I couldn’t find clear information online about how sorting by a serial column behaves in such cases. Any insights would be greatly appreciated!

I'm working with a Ruby on Rails project that includes a table with a serial column:

create_table "fast_answer_templates", id: :serial, force: :cascade do |t|
  t.integer "company_id"
  t.string "title"
  t.text "body"
  t.datetime "created_at", precision: nil, null: false
  t.datetime "updated_at", precision: nil, null: false
  t.serial "position", null: false
  t.index ["company_id"], name: "fast_answer_templates_company_id_idx"
  t.index ["title"], name: "index_fast_answer_templates_on_title_trigram", opclass: :gin_trgm_ops, using: :gin
end

I'm trying to understand how the serial column handles value collisions when sorting. Does it rely on some kind of secondary sorting factor (like created_at or id)?

Additionally, if this behavior is configurable, where in the project should I look for such settings?

I couldn’t find clear information online about how sorting by a serial column behaves in such cases. Any insights would be greatly appreciated!

• ruby-on-rails
• ruby
• postgresql

• Postgres serial type is an auto incrementing integer sequence from 1 to 2147483647. From the docs: "The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns". Sorting would work just like an Integer because it is an integer. You should not have collisions because it is an auto incrementing sequence and thus it will just pull the next number from the sequence when creating the row... – engineersmnky Commented Mar 17 at 14:53
• ...I say should not because it is possible to supply a value for this column, in which case the sequence will not be used or incremented. – engineersmnky Commented Mar 17 at 14:53
• Why not just try it out? Run FastAnswerTemplate.order(:position).explain to see the query plan. – max Commented Mar 19 at 13:50
• Btw, most of the times, sorting by any column with integer value does not actually handle value collision. Since x == x in math, if there is no other particular ordering, the two items having same integer value are considered equal and most engines orders them by the order of physical addition to the table (it is not related to created_at at all so it may or may not follow that order). – Gabor Garami Commented Mar 24 at 6:01
• Thanks everyone for your answers! Helped me figure out the problem. – Иван Сизых Commented Mar 25 at 7:50

1 Answer 1

serial is just integer with autoincrement. PostgreSQL uses sequence for that

By default first value is 1, last value is 2147483647, increment step is +1, no cycle

if this behavior is configurable, where in the project should I look for such settings

Sequence is configurable. Best place to change such config is database migration

I'm trying to understand how the serial column handles value collisions when sorting

Sorting by your position is just sorting by some integer column (ORDER BY)

So it depends. If just ORDER BY position, PostgreSQL will not sort by any additional criteria. If two records have same value (if assigned manually, not by sequence), we can say that there is no guarantee what record will be first of them

But if not assign position manually and if sequence is not cycling, you will not have two records with same position value

If you need to apply additional criteria for sorting, you should apply them explicitly

Let's say

FastAnswerTemplate
  .order(:position) # sort firstly by position
  .order(:id) # and secondly by id 

BTW if you have a lot of records, you need index for sorting queries