Skip site navigation (1) Skip section navigation (2)

Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Sam Mason" <sam(at)samason(dot)me(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org, Ragnar <gnari(at)hive(dot)is>
Subject: Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
Date: 2008-06-30 16:24:24
Message-ID: b42b73150806300924t335c17et25bae6fa9c15c1f6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, Jun 30, 2008 at 8:51 AM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
>>
>>  select * from foo where (o,pk)>(o,?) order by o limit 10;
>
> Hum, I think I must be missing something.  I'm not sure why you're
> comparing 'o' to itself and you're not putting any ordering constraint
> on the primary key.  I think the query should look closer to:
>
>    SELECT * FROM foo WHERE (o,pk)>($1,$2) ORDER BY o,pk LIMIT 10;
>
> Or am I going mad?

yes, you are correct.  you need to supply at least one value for each
ordered field.  I think this is what the OP was tring to say.

usually it's much simpler than this:
select * from foo where pk > $1 order by pk limit 1;

This will pull up table in pk order which is usually fine.  Any
ordering will do as long as the combination of fields being ordered
are unique.  Adding pk as the second criteria is only needed if you
want to order by a non duplicate field.  If 'o' is a candidate key
this is not required.

btw, the use of OFFSET for this type of problem is actually fairly
terrible...it's almost never a good idea.

merlin

In response to

pgsql-general by date

Next:From: Teodor SigaevDate: 2008-06-30 17:08:55
Subject: Re: multi-word expression full-text searching
Previous:From: Fco. Mario Barcala RodríguezDate: 2008-06-30 15:50:02
Subject: multi-word expression full-text searching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group