Mastering PostgreSQL 12 - Third Edition

Considering alternative solutions

There is an alternative solution to this problem. Consider an example where you are asked to write an application to generate invoice numbers. The tax office might require you to create invoice numbers without gaps and without duplicates. How would you do this? Of course, one solution would be a table lock. However, you can really do better. Here is what you can do to handle the numbering problem we are trying to solve:

test=# CREATE TABLE t_invoice (id int PRIMARY KEY);CREATE TABLEtest=# CREATE TABLE  t_watermark (id int);  
CREATE TABLEtest=# INSERT INTO  t_watermark VALUES (0);  
INSERT 0  test=# WITH  x AS (UPDATE t_watermark SET id = id + 1 RETURNING *)  
         INSERT INTO  t_invoice 
         SELECT * FROM  x RETURNING *; id  ...