title: Postgres function getting unique constraint violation
body: <p>I have a <code>plpgsql</code> function containing this</p>
  
  <pre><code>update allplaces set visittime = now() where visittime is null and person = 'me' and place = 'this';
  if (FOUND) then
      update allplaces set visittime = null where person = 'me' and place != 'this';
      return true;
  else
      return false;
  end if;
  </code></pre>
  
  <p>The <code>allplaces</code> table has a unique constraint as <code>create unique index indexallplaces on allplaces (person) where visittime is not null</code> which means that a given <code>person</code> can only have 1 record with <code>visittime</code> that is not null.</p>
  
  <p>I'm trying to switch the <code>visittime</code> from whichever record of mine (<code>person</code> equals <code>'me'</code>) it was before (<code>place</code> equals whatever) to the one where <code>place</code> equals <code>'this'</code>, but I get a unique constraint violation for the unique index I mentioned. I can't set the old value to null without changing ensuring a new value has been set, hence the need for the <code>if (FOUND)</code> check.</p>
  
  <p>How can I change this postgres function to work? The function is using <code>plpgsql</code> but it could be normal SQL too if that works better in this case.</p>
  
  <p>EDIT</p>
  
  <pre><code>perform from allplaces where visittime is null and person = 'me' and place = 'this' and ...otherconditions...;
  if (FOUND) then
      update allplaces set visittime = null where person = 'me';
      update allplaces set visittime = now() where person = 'me' and place = 'this' and ...otherconditions...;
      return true;
  else
      return false;
  end if;
  </code></pre>