Custom auto-increment field in postgresql (Invoice/Order No.)

Question

The baseline requirement is to create an order number in the format:

(M)M-SSS

Where MM represents the current month and SSSS represents the order sequence for that month. For example 1-002 would represent the second order submitted in January.

Using a TRIGGER I'd like the auto-increment and insert to work transparently.

Unfortunately, it has been a long time since I have touched a stored procedure and this is my first foray into postgresql. Any help pointing in the right direction would be greatly appreciated.

Update: Here's the final implementation using @peterm's code

-- The trigger
CREATE TRIGGER add_order_number 
   BEFORE INSERT ON orders FOR EACH ROW
   EXECUTE PROCEDURE order_number_update();

-- The trigger function
CREATE FUNCTION order_number_update() RETURNS TRIGGER AS $$
DECLARE
    next_order TEXT;
BEGIN
    -- get the next order number
    SELECT INTO next_order CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
       '-', 
       LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_number), 3) AS INT), 0) + 1 AS VARCHAR(3)), 3, '0'))
    FROM orders
    WHERE CAST(LEFT(order_number, STRPOS(order_number, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE);

    -- update the field
    NEW.order_number = next_order;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Note: By adding the update as an BEFORE INSERT trigger it functions in a completely transparent and predictable manner, much like a normal SERIAL or BIGSERIAL field would.

peterm · Accepted Answer · 2013-05-17T03:44:56.213

2

Are you looking for something like this?

-- Next No calculated for current month
SELECT CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
       '-', 
       LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_no), 4) AS INT), 0) + 1 AS VARCHAR(4)), 4, '0')) next_no
  FROM orders
 WHERE CAST(LEFT(order_no, STRPOS(order_no, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE)
;

Output:

| NEXT_NO |
-----------
|  5-0001 |

Assuming that there is a record with order_no = '1-0001' then

-- Next No for January
SELECT CONCAT(CAST(DATE_PART('MONTH', DATE '2013-01-01') AS VARCHAR(2)),
       '-', 
       LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_no), 4) AS INT), 0) + 1 AS VARCHAR(4)), 4, '0')) next_no
  FROM orders
 WHERE CAST(LEFT(order_no, STRPOS(order_no, '-') - 1) AS INT) = DATE_PART('MONTH', DATE '2013-01-01')
;

Output:

| NEXT_NO |
-----------
|  1-0002 |

SQLFiddle

edited May 17 '13 at 03:44

answered May 17 '13 at 01:01

peterm

91,357
15
148
157

@EvanPlaice See updated answer. First version of the query was built on assumption that month portion is fixed two char string MM. – peterm May 17 '13 at 01:56
@EvanPlaice That's kind of silly. Here is [sqlfiddle](http://sqlfiddle.com/#!1/798aa/2) that uses field name `order_number`. That means that you have some typo in your code. – peterm May 17 '13 at 03:43
I'm a dumbass, I'm using pgadmin to check the code and didn't drag the UI output to the right enough to see the full result. – Evan Plaice May 17 '13 at 03:54
Either way, interesting chunk of code. Using coalesce to change the null result of the query to a default value is pretty neat. – Evan Plaice May 17 '13 at 03:57
one last thing, are all of the explicit casts necessary; or is it just good practice. I'm using psql 9.2 and it seems to handle the implicit casting without any issues. – Evan Plaice May 17 '13 at 04:01
@EvanPlaice If psql 9.2 handles it well then you can safely ditch them. – peterm May 17 '13 at 04:05

Custom auto-increment field in postgresql (Invoice/Order No.)

1 Answers1

Linked