Postgresql join with limit
<p>I am creating SQL query that involves multiple tables with 1 to N relation to support pagination.</p>

<p>To get the first 10 parents, I tried to do</p>

<pre><code>SELECT * from parent p
LEFT JOIN child c
ON c.parent_id = p.id
LIMIT 10
</code></pre>

<p>This does not work if any parent has more than one children</p>

<p>One alternative I can do is</p>

<pre><code>SELECT * from parent LIMIT 10 into temp_p;
SELECT * from temp_p p
LEFT JOIN child c
ON c.parent_id = p.id
</code></pre>

<p>This is pretty clumsy. What I would like to do is</p>

<pre><code>SELECT * from parent p LIMIT 10
LEFT JOIN child c
ON c.parent_id = p.id
</code></pre>

<p>but of course the syntax is wrong. I am wondering if Postgresql have some way to support what I want to do. </p>