dK3 dK3 - 4 years ago 281
SQL Question

Custom SERIAL / autoincrement in postgresql

I'm trying to make a blog system of sort and I ran into a slight problem.

Simply put, there's 3 columns in my 'article' table: id(SERIAL), category(VARCHAR FK), category_id(INT).

id column is obviously the PK and it is used as a global identifier for all articles.

category column is well.. is category.

category_id is used as a UNIQUE id within a category so currently there is a UNIQUE(category, category_id) constraint in place.

However, what I also want is for category_id column to auto increment.

I want it so that every time I execute a query like

INSERT INTO article(category) VALUES ('stackoverflow');


I want the category_id column to be automatically be filled according to the latest category_id of the "stackoverflow" category.

Achieving this in my logic code is quite easy. I just select latest num and insert +1 of that but that involves two separate queries.
I am looking for a SQL solution that can do all this in one query.

Thanks :)

Answer Source

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 trigger
LANGUAGE plpgsql
AS
$$
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_id
stackexchange   1
stackexchange   2
stackexchange   3
stackoverflow   1

Result after final inserts:

category    category_id
nonexisting     1
stackexchange   1
stackexchange   2
stackexchange   3
stackexchange   4
stackoverflow   1
stackoverflow   2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download