'Multiple postgres sequences for each foreign key

I'm using postgres 14 and my invoice table looks something like that:

name type
location_id uuid
invoice_id int
... ...

The location_id is a foreign key from the location table. location_id and invoice_id are a composite primary key.

What I want to achieve now is that the invoice id for each location starts at 1 and increments automatically.
Is there a way to implement this with something like a sequence?

postgresqldatabase-design


Solution 1:[1]

I solved the issue by using a before insert trigger:

create or replace Function private.invoice_before_insert()
returns trigger
language plpgsql
as $$
  begin
    new.invoice_id = (
      select coalesce(max(invoice_id), 0) + 1
      from private.invoice
      where invoice_location_id = new.invoice_location_id
    );
    new.invoice_created_at = current_timestamp;

    return new;
  end;
$$;

create trigger invoice_before_insert
before insert
on private.invoice
for each row
execute function private.invoice_before_insert();

I'm not sure if this aprouch has any drawbacks tho. ¯_(?)_/¯

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Norbert Bartko

Related Questions