Can you create bigserial that is scoped to two other columns? [duplicate]

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=#

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

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

Just a guess.

INSERT INTO TABLE (ID, SEQ, DATA)
VALUES
(
 IDVALUE,
 (SELECT max(SEQ) +1 FROM TABLE WHERE ID = IDVALUU),
 DATAVALUE
);

Can you create bigserial that is scoped to two other columns? [duplicate]

Related

Recent Posts