Rails & Postgresql create a field that auto increments from 1

Question

I'm looking to create a field called id_visual in my table orders which starts at 1 and auto increments from there. I could create a method in my model to do it but I thought there must be a better more foolproof way. Any ideas?

Chris Woolum · Accepted Answer · 2013-02-14T02:01:21.567

0

From what I can tell, you want a secondary id based on the primary id? The identity key can only be table based and can not be dependent on another key. You will have to do this in code and then save it to a new field on before_create. The easiest way to do this is for each order that you want to id, get the count of all orders less than or equal to the one you are working with based on whatever the primary key is. Its a simple one query calculation.

edited Feb 14 '13 at 02:01

answered Feb 12 '13 at 03:46

Chris Woolum

2,854
20
20

One query that is subject to race conditions and your count will fail as soon as a row gets deleted. – mu is too short Feb 12 '13 at 04:24
Actually I ended up doing this and storing a `orders_count` field in the `accounts` table so it doesn't matter if an order is deleted. That part is very important. – bcackerman Feb 19 '13 at 05:09

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

This is something your database should be providing at some level, either in a transaction or with some other locking.

Take a look at this question for some ways to get postgres configured to auto-increment a column:

PostgreSQL Autoincrement

edited May 23 '17 at 12:12

Community

1
1

answered Feb 12 '13 at 05:01

John Naegle

8,077
3
38
47

Rails & Postgresql create a field that auto increments from 1

2 Answers2