CODEWITHSUNDEEP
databasepostgresqlhibernatejpasequence
Richard
Richard

Reputation: 23

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

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".

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

Upvotes: 1

Views: 1689

Answers (1)

Dan
Dan

Reputation: 1881

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

Upvotes: 1

Related Questions