Foo meaning in postgresql

Question

I heard that foo is used as a table alias but why is it needed? ex. When I try to run the code on pgadmin4 it doesn work without the foo and says that the subquery inside From must have an alias. ( Think my knowledge with the whole alias is lacking behind )

`

SELECT pcode,
       district_name
  FROM (SELECT pcode,
               district_name,
               CASE WHEN pcode = '4130033' THEN 0
                    WHEN pcode LIKE '413003%' THEN 1
                    WHEN pcode LIKE '41300%'  THEN 2
                    WHEN pcode LIKE '4130%'   THEN 3
                    WHEN pcode LIKE '413%'    THEN 4
                    WHEN pcode LIKE '41%'     THEN 5
                    WHEN pcode LIKE '4%'      THEN 6
                    ELSE NULL END AS hit_code,
               MIN(CASE WHEN pcode = '4130033' THEN 0
                        WHEN pcode LIKE '413003%' THEN 1
                        WHEN pcode LIKE '41300%'  THEN 2
                        WHEN pcode LIKE '4130%'   THEN 3
                        WHEN pcode LIKE '413%'    THEN 4
                        WHEN pcode LIKE '41%'     THEN 5
                        WHEN pcode LIKE '4%'      THEN 6
                        ELSE NULL END) 
                OVER(ORDER BY CASE WHEN pcode = '4130033' THEN 0
                                   WHEN pcode LIKE '413003%' THEN 1
                                   WHEN pcode LIKE '41300%'  THEN 2
                                   WHEN pcode LIKE '4130%'   THEN 3
                                   WHEN pcode LIKE '413%'    THEN 4
                                   WHEN pcode LIKE '41%'     THEN 5
                                   WHEN pcode LIKE '4%'      THEN 6
                                   ELSE NULL END) AS min_code
          FROM PostalCode) Foo
 WHERE hit_code = min_code;

`

Tried without the Foo and didnt work out

An explanation of what you are trying to achieve would help your question. — Tim Biegeleisen, Dec 17 '22 at 08:36
You should search internet for more on aliases eg https://en.wikipedia.org/wiki/Alias_(SQL) and it's a rule that sub queries must be aliased.. — P.Salmon, Dec 17 '22 at 08:36
But aside from that you aren't using any of the columns created in the sub query in the main select..WHY?? — P.Salmon, Dec 17 '22 at 08:39
"*but why is it needed*" - because this is what the SQL syntax rules require. In some databases (e.g. Oracle) you _could_ leave it out, but not in Postgres — , Dec 17 '22 at 10:29

score 1 · Answer 1 · answered Dec 17 '22 at 17:28

1

It wants to be able to resolve any potential ambiguity in the column names (which requires an alias to do), even if there is no actual ambiguity present.

Note that in v16, the alias will longer be needed if there is no ambiguity.

answered Dec 17 '22 at 17:28

jjanes

37,812
5
27
34

Foo meaning in postgresql

1 Answers1