Default incrementing value in postgresql table

Question

I have carried out a lot of searching but unable to locate an answer to my query. What I am asking may not be possible however:

I have a series of tables but to simplify let us assume that I have the following

Article articleId SERIAL

ArticlePageNumber articleId /references Article.articleId/ pageNumber Int

This is fairly self explanatory however data thatwould be acceptable would be

 Article
 -------
 articleiId
 1
 2

 ArticlePageNumber
 -----------------
 articleiId   pageNumber
 1            1
 1            2
 1            3
 1            4
 1            5
 1            6
 2            1
 2            3
 2            5
 2            2
 2            4

An unacceptable table would be ArticlePageNumber

 ArticlePageNumber
 -----------------
 articleiId   pageNumber
 1            1
 1            1
 1            2
 1            3
 1            4
 1            5
 2            1
 2            3
 2            5
 2            4

I would like the default value for ArticlePageNumber.pageNumber to increment similar to the way that serial does however the next value would be dependant on the previous value (or 1 if none) for entries with that particular articleId.

I would prefer to do this as constraints and checks within SQL rather that code however I am guessing it might not be possible.

Possibly similar to http://stackoverflow.com/questions/12746106/how-to-create-multiple-sequences-in-one-table/12746929 — jcern, Oct 10 '12 at 12:12
@jcern on reviewing the other question you are quite right there is quite a bit of similarity and has a couple of suggestions that may resolve my issue. Thank you. — Peter, Oct 10 '12 at 12:20

score 2 · Accepted Answer · answered Oct 10 '12 at 13:47

The behavior that you need is not impossible, but is tricky:

Easy to accomplish - a page number sequence that does not know what article it is attached to

First, create a sequence for page numbers as follows:

CREATE SEQUENCE page_number_sequence;

If you need to tune the allocation size, start etc look here

Then: when defining the "article_page_number" table, define the pageNumber field as follows:

# omitted boilerplate
pageNumber integer not null default nextval('page_number_sequence')

That will automatically fill in for you the page numbers in an incrementing fashion. If you do not want "gappy" sequence numbers, set the increment to 1 ( default ) and the cache to 1 ( default ) when setting up the sequence.

Trickier - an autoincrementing page number sequence that is aware of the article

You could use a per row trigger that listens for saves on the article page number table and:

Checks if a sequence specific to that article id exists and creates it does not exist
Assigns a page number from this sequence if one does not exist

The code below has only undergone light testing, but it should illustrate the thinking...

CREATE TABLE IF NOT EXISTS article (
  articleId SERIAL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS article_page_number (
  articleId INTEGER NOT NULL,
  pageNumber INTEGER NOT NULL,
  CONSTRAINT article_fk FOREIGN KEY (articleId)
      REFERENCES article (articleId)
);

CREATE OR REPLACE FUNCTION page_number_trg()
  RETURNS trigger AS
$BODY$ DECLARE 
  seqname VARCHAR(60):= NULL;
BEGIN
  IF (TG_OP = 'INSERT') THEN
    BEGIN
      seqname:= 'pageno_seq_'||NEW.articleId;
      IF NOT EXISTS (SELECT 0 FROM pg_class where relname = seqname) THEN
        EXECUTE 'CREATE SEQUENCE '||seqname;
      END IF;
      IF NEW.pageNumber IS NULL THEN
        NEW.pageNumber := nextval(seqname);
      END IF;
    END;
  END IF;
  IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
END
; $BODY$
LANGUAGE plpgsql;

CREATE TRIGGER page_number_trg
BEFORE INSERT ON article_page_number
FOR EACH ROW
EXECUTE PROCEDURE page_number_trg();

Default incrementing value in postgresql table

1 Answers1