Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"

Ok I have decided that this is the best way to do it. Assuming I have a column (or a few columns) which can uniquely define a row I should be able to select up to a known row with a clever where clause. For example a table contains the following dummy data (the combination of s_alias and s_loginid will be unique for each row):

select * FROM vu_tbl_user_all_s ORDER BY s_surname, s_loginid;
s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled | s_4eyestype | s_usertype | b_hasvaluation | d_dob | s_email | s_phone | s_phone_mb
---------+-------------+---------+-----------+------------+------------+-----------------+--------------------+----------------+------------+---------------+---------+------------
CISX | 3 A Surname | 2aname | | Loginid3 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | A Asurname | A Aname | | Loginid2 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish3 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish4 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish5 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish6 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aoname | | Lloginid | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Loginid | | No restrictions | CISX Administrator | | | asdf(at)asdf(dot)com | |
CISX | Asurname | Aname | | Loginid8 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Loginid9 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | A Surname | Aname | | Loginid4 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Cisx | Cursor | | Systemuser | f | No restrictions | CISX Administrator | f | 1966-01-23 | | |
CISX | Dazor | Ronald | | Demolish | t | No restrictions | CISX Administrator | | 1967-12-12 | | |
CISX | Dazor | Ronald | | Demolish2 | f | No restrictions | CISX Administrator | | 1967-12-12 | | |
(14 rows)

I should be able to select up to row 9 (Loginid8)using this query:

select * FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or (s_surname = 'Asurname' and s_alias <= 'CISX' and s_loginid <= 'Loginid8') ORDER BY s_surname, s_loginid;

This works and I get the following (expected) output:

s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled | s_4eyestype | s_usertype | b_hasvaluation | d_dob | s_email | s_phone | s_phone_mb
---------+-------------+---------+-----------+-----------+------------+-----------------+--------------------+----------------+------------+---------------+---------+------------
CISX | 3 A Surname | 2aname | | Loginid3 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | A Asurname | A Aname | | Loginid2 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish3 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish4 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Demolish5 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aso it is not the very latest version.name | | Demolish6 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aoname | | Lloginid | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Asurname | Aname | | Loginid | | No restrictions | CISX Administrator | | | asdf(at)asdf(dot)com | |
CISX | Asurname | Aname | | Loginid8 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
(9 rows)

However, if I try to count the records using the count(*) function I get the following error.

ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in an aggregate function

Surely, if postgres can execute the query it should also be able to count the rows that would be returned in the query? Is this a bug in Postgres and is there a work around? I am using Postgres 7.3.

Thanks again!

On Wednesday 12 Feb 2003 11:45 pm, Dmitry Tkach wrote:
> Nicholas Allen wrote:
> >Ok I thought of that but what happens if there is no primary key in the
> > table?
>
> Then tough luck - as you said yourself, since everything else can be
> duplicated, the primary key is the only way to
> tell for sure which exact row you are talking about.
>
> >I can probably add primary keys to the table but I didn't design the
> > tables and so I have little (but luckily some) say over what columns
> > appear in them. What has actually happened is that we have a view on a
> > table and the view doesn't return the primary key. I'll try and ask the
> > database administrator to add the primary keys.
> >
> >Thanks for the help though I guess it is the only way to do it. I was just
> >hoping there would be a way to do it without a promary key to prevent
> > changes to our database views.
>
> I think you are better off redesigning your interface - even if you do
> modify the view, and use those suggestions I mentioned earlier,
> it will be slow like hell, unless your table only contains a few hundred
> rows, in which case it doesn't matter if you send the whole table to the
> client every time,
>
> Dima
>
> >On Wednesday 12 Feb 2003 9:37 pm, Bruno Wolff III wrote:
> >>On Wed, Feb 12, 2003 at 20:55:21 +0100,
> >>
> >> Nicholas Allen <nallen(at)freenet(dot)co(dot)uk> wrote:
> >>>I thought of this but the problem is that there may be multiple rows
> >>> with the same value for the column I am sorting on. Eg if sorting on a
> >>> surname then there may be 100s of people with the same surname so
> >>> generating a where clause that selects up to the exact person
> >>> previously selected is very difficult.
> >>
> >>Then you should sort on surname AND whatever you are using as the primary
> >>key.
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)or
> >
> >g