Postgresql "subquery in FROM must have an alias" error

Question

I'm doing a quick query using Postgresql 8.2 and I've done queries like this a thousand times before, but I can't figure out why I'm getting this error. I probably am missing something obvious, but it's saying my "subquery in FROM must have an alias". I do have an alias for my subquery "inner", and I can't figure out why else I would be getting the error.

    SELECT "Branch", "Zip_5", "CountofStops", avg("EarlyTime") As 
    "Average_Arrival_Time"
    FROM
    (SELECT branch_id as "Branch", substring(stop_zip_postal_code, 1, 5) as 
    "Zip_5", count(stop_name) as "CountofStops", min(actual_arrival_time) as 
    "EarlyTime"

    FROM distribution_stop_information

    WHERE company_no = '001' AND route_date > '3/13/2017'

    GROUP BY branch_id, stop_zip_postal_code)
    inner

    GROUP BY "Branch", "Zip_5"

    ORDER BY Zip_5

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

ERROR: subquery in FROM must have an alias
SQL state: 42601
Hint: For example, FROM (SELECT ...) [AS] foo.

juergen d · Accepted Answer · 2017-05-15 19:58:22Z

2

inner is a reserved keyword. Use another name as alias.

answered May 15, 2017 at 19:58

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Emac Over a year ago

D'oh! Easy enough of a solution! Why didn't I think of that? Thanks.

Gordon Linoff · Accepted Answer · 2017-05-15 20:08:23Z

0

inner . . . think "inner join". You need a better alias than that.

SELECT Branch, Zip_5, CountofStops, avg(EarlyTime) As Average_Arrival_Time
FROM (SELECT branch_id as Branch, left(stop_zip_postal_code, 5) as Zip_5, 
             count(stop_name) as CountofStops,
             min(actual_arrival_time) as EarlyTime
      FROM distribution_stop_information
      WHERE company_no = '001' AND route_date > '2017-03-13'
      GROUP BY branch_id, stop_zip_postal_code
     ) b
GROUP BY Branch, Zip_5
ORDER BY Zip_5;

Notes:

Don't wrap column names in double quotes unless needed. They are just superfluous.
Use standard formats for date constants.
LEFT() is a convenient shorthand for substring( . . ., 1, . . .)

answered May 15, 2017 at 20:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . "Branch" is defined in the subquery. If the double quotes are removed there they are not needed in the outer query.

Collectives™ on Stack Overflow

Postgresql "subquery in FROM must have an alias" error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related