title: Selecting random IDs from another table....confused about LATERAL JOIN
body: <p>I'm trying to get a handle on generating random data in Postgres, and find I'm misunderstanding something about <code>LATERAL JOIN</code>. Building on some help I got earlier, I've got some code that's trying to:</p>
  
  <p>-- Generate a series of numbers<br>
  -- Generate timestamps on the hour matching the number sequence<br>
  -- Generate a random(ish) score in a roughly normal distribution for each row<br>
  -- Pick an ID randomly from a table in the database.  </p>
  
  <p>This last bit is not working. When I run the script shown below, I get a random value for the <code>facility_id</code>, but every row has the same random value. I'd like the random ID assigned on <em>each</em> row, not once globally for the whole run. In procedural thinking, the <code>facility_id</code> is being assigned before the loop, and I want it assigned in the loop. I thought that <code>LATERAL JOIN</code> would help me out here, but </p>
  
  <pre><code>WITH facilities_count AS
  (SELECT count(*) from facility)
  
   SELECT hour_number AS id, -- Get numbers in sequence.
         '2019-01-01 00:00'::timestamp + interval '1 HOUR' * hour_number AS stamp, -- Get hours in sequence
          ABS(TRUNC(normal_rand(1, 0, 1) * 100)) AS score, -- Create a random score in a ~normal distribution.
         random_facility.id
  
   FROM (SELECT * FROM generate_series(1,8760,1) AS hour_number) generated_numbers 
  
   LEFT JOIN LATERAL
        (SELECT id
            FROM facility 
          OFFSET floor(random() * (select count from facilities_count))
           LIMIT 1) random_facility
              ON true;
  </code></pre>
  
  <p>I thought that a subquery might work, but I also get a single value across all rows for the <code>facility_id</code> with this code:</p>
  
  <pre><code>WITH facilities_counter AS
  (SELECT count(*) from facility)
  
   SELECT hour_number AS id, -- Get numbers in sequence.
         '2019-01-01 00:00'::timestamp + interval '1 HOUR' * hour_number AS stamp, -- Get hours in sequence
          ABS(TRUNC(normal_rand(1, 0, 1) * 100)) AS score, -- Create a random score in a ~normal distribution.
           (SELECT id FROM facility OFFSET floor(random() * (select count from facilities_counter)) LIMIT 1)
  
   FROM (SELECT * FROM generate_series(1,8760,1) AS hour_number) generated_numbers;
  </code></pre>
  
  <p>I haven't listed the <code>facility</code> table definition, but the only field that matters above is <code>id</code>, so any table would work the same way.</p>
  
  <p>In case it makes any difference to the answer, once I can figure out the solution to this problem, I'd like to then use the random <code>facility_id</code> on each row as an input to select something else out of another table. </p>
  
  <p>Thanks for any help. I'm working on this not just to get the solution, but to try and get a better mental model of how the various tools work. I'm (obviously) not at the point where I can read code like the above and predict in my head how it will behave. Getting to that understanding is kind of fundamental to figuring out how to solve problems on my own. Put another way, I'm not only trying to solve this problem, I'm trying to reduce my mental gaps generally.</p>
  
  <h1>Premature Optimization</h1>
  
  <p>I'm editing my question as I need to post more code.</p>
  
  <p>Ha! Glad to see I'm not the only one accused of having <em>premature optimization problems</em> ;-) That's something that can be hurled in anger in a design or review session, to be sure.</p>
  
  <p>I think what you're showing is that the <code>id</code> is used in an expression, which makes its outcome indeterminate. And here's where I <strong>live and relearn</strong> that I I should post my table structure. Our <code>id</code> fields are UUIDs. I've attempted to hack together something that would use the UUID in an expression, but it does not change the behavior.</p>
  
  <p><code>where left(id::text,1) &lt;&gt; 'X'   -- prevent premature optimization</code></p>
  
  <p>The premature optimization you describe, and the workaround to defeating it, are not behaviors I could predict. Because gaps. I've tried running my code with the <code>left</code> expression through <code>explain (costs off)</code>, and I get this kind of output:</p>
  
  <pre><code>  CTE facilities_counter
      -&gt;  Aggregate
            -&gt;  Seq Scan on facility
    InitPlan 3 (returns $2)
      -&gt;  Limit
            InitPlan 2 (returns $1)
              -&gt;  CTE Scan on facilities_counter
            -&gt;  Seq Scan on facility facility_1
                  Filter: ("left"((id)::text, 1) &lt;&gt; 'X'::text)
    -&gt;  ProjectSet
          -&gt;  Function Scan on generate_series hour_number```
  
  I'm unclear from the output how I would distinguish the behavior you describe, which is still present here (?)
  </code></pre>