Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals).

I have anonymized all the names and other personalized information, and I would like to keep each lat/long pair together, however randomize them within the users, so that the locations stay accurate, but their association with a User is now random.

I assume this involves an UPDATE function, but I'm not sure how to perform it on the same table.

I am very new at postgres and would appreciate any help - thanks!

share|improve this question
If the rows cannot be identified to any real user (you have put random names there), why don't you keep the lat/longs as they are? – ypercubeᵀᴹ Feb 16 '12 at 21:17
Good point, however the user IDs stay identical so if you have access to the original dataset, you can match to the fake set and find the user's location. – adarsh Feb 17 '12 at 14:56
Ok, then Erwin's answer seems to work fine. No idea how efficient will be with a big table. – ypercubeᵀᴹ Feb 17 '12 at 14:58
up vote 2 down vote accepted

so that the locations stay accurate, but their association with a User is now random.

If I understand you right, it could work like this:

SET lat =
  , lon = t.lon
   SELECT,, t1.lon
   FROM  (
      SELECT lat, lon, row_number() OVER () AS rn
      FROM   tbl t1
      ) t1
   JOIN  (
      SELECT id, row_number() OVER (ORDER BY random()) AS rn
      FROM   tbl t2
      ) t2 USING (rn)
   ) t

lat / lon have moved to another row, randomly. Every connection to it's original row is gone, but lat & lon stay together.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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