How to Create a PostgreSQL Sequence Dependent on Another Column?

PostgreSQL: Create auxiliary sequence based on another column

In PostgreSQL, you can create a sequence that depends on another column, allowing you to generate sequence numbers within a subgroup of a table.

Consider the following form:

<code class="language-sql">CREATE TABLE stuff (
    id integer PRIMARY KEY,
    seq integer NOT NULL,
    data text
);</code>

You want each combination of id and seq to be unique, similar to the following example:

<code>ID  | SEQ   | DATA
----+------ +-------------------
 1  | 1     | Quick brown fox...
 1  | 2     | Quick brown fox...
 1  | 3     | Quick brown fox...
 2  | 1     | Quick brown fox...
 3  | 1     | Quick brown fox...</code>

Create auxiliary tables and triggers

To do this, you need to create two tables and a trigger:

<code class="language-sql">CREATE TABLE things (
    id SERIAL PRIMARY KEY,
    name text
);

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

CREATE SEQUENCE things_id_seq;</code>

The

things table is the parent table containing the id columns. The make_thing_seq trigger automatically creates a new sequence named things for each row inserted into the thing_seq_<thing_id> table.

Fill the Stuff sequence

Now you need to create a second trigger to populate the stuff column in the seq table:

<code class="language-sql">CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger AS $$
BEGIN
  NEW.seq := nextval('thing_seq_' || NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW
    EXECUTE PROCEDURE fill_in_stuff_seq();</code>

This trigger ensures that when a new row is inserted into the stuff table, it retrieves the corresponding sequence (based on the id column) and assigns its next value to the seq column. Note that the LANGUAGE plpgsql statement is added here to clearly specify that the language of the function is PL/pgSQL.

Demo

Insert some data and verify sequence generation:

<code class="language-sql">INSERT INTO things (name) VALUES ('Joe');
INSERT INTO things (name) VALUES ('Bob');
SELECT * FROM things;

-- 检查序列表
\d+ things_id_seq

INSERT INTO stuff (id, data) VALUES (1, 'Keychain');
INSERT INTO stuff (id, data) VALUES (1, 'Pet goat');
INSERT INTO stuff (id, data) VALUES (2, 'Family photo');
INSERT INTO stuff (id, data) VALUES (1, 'Redundant lawnmower');
SELECT * FROM stuff;</code>

The result will demonstrate automatic assignment of sequences based on the id column. The d things_id_seq command is used to display sequence details.

The above is the detailed content of How to Create a PostgreSQL Sequence Dependent on Another Column?. For more information, please follow other related articles on the PHP Chinese website!