A nice trick for small unicity constraint index in PostgreSQL

I found this nice trick and thought it may interest others.

Imagine you have a few foreign keys in a table which together form a primary key in the database theory meaning (a minimal set of columns that yields uniqueness of rows).

It may be costly in space and time to have a unique constraint on these foreign keys.

But for example in Django you would not expect a huge number of sites or logistics centers, etc.

If you know you data well, there are always some parameters tables which are small (less than 256 or less than 65536 rows), compared to exploitation tables (the daily data increase linked to activity).

Now look at generated columns magic on a simple example:

  test_db=# CREATE TABLE site_center (
      id INT PRIMARY KEY GENERATED ALWAYS AS ((COALESCE(center_id, 0) << 8) + site_id) STORED, 
      site_id INT NOT NULL,
      center_id INT
  );

  test_db=# insert into site_center (site_id) VALUES (1);
  INSERT 0 1

  test_db=# insert into site_center (site_id, center_id) VALUES (1, 12);
  INSERT 0 1

  test_db=# select \* from site_center;
    id  | site_id | center_id
  ------+---------+-----------
      1 |       1 |
   3073 |       1 |        12
  (2 lignes)

Nice :)