select single entry and its neighbours using direct-acess to index?

select id from mytable where id=45 order by name,name2;

Why do you want to select id if you already know it ?
Do you not want to specify a starting value for name and name2 ?

I'll presume you want to select a row by its 'id' and then get the
previous and next ones in the name, name2 order. I'll guess the id is
UNIQUE so these two other rows won't have the same id.

If I guessed right I have the solution, if I'm not please explain what
you wanna do more precisely ;)

Pierre-Frï¿œdï¿œric Caillaud wrote:

select id from mytable where id=45 order by name,name2;

Why do you want to select id if you already know it ?
Do you not want to specify a starting value for name and name2 ?

I'll presume you want to select a row by its 'id' and then get the
previous and next ones in the name, name2 order. I'll guess the id is
UNIQUE so these two other rows won't have the same id.

If I guessed right I have the solution, if I'm not please explain
what you wanna do more precisely ;)

sorry for being unclear.

but you guessed right. ID is UNIQUE and and I want to select a row by
its ID and also get the previous and next ones in the name, name2-order.

For the selected row I need all datafields and for the next and previous
I need only the ID (to have it referenced on the dataoutputpage for a
certain row).

I'm very looking forward for your solution.
thnx a lot,

peter

and then I'd like to select the two entries that would come before
and after according to the order "name,name2";
id is not ordered, but there is an index on (name,name2) so the
needed infomation about previous, next should be stored somewhere in
this index.

My current solution is to read all the data without the WHERE-clause
and then fetch the needed ones, which is quite time-demanding.

thnx,
peter

--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
pilsl@goldfisch.at

sorry for being unclear.

but you guessed right. ID is UNIQUE and and I want to select a row by
its ID and also get the previous and next ones in the name, name2-order.

For the selected row I need all datafields and for the next and previous
I need only the ID (to have it referenced on the dataoutputpage for a
certain row).

OK, this is a lot clearer now.
I suppose you have a UNIQUE(name,name2) or else, if you have several rows
with the same (name,name2) you'll get one of them, but you won't know
which one.

For example :
select * from test;
id | name | name2
----+------+-------
1 | a | a
2 | a | b
3 | a | c
4 | b | a
5 | b | b
6 | b | c
7 | c | a
8 | c | b
9 | c | c
(9 lignes)

Solution #1 :

- In you application :
SELECT * FROM test WHERE id=4;
id | name | name2
----+------+-------
4 | b | a

You then fetch name and name2 and issue the two following SELECT,
replacing 'a' and 'b' with name2 and name1 :

SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
name,name2 ASC LIMIT 1;
id | name | name2
----+------+-------
5 | b | b

SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
name,name2 DESC LIMIT 1;
id | name | name2
----+------+-------
3 | a | c

These should use an index on (name,name2).

Solution #2 :
You could do the same in a pl/pgsql function, which will be a lot faster,
and return three rows.

It is a pity you cannot use (name,name2) > ('a','b').

On 2004-12-06, Pierre-Fr�d�ric Caillaud <lists@boutiquenumerique.com>
wrote:

SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
name,name2 ASC LIMIT 1;

Write that WHERE clause instead as:

WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))

This is logically equivalent, but it gives the planner a better handle on
how to use an index scan to satisfy the query.

SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
name,name2 DESC LIMIT 1;

That needs to be ORDER BY name DESC, name2 DESC (the direction indicator
applies per-column and not to the output ordering). Same goes for the
WHERE clause in this query as the previous one, too.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
name,name2 ASC LIMIT 1;

Write that WHERE clause instead as:
WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))
This is logically equivalent, but it gives the planner a better handle on
how to use an index scan to satisfy the query.

I thought the planner had an automatic rewriter for these situations.
It'd be interesting to see an EXPLAIN ANALYZE output to see if it's indeed
rewritten.

SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
name,name2 DESC LIMIT 1;

That needs to be ORDER BY name DESC, name2 DESC (the direction indicator
applies per-column and not to the output ordering). Same goes for the
WHERE clause in this query as the previous one, too.

You're right, I screwed up !
Sorry ;)

=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <lists@boutiquenumerique.com> writes:

SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
name,name2 ASC LIMIT 1;

Write that WHERE clause instead as:
WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a'))
This is logically equivalent, but it gives the planner a better handle on
how to use an index scan to satisfy the query.

I thought the planner had an automatic rewriter for these situations.

No. There was a prior discussion of this, saying that we really ought
to support the SQL-spec row comparison syntax:
... WHERE (name, name2) > ('b', 'a');
which would map directly onto the semantics of a 2-column index. We
don't have that functionality yet, though (we take the syntax but the
semantics are not SQL-compliant) let alone any ability to pass it
through to a 2-column index.

regards, tom lane

I thought the planner had an automatic rewriter for these situations.

No. There was a prior discussion of this, saying that we really ought
to support the SQL-spec row comparison syntax:

What I meant was that I thought the planner could rewrite :
(A and C) or (A AND B) as A and (B or C)
which is more index-friendly.

... WHERE (name, name2) > ('b', 'a');
which would map directly onto the semantics of a 2-column index. We
don't have that functionality yet, though (we take the syntax but the
semantics are not SQL-compliant) let alone any ability to pass it
through to a 2-column index.

One could always use ARRAY[name,name2] > ARRAY['b','a']
But it is NOT index-friendly...

Pierre-Fr�d�ric Caillaud <lists@boutiquenumerique.com> writes:

One could always use ARRAY[name,name2] > ARRAY['b','a']
But it is NOT index-friendly...

It won't use an existing two-column index but you can create an expression
index on array[name,name2] and this expression will use it. It won't work if
either column is NULL though.

--
greg