In composite primary key, auto-increment first data for each second one in postgres?

Question

Let's suppose I have a table with a composite primary key, as follows:

CREATE TABLE fruits(
   id SERIAL NOT NULL,
   name VARCHAR NOT NULL,
   PRIMARY KEY(id , name)
);

Had name not be a primary key too, we could have expected this behaviour:

id        name

1         banana
2         apple
3         peach
4         banana
5         apple

However, I want id, the first of the two primary keys, to be incremented for each name, in order to have an increment of id for EACH value of name.

Is it possible, with any data type like SERIAL or with any feature provided by postgres, to reach the following behaviour when you have a composite key, without having to add extra logic ( like a TRIGGER ) for each new row of fruits and to reach an example such as follows?

id just has to be an incrementing integer.

id        name

1         banana
1         apple
1         peach
2         banana
3         banana
2         peach

Does this answer your question? [Serial numbers per group of rows for compound key](https://stackoverflow.com/questions/24918552/serial-numbers-per-group-of-rows-for-compound-key) — Kadet, Jun 14 '22 at 05:38
I recommend that you forget about that requirement and continue with the sequence. More fancy numbering can be computed on `SELECT` — Laurenz Albe, Jun 14 '22 at 05:40
@Kadet Sadly this solition uses some extra logic, which I know how to do already. I was interesting in knowing if a data type like SERIAL could manage this on a native way — Virthuss, Jun 14 '22 at 05:56
Short answer - You can't do this without cutom logic. Use one serial for all groups and use rank inside each group if you need order or numbers — Kadet, Jun 14 '22 at 06:03
@Kadet Sadly I reached the same conclusion. I hope this is doable on the near future. — Virthuss, Jun 14 '22 at 06:05
As a side note: the use of `serial` is discouraged in modern Postgres versions in favor of the standard compliant `identity` column (but to make this clear: they too don't offer any solution to what you are trying to do). — , Jun 14 '22 at 06:16
@a_horse_with_no_name I think I will probably stick to the normal integer if there is no native data type that can handle such thing anyways. `identity` seems to be for unique distinct values while I want more unique set of `id` + `name` — Virthuss, Jun 14 '22 at 06:51

score 0 · Answer 1 · answered Jun 14 '22 at 05:07

auto-increment means every time insert operation the sequence nextval increased by 1. If the sequence column have some duplicated value then you may want to reset the nextval of the sequence.

CREATE temp TABLE fruits (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    name text NOT NULL,
    PRIMARY KEY (id, name)
);

INSERT INTO fruits (name)
    VALUES ('banana');

SELECT
    nextval('fruits_id_seq');
--return the sequence next value. it will return 2.

INSERT INTO fruits (id, name)
    VALUES (1, 'apple');

INSERT INTO fruits (id, name)
    VALUES (1, 'peach');

INSERT INTO fruits (id, name)
    VALUES (2, 'banana');

INSERT INTO fruits (id, name)
    VALUES (3, 'banana');

INSERT INTO fruits (id, name)
    VALUES (2, 'peach');

SELECT
    nextval('fruits_id_seq');
--return 6.

ALTER SEQUENCE fruits_id_seq
    RESTART WITH 4;

SELECT
    nextval('fruits_id_seq');
--return 4.

The problem here is that you still have to insert the incrementing value yourself. It's not automatic as it would be with a SERIAL. I want to know if such thing is doable using data type only. — Virthuss, Jun 14 '22 at 05:30
@Virthuss one way or another if the sequence have duplicated value then you may want reset the nextval of the sequence. sequence cannot compute the previously max sequence value. And if every insert operation properly compute the previous max sequence value would be expensive. — jian, Jun 14 '22 at 06:04

In composite primary key, auto-increment first data for each second one in postgres?

1 Answers1