Postgresql random row assignment

Question

I have got 2 tables

Student
|---------------------|------------------|
| id | studentname |
|---------------------|------------------|
| 1 | studentA |
|---------------------|------------------|
| 2 | studentB |
|---------------------|------------------|
| 3 | studentC |
|---------------------|------------------|
| 4 | studentD |
|---------------------|------------------| 
Chair
|---------------------|------------------|
| id | chairname |
|---------------------|------------------|
| 1 | chairA |
|---------------------|------------------|
| 2 | chairB |
|---------------------|------------------|
| 3 | chairC |
|---------------------|------------------|

I need a query to randomly assign a student to a chair in postgresql. Note that there can be more chairs than students or vice versa. In case there is more student than chairs, students not assigned will have null value in chair.

For the above tables we should have a result like

|---------------------|------------------|
| studentname | chairname |
|---------------------|------------------|
| studentA | chairC |
|---------------------|------------------|
| studentB | chairA |
|---------------------|------------------|
| studentC | chairB |
|---------------------|------------------|
| studentD | NULL |
|---------------------|------------------|

OR

|---------------------|------------------|
| studentname | chairname |
|---------------------|------------------|
| studentA | chairA |
|---------------------|------------------|
| studentB | chairC |
|---------------------|------------------|
| studentC | NULL |
|---------------------|------------------|
| studentD | chairB |
|---------------------|------------------|

Any idea how this can be done in postgresql?

Answer 1

You could try using ROW_NUMBER with a random ordering to assign student IDs to the various chairs:

WITH cte1 AS (
 SELECT chairname, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
 FROM Chair
),
cte2 AS (
 SELECT studentname, ROW_NUMBER() OVER (ORDER BY id) rn
 FROM Student
)
SELECT
 s.studentname,
 c.chairname
FROM cte2 s
LEFT JOIN cte1 c
 ON s.rn = c.rn;

enter image description here

Demo

Note that I actually generate a row number sequence across both tables, including the Student table. This is to ensure that we always compare row number values which would match between both tables.

Edit: Replace LEFT JOIN with FULL OUTER JOIN to generally handle the case where the number of students exceed chairs or vice-versa.

• S-Man: That is really a nice solution
• forpas: Since there is the case of more chairs than students, I think that a FULL JOIN is more suitable.
• Kevin Joymungol: Great solution. Works. Thanks