Select max + 1 else 1

Question

Below is my table book.

        ID  BookName StoreId  Sequence Price
        1    ABC       1         1      450
        2    DEF       1         2      450
        3    GHF       2         1      300

So above table is book table, Each store will have multiple books. I am writing a function which will insert book record in this table. While inserting the new record our requirement is new book sequence should Max(Sequence) + 1 of corresponding store. I have written below query for that.

    INSERT INTO Book(BookName,StoreId,Sequence,Price) Values
       (@name,@sid,(SELECT MAX(Sequence) + 1 from book where StoreId=@StoreId),@price)

This query is working fine when we already have inserted storeId present in table but as soon as i give storeId not present table Sequence will come as NULL because we don't have any existing sequence for that store and Max(sequence)+1 will give Null. What is best way to resolve this?

On a side note, you have to `LOCK TABLE Book IN EXCLUSIVE MODE` before you do this, otherwise you can insert duplicate IDs from concurrent transactions. I strongly suggest using a counter table based approach instead; search for "postgresql gapless sequence" to learn more. — Craig Ringer, Oct 24 '15 at 08:33

score 0 · Answer 1 · edited Oct 24 '15 at 10:12

0

Try It :

INSERT INTO Book
       (BookName,StoreId,Sequence,Price)
Values
       (@name,@sid,(SELECT MAX(Sequence) + 1 from book),@price)

edited Oct 24 '15 at 10:12

Craig Ringer

307,061
76
688
778

answered Oct 24 '15 at 08:36

Chilli

123
3

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

0

You will have to use some sort of isnull function or coalesce.

refer this.

What is the PostgreSQL equivalent for ISNULL()

INSERT INTO Book(BookName,StoreId,Sequence,Price) Values
       (@name,@sid,coalesce((SELECT MAX(Sequence) + 1 from book where StoreId=@StoreId),1),@price)

edited May 23 '17 at 10:27

Community

1
1

answered Oct 24 '15 at 08:53

Abdul Rehman Sayed

6,532
7
45
74

Select max + 1 else 1

2 Answers2