Auto Generate Unique Number for (Non ID Column) in PostgreSQL Table

Question

The PostgreSQL database we have is common multi tenant database.

Question is, need to auto generate a unique number in "customerNumber" column which needs to be in sequential order.

The trick here is, the sequence needs to be unique for each "hotelLocation".

For "hotelLocation"= 1, If we have numbers: 1,2,3 for "customerNumber"
For "hotelLocation"= 2, We need have numbers: 1,2,3 for "customerNumber"

Following is the sample layout for table,

@Entity
public class CustomerInfo {

  @Id
  @GeneratedValue(...)
  private Long idNumber;

  String hotelLocation;

  /** Looking for option where, this number needs to 
      auto generated on SAVE, and need to be in separate sequence 
      for each hotelLocation **/
  private Long customerNumber;

}

So finally here's how output will look like,

+----------+---------------+----------------+
| idNumber | hotelLocation | customerNumber |
+----------+---------------+----------------+
|        1 |             1 |              1 |
|        2 |             1 |              2 |
|        3 |             2 |              1 |
|        4 |             1 |              3 |
|        5 |             2 |              2 |
+----------+---------------+----------------+

I am ok with generating unique number both via Hibernate based or via Triggers also.

Searching across, i got following,

Hibernate JPA Sequence (non-Id)

But this one would keep generating in sequence without having separate sequence for each "hotelLocation"

Any solution to this will be very helpful. I am sure there are lot of people with multi tenant database looking for similar solution.

Thanks

score 1 · Accepted Answer · answered Oct 23 '17 at 20:21

1

You can do this easly with postgresql window function row_number().

Don't know your database but it should be something like this:

SELECT  idNumber, hotelLocation,
row_number() OVER (PARTITION BY hotelLocation ORDER BY idNumber) AS
customerNumber FROM table

Check more on window functions here: Understanding Window Functions

answered Oct 23 '17 at 20:21

Dan

1,771
1
11
19

This worked. Exactly what i was looking for. Thanks again for your help. – Richard Oct 24 '17 at 07:08
1

perhaps I am missing something - but in a heavily loaded system, where there may be hundreds or more records created per second could this not lead to two processes getting the same number if they make this query at the same time ? – jmls Mar 27 '18 at 07:37
The question never stated that it has changes per seconds. Anyhow, with changes per seconds every query could have different results in two separated scopes. But there won't be duplicate values on any result because window functions work over the generated data on the working scope. In other words, **you will never have duplicate values with `row_number()` for any partition**. – Dan Mar 27 '18 at 21:12

Auto Generate Unique Number for (Non ID Column) in PostgreSQL Table

1 Answers1