Assign random number in postgresql

Question

I have table in postgresql with statename, districtname and DistID . Now I want to assign random number(starting from 1 to count of district in a particular state) to the column 'DistID' for each district based on their states. Kindly help.

Do the numbers need to be unique within a particular state, and when you say random do you mean they need to be unpredictable or you just don't care what the numbers are? — Gary - Stand with Ukraine
– Gary - Stand with Ukraine, Commented Aug 5, 2015 at 7:13

Gary - Stand with Ukraine · Accepted Answer · 2015-08-05 07:19:18Z

1

The query below will update the table so that each district is numbered within the state with values starting from 1.

The query assigns the IDs in a random order. If you wanted predictable change random() to district and it will order them by district name alphabetically.

update t
set distid = v.distid
from (
select 
  statename, district, 
  row_number() over (partition by statename order by random()) as distid
from t) v
where v.statename = t.statename and v.district = t.district;

Fiddle example at http://sqlfiddle.com/#!15/86341/5

answered Aug 5, 2015 at 7:19

Gary - Stand with Ukraine

7,6391 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign random number in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related