title: Create big integer from the big end of a uuid in PostgreSQL
body: <p>I have a third-party application connecting to a view in my PostgreSQL database. It requires the view to have a primary key but can't handle the UUID type (which is the primary key for the view). It also can't handle the UUID as the primary key if it is served as text from the view.</p>
  
  <p>What I'd like to do is convert the UUID to a number and use that as the primary key instead.  However,</p>
  
  <pre><code>SELECT x'14607158d3b14ac0b0d82a9a5a9e8f6e'::bigint
  </code></pre>
  
  <p>Fails because the number is out of range.</p>
  
  <p>So instead, I want to use SQL to take the big end of the UUID and create an int8 / bigint. I should clarify that maintaining order is 'desirable' but I understand that some of the order will change by doing this.</p>
  
  <p>I tried:</p>
  
  <pre><code>SELECT x(substring(UUID::text from 1 for 16))::bigint
  </code></pre>
  
  <p>but the x operator for converting hex doesn't seem to like brackets. I abstracted it into a function but</p>
  
  <pre><code>SELECT hex_to_int(substring(UUID::text from 1 for 16))::bigint
  </code></pre>
  
  <p>still fails.</p>
  
  <p>How can I get a bigint from the 'big end' half of a UUID?</p>