subquery in FROM must have an alias

Question

I have this query I have written in PostgreSQL that returns an error saying:

[Err] ERROR:
LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge

This is the whole query:

SELECT COUNT (made_only_recharge) AS made_only_recharge
FROM (
    SELECT DISTINCT (identifiant) AS made_only_recharge
    FROM cdr_data
    WHERE CALLEDNUMBER = '0130'
    EXCEPT
    SELECT DISTINCT (identifiant) AS made_only_recharge
    FROM cdr_data
    WHERE CALLEDNUMBER != '0130'
)

I have a similar query in Oracle that works fine. The only change is where I have EXCEPT in Oracle I have replaced it with the MINUS key word. I am new to Postgres and don't know what it is asking for. What's the correct way of handling this?

Solution

Add an ALIAS onto the subquery,

SELECT  COUNT(made_only_recharge) AS made_only_recharge
FROM    
    (
        SELECT DISTINCT (identifiant) AS made_only_recharge
        FROM cdr_data
        WHERE CALLEDNUMBER = '0130'
        EXCEPT
        SELECT DISTINCT (identifiant) AS made_only_recharge
        FROM cdr_data
        WHERE CALLEDNUMBER != '0130'
    ) AS derivedTable                           -- <<== HERE

Comments

[Andrew Cassidy]

@JohnWoo thanks for this, but why is it needed (I guess I'm asking a theory question here)?

[stackhelper101]

@AndrewCassidy You have to define so you will be able to add further constraints on your query (WHERE derivedTable.<attribute> = 5). otherwise your db will not know how to refer to the subquery

[Tregoreg]

@AndrewCassidy It is just unlucky syntax. As long as you are not referencing to that subquery, it does not matter what it's alias is. Personally, I'm using AS pg_sucks, meaning "well, here you have some redundant identifier, but you could generate some internally by yourself, damn postgres!" :)

[Scratte]

Your wording suggest there is such a requirement for both Oracle and MySQL. Am I reading it right?

[lmat - Reinstate Monica]

@Scratte I think you're right and the wording is turned around. "MySQL and Oracle but others" should be "Postgresql, but other ssuch as MySQL and Oracle" I think. Of course it's still a run-on sentence, and could be further improved. The 2013 answer is fine and this answer adds nothing (comment on the 2013 answer if you must), so the latter should be removed.

[jbowman]

@Tregoreg My aliases always involve some sort of expletive to the same effect.

[ysth]

mysql does require aliases