Clever Geek Handbook

PostgreSQL query inconsistency

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 this, 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 tried adding: ORDER BY (page.page_namespace, pagelinks.pl_namespace) ASC until the end of the request with no 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.

thank

+2


source to share


4 answers


I haven't checked the documentation, but the fact that you have an expression GROUP BY

in parentheses looks unusual to me. What happens if you don't use parentheses here?



+12


source


I am not an expert, but my understanding of this post is that PostgreSQL is commenting on the impossibility to handle page.page_namespace <=3

either pagelinks.pl_namespace <=3

. To make such a comparison, you need to determine the order and perhaps one of these fields is not a standard numeric field. Or maybe there is a problem with integer or floats - for example. the question if "3.0 = 3" is actually that easy to answer, since the floating point representation of 3.0 is most likely not exactly 3.



It's all just guesswork, but I'm sure those two <=

are your problem.

0


source


Well, I don't know which scheme (*) you are using, but the error message seems clear. One of your columns is of type RECORD and there is no statement for the RECORD order. But some parts of your query like <= require such an ordering operator. Using ORDER BY is unlikely to help, as the main problem is the lack of an operator to order ...

(*) From your other questions, I am assuming your schema is a wikipedia page reset at http://download.wikimedia.org/enwiki/latest/enwiki-latest-page.sql.gz Right?

0


source


i has some bug. but I found this page, http://www.w3schools.com/sql/sql_groupby.asp it is easy, you have to group without parentheses eg:

GROUP BY (page.page_namespace, pagelinks.pl_namespace) correct GROUP BY page.page_namespace, pagelinks.pl_namespace

Hello!!

-1


source






More articles:


All Articles