find next in an index

On Sun, Feb 13, 2005 at 14:03:02 +1100,
Neil Dugan <postgres@butterflystitches.com.au> wrote:

Hi,
I am trying to find out how to get the next record according to a
particular index.
I have a table with a name field and a serial field. The name field
isn't unique so I made an index on name(varchar) & serialno(bigserial).
I also have an index just on 'name'. I am having trouble working out
the syntax for the query.

select * from table where name>='jack' and serialno!='2'
order by name,serialno;

I don't think this will work under all circumstances.

Any help appreciated. Thanks.

I think using OFFSET 1 and LIMIT 2 with an appropiate WHERE clause
will get you want you want. If you are going to keep stepping through
the list, you might want to use a cursor.

On Sat, 2005-02-12 at 21:47 -0600, Bruno Wolff III wrote:

On Sun, Feb 13, 2005 at 14:03:02 +1100,
Neil Dugan <postgres@butterflystitches.com.au> wrote:

Hi,
I am trying to find out how to get the next record according to a
particular index.
I have a table with a name field and a serial field. The name field
isn't unique so I made an index on name(varchar) & serialno(bigserial).
I also have an index just on 'name'. I am having trouble working out
the syntax for the query.

select * from table where name>='jack' and serialno!='2'
order by name,serialno;

I don't think this will work under all circumstances.

Any help appreciated. Thanks.

I think using OFFSET 1 and LIMIT 2 with an appropiate WHERE clause
will get you want you want. If you are going to keep stepping through
the list, you might want to use a cursor.

With cursors is it possible to set the cursor to the location of a
particular row (found by another select query). The documentation say
you can set at a particular row number, but I would like to move the
cursor to the same record as found by another query (same index, same
fields).

Neil Dugan <postgres@butterflystitches.com.au> writes:

Hi,
I am trying to find out how to get the next record according to a
particular index.
I have a table with a name field and a serial field. The name field
isn't unique so I made an index on name(varchar) & serialno(bigserial).
I also have an index just on 'name'. I am having trouble working out
the syntax for the query.

select * from table where name>='jack' and serialno!='2'
order by name,serialno;

From what you describe it sounds like you are really asking for

SELECT *
FROM table
WHERE (name > 'jack')
OR (name = 'jack' AND serialno>2)
ORDER BY name, serialno
LIMIT 1

However Postgres doesn't really handle this very well. If it uses the index at
all it fetches all the records starting from the beginning of the table
stopping when it finds the right one.

One option is to do

SELECT *
FROM table
WHERE name >= 'jack'
AND ((name > 'jack') OR (name = 'jack' AND serialno>2))
ORDER BY name, serialno
LIMIT 1

Which is fine as long as there are never too many records with the name
'jack'. If you have can possibly have hundreds of records with the name 'jack'
then it's going to spend time skimming through all of them even if you're
already far down the list.

To guarantee reasonable behaviour it looks like you have to do this:

(
SELECT *
FROM table
WHERE name > 'jack'
ORDER BY name, serialno
LIMIT 1
) UNION ALL (
SELECT *
FROM table
WHERE name = 'jack' AND serialno>2
ORDER BY name, serialno
LIMIT 1
)
ORDER BY name, serialno
LIMIT 1

I think there's a todo item about making indexes handle the row-wise
comparison operators like:

WHERE (name,serialno) > ('jack',2)

But that doesn't work properly in Postgres currently. (It may seem to, but
don't be confused, it's actually not doing what you want). It's too bad since
it would be a nice clean simple way to get exactly the right behaviour.

--
greg

On Sun, 2005-02-13 at 01:24 -0500, Greg Stark wrote:

Neil Dugan <postgres@butterflystitches.com.au> writes:

Hi,
I am trying to find out how to get the next record according to a
particular index.
I have a table with a name field and a serial field. The name field
isn't unique so I made an index on name(varchar) & serialno(bigserial).
I also have an index just on 'name'. I am having trouble working out
the syntax for the query.

select * from table where name>='jack' and serialno!='2'
order by name,serialno;

From what you describe it sounds like you are really asking for

SELECT *
FROM table
WHERE (name > 'jack')
OR (name = 'jack' AND serialno>2)
ORDER BY name, serialno
LIMIT 1

However Postgres doesn't really handle this very well. If it uses the index at
all it fetches all the records starting from the beginning of the table
stopping when it finds the right one.

One option is to do

SELECT *
FROM table
WHERE name >= 'jack'
AND ((name > 'jack') OR (name = 'jack' AND serialno>2))
ORDER BY name, serialno
LIMIT 1

Which is fine as long as there are never too many records with the name
'jack'. If you have can possibly have hundreds of records with the name 'jack'
then it's going to spend time skimming through all of them even if you're
already far down the list.

To guarantee reasonable behaviour it looks like you have to do this:

(
SELECT *
FROM table
WHERE name > 'jack'
ORDER BY name, serialno
LIMIT 1
) UNION ALL (
SELECT *
FROM table
WHERE name = 'jack' AND serialno>2
ORDER BY name, serialno
LIMIT 1
)
ORDER BY name, serialno
LIMIT 1

I think there's a todo item about making indexes handle the row-wise
comparison operators like:

WHERE (name,serialno) > ('jack',2)

But that doesn't work properly in Postgres currently. (It may seem to, but
don't be confused, it's actually not doing what you want). It's too bad since
it would be a nice clean simple way to get exactly the right behaviour.

Thanks Greg,
I have put your suggestion (number 2) in my code. It is working quite
well.