Custom SERIAL / autoincrement per group of values

This has been asked many times and the general idea is bound to fail in a multi-user environment - and a blog system sounds like exactly such a case.

So the best answer is: Don't. Consider a different approach.

Drop the column category_id completely from your table - it does not store any information the other two columns (id, category) wouldn't store already.

Your id is a serial column and already auto-increments in a reliable fashion.

• Auto increment SQL function

If you need some kind of category_id without gaps per category, generate it on the fly with row_number():

• Serial numbers per group of rows for compound key

Concept

There are at least several ways to approach this. First one that comes to my mind:

Assign a value for category_id column inside a trigger executed for each row, by overwriting the input value from INSERT statement.

Action

Here's the SQL Fiddle to see the code in action

For a simple test, I'm creating article table holding categories and their id's that should be unique for each category. I have omitted constraint creation - that's not relevant to present the point.

create table article ( id serial, category varchar, category_id int )

Inserting some values for two distinct categories using generate_series() function to have an auto-increment already in place.

insert into article(category, category_id)  select 'stackoverflow', i from generate_series(1,1) i  union all  select 'stackexchange', i from generate_series(1,3) i

Creating a trigger function, that would select MAX(category_id) and increment its value by 1 for a category we're inserting a row with and then overwrite the value right before moving on with the actual INSERT to table (BEFORE INSERT trigger takes care of that).

CREATE OR REPLACE FUNCTION category_increment()RETURNS triggerLANGUAGE plpgsqlAS$$DECLARE  v_category_inc int := 0;BEGIN  SELECT MAX(category_id) + 1 INTO v_category_inc FROM article WHERE category = NEW.category;  IF v_category_inc is null THEN    NEW.category_id := 1;  ELSE    NEW.category_id := v_category_inc;  END IF;RETURN NEW;END;$$ 

Using the function as a trigger.

CREATE TRIGGER trg_category_increment   BEFORE INSERT ON article   FOR EACH ROW EXECUTE PROCEDURE category_increment()

Inserting some more values (post trigger appliance) for already existing categories and non-existing ones.

INSERT INTO article(category) VALUES   ('stackoverflow'),  ('stackexchange'),  ('nonexisting');

Query used to select data:

select category, category_id From article order by 1,2

Result for initial inserts:

category    category_idstackexchange   1stackexchange   2stackexchange   3stackoverflow   1

Result after final inserts:

category    category_idnonexisting     1stackexchange   1stackexchange   2stackexchange   3stackexchange   4stackoverflow   1stackoverflow   2

Postgresql uses sequences to achieve this; it's a different approach from what you are used to in MySQL. Take a look at http://www.postgresql.org/docs/current/static/sql-createsequence.html for complete reference.

Basically you create a sequence (a database object) by:

CREATE SEQUENCE serials;

And then when you want to add to your table you will have:

INSERT INTO mytable (name, id) VALUES ('The Name', NEXTVAL('serials')