PostgreSQL query inconsistency

Question

Hi again,

I am trying to execute this SQL command:

SELECT page.page_namespace, pagelinks.pl_namespace, COUNT(*) 
    FROM page, pagelinks
    WHERE 
        (page.page_namespace <=3 OR page.page_namespace = 12 
            OR page.page_namespace = 13
        ) 
        AND 
        (pagelinks.pl_namespace <=3 OR pagelinks.pl_namespace = 12 
            OR pagelinks.pl_namespace = 13
        )
        AND 
        (page.page_is_redirect = 0)
        AND 
        pagelinks.pl_from = page.page_id 
    GROUP BY (page.page_namespace, pagelinks.pl_namespace) 
;

When I do so, I get the following error:

    ERROR:  could not identify an ordering operator for type record
    HINT:  Use an explicit ordering operator or modify the query.

    ********** Error **********

    ERROR: could not identify an ordering operator for type record
    SQL state: 42883
    Hint: Use an explicit ordering operator or modify the query.

I have tried adding : *ORDER BY (page.page_namespace, pagelinks.pl_namespace) ASC* to the end of the query without success.

UPDATE:

I also tried this:

SELECT page.page_namespace, pagelinks.pl_namespace, COUNT(*) 
    FROM page, pagelinks
    WHERE pagelinks.pl_from = page.page_id 
    GROUP BY (page.page_namespace, pagelinks.pl_namespace) 
;

But I still get the same error.

Thx

Answer 1

I'm not really an expert, but my understanding of that message is that PostgreSQL compains about not being able to handle either page.page_namespace <=3 or pagelinks.pl_namespace <=3. To make a comparison like that you need to have an order defined and maybe one of these fields is not a standard numerical field. Or maybe there is some issue with integer vs. floating point -- e.g. the question if "3.0 = 3" isn't really that easy to answer since the floating point representation of 3.0 is most likely not exactly 3.

All just guesswork, but I'm pretty sure those two <=s are your problem.

Answer 2

Well, I do not know the schema (*) you use but the error message seems clear. One of your columns is of type RECORD and there is no operator to order RECORD.But some parts of your query like <= requires such an ordering operator. Using ORDER BY is unlikely to help since the crux of the problem is the lack of an operator to order...

(*) From your other questions, I assume your schema is Wikipedia page dump in http://download.wikimedia.org/enwiki/latest/enwiki-latest-page.sql.gz Correct?

Answer 3

I haven't checked any documentation but the fact that you have your GROUP BY expression in parentheses looks unusual to me. What happens if you don't use parentheses here?