Generate sequential numbers on a join

For each right-side row in a JOIN I'd like to create a sequential number, starting from 1 (or 0) for each left-side row.

Example:

create table persons (person_id int, person_name text);
create table places (place_id int, person_id int, place_name text);
insert into persons values (10, 'Aulus Agerius'), (20, 'Numerius Negidius');
insert into places values (10, 10, 'Anytown'), (20, 10, 'Timbuktu'), (30, 20, 'Podunk');

select person_name, place_name
from persons join places using (person_id)
order by person_id, place_id;

fiddle

Desired result:

person_name
place_name
place_seq

Aulus Agerius
Anytown
1

Aulus Agerius
Timbuktu
2

Numerius Negidius
Podunk
1

So for example a 2 in place_seq would mean "this is the 2nd place found for this person".

How to create the place_seq column?

You need the ROW_NUMBER() (manual) window function as follows (the code below is shown on the fiddle here - based on your own fiddle +1 for that!):

SELECT 
  person_id,
  place_id,
  person_name, 
  place_name, 
  ROW_NUMBER() 
    OVER (PARTITION BY person_id ORDER BY place_id) AS rn
FROM persons 
  JOIN places using (person_id)
ORDER BY person_id, place_id;

This is based on the assumption that person_id & place_id are at least UNIQUE or (better) the PRIMARY KEYs of their respective tables. Conceivably, two people could have the same name - I've known two sets of three people (indepedent - not the same family) with the same name. As for places, duplicates are even more common.

Result:

person_name       place_name    rn
Aulus Agerius        Anytown     1
Aulus Agerius       Timbuktu     2
Numerius Negidius     Podunk     1

In order to be guaranteed to obtain consistent results over different runs, a deterministic ORDER BY clause in the window function is necessary. In general, an SQL statement should contain an ORDER BY, as should window functions, if we desire results in deterministic order.

For window functions, see also here and here. These functions are very powerful and will repay any effort spent learning them many times over. There are articles all over the web about them - I would urge you to have a look around and practice until you have have (some degree of) mastery of them.

As a first port of call, I highly recommend Bruce Momjian's YouTube presentation here (slides here). Bruce Momjian is VP and "Postgres Evangelist" for EnterpriseDB - the world's premier PostgreSQL company. He's also an excellent speaker and explains PostgreSQL functionality very well!

StackExchange Database Administrators Q#316783, answer score: 6