generate new column from another table column, treating nulls are unique

Question

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.

Answer 1

Adding the column:

alter table device_login
  add column person_id int ;

The query is a bit complicated, due to the nulls. First group the not null logins per login and keep each null seprately

with a as 
( select min(device_id) as device_id, login_id
  from device_login
  where login_id is not null
  group by login_id 
union all
  select device_id, login_id
  from device_login
  where login_id is null
)

then use dense_rank() to assign separate numbers to each group

, b as
( select dl.device_id, a.login_id, 
         dense_rank() over (order by a.device_id, a.login_id) as rnk
  from a join device_login as dl 
      on a.login_id = dl.login_id 
      or a.login_id is null and a.device_id = dl.device_id
)

and finally update the table:

update device_login as dl
set person_id = b.rnk
from b
where b.login_id = dl.login_id 
   or b.login_id is null and b.device_id = dl.device_id ;

Tested at SQLfiddle.