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');
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
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