database – How to make an auto-increment ID against another field in PostgreSQL "PostgreSQL"?


Guys, it's been a while since I've been looking for a way to do this, what would be the correct way to do an auto increment that depends on another field as in the example below?

id_empresa | id_pedido
 1         | 1
 1         | 2
 1         | 3
 1         | 4
 2         | 1
 2         | 2
 3         | 1

I've already thought about making only one trigger and procedure for all tables, but I don't know how to do it.

A co-worker told me to do as follows:

1 This field would not be part of the primary key, so it would be a serial PK id, company_id integer FK PK, num_ped integer.

2 I create a friendly_sequence table just to reference these ids, for example a record in this table would be, (request, 1) and its columns would be (table, sequence).

3 Every time I place an order I make an UPDATE with RETURNING in the friendly_sequence table.

4 Receiving this value, I INSERT the order table


At first, as rLinhares said, it's not a PK, so you should send it to the bank.

a simple sub-query solves the problem:

insert into pedidos (id_pedido,id_empresa) values ((select coalesce(max(id_pedido),0) + 1 from pedidos where id_empresa = 1 ), 1) returning id_pedido;

The coalesce, serves for the first request, which is when there is none, it must return 0.

Note that the company_id value is used twice.

Evading the question a little, and getting ahead of myself, every order must have items, in this case I used the returning which will be the FK in the items table (it must be a FK composed with company, order, item) and to maintain integrity, all this must be within a transaction.

Scroll to Top