1

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.

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.

  • thank so much. I'll try this out tomorrow. Incidentally, I'm using psycopg2 in python so hoping this will still work. I'll green check-it if it works. – Donald Vetal Jun 14 '15 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.