I have a table that looks like the following:
|device_id | login_id |
-----------------------
| 1 | NULL |
| 2 | A |
| 3 | A |
| 4 | NULL |
| 5 | B |
| 5 | C |
-----------------------
I want to generate a new column (person_id) like the following. The column essentially generates a (person) unique identifier. Every new NULL value gets its own unique_id and every distinct non-null login_id gets its own unique person_id.
|device_id | login_id | person_id |
-----------------------------------
| 1 | NULL | 1 |
| 2 | A | 2 |
| 3 | A | 2 |
| 4 | NULL | 3 |
| 5 | B | 4 |
| 5 | C | 5 |
-----------------------------------
Is there a way to do this in pure PostgreSQL without going through a series of queries and temp tables? I'm using PostgreSQL 9.4. Any help is much appreciated.