PostgreSQL sequence based on another column

Question

Lets say I have a table as such:

Column   |     Type    |                        Notes
---------+------------ +----------------------------------------------------------
 id      | integer     | An ID that's FK to some other table
 seq     | integer     | Each ID gets its own seq number
 data    | text        | Just some text, totally irrelevant.

id + seq is a combined key.

What I'd like to see is:

ID  | SEQ   |                        DATA
----+------ +----------------------------------------------
 1  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 2  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.

As you can see, a combination of id and seq is unique.

I'm not sure how to set up my table (or insert statement?) to do this. I'd like to insert id and data, resulting in seq being a sub-sequence dependent on id.

Answer 1

No problem! We're going to make two tables, things and stuff. stuff will be the table you describe in your question, and things is the one it refers to:

CREATE TABLE things (
    id serial primary key,
    name text
);

CREATE TABLE stuff (
    id integer references things,
    seq integer NOT NULL,
    notes text,
    primary key (id, seq)
);

Then we'll set things up with a trigger that will create a new sequence every time a row is created:

CREATE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  execute format('create sequence thing_seq_%s', NEW.id);
  return NEW;
end
$$;

CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();

Now we'll end up with thing_seq_1, thing_seq_2, etc, etc...

Now another trigger on stuff so that it uses the right sequence each time:

CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval('thing_seq_' || NEW.id);
  RETURN NEW;
end
$$;

CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();

That'll ensure that when rows go into stuff, the id column is used to find the right sequence to call nextval on.

Here's a demonstration:

test=# insert into things (name) values ('Joe');
INSERT 0 1
test=# insert into things (name) values ('Bob');
INSERT 0 1
test=# select * from things;
 id | name
----+------
  1 | Joe
  2 | Bob
(2 rows)

test=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | stuff         | table    | jkominek
 public | thing_seq_1   | sequence | jkominek
 public | thing_seq_2   | sequence | jkominek
 public | things        | table    | jkominek
 public | things_id_seq | sequence | jkominek
(5 rows)

test=# insert into stuff (id, notes) values (1, 'Keychain');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Pet goat');
INSERT 0 1
test=# insert into stuff (id, notes) values (2, 'Family photo');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Redundant lawnmower');
INSERT 0 1
test=# select * from stuff;
 id | seq |        notes
----+-----+---------------------
  1 |   1 | Keychain
  1 |   2 | Pet goat
  2 |   1 | Family photo
  1 |   3 | Redundant lawnmower
(4 rows)

test=#

Answer 2

You could use a window function to assign your SEQ values, something like:

INSERT INTO YourTable
    (ID, SEQ, DATA)
    SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATA), DATA
        FROM YourSource

Answer 3

If seq reflects (or should reflect) the order in which the rows are inserted, I'd rather use a timestamp that gets populated automatically and generate the sequence number on the fly when selecting the rows using row_number():

create table some_table
( 
  id          integer   not null,
  inserted_at timestamp not null default current_timestamp,
  data text
);

The to get the seq column, you can do:

select id,  
       row_number() over (partition by id order by inserted_at) as seq,
       data
from some_table
order by id, seq;

The select is however going to be a bit slower compared to using a persisted seq column (especially with an index on id, seq).

If that becomes a problem you can either look into using a materialized view, or adding the seq column and then updating it on a regular basis (I would not do this in a trigger for performance reasons).

SQLFiddle example: http://sqlfiddle.com/#!15/db69b/1