4

In the following statement (PostgreSQL 11):

=> SELECT c cost FROM tt;
ERROR:  syntax error at or near "cost"
LINE 1: SELECT c cost FROM tt;

I get an error. Adding parenthesis around the field expression doesn't help (SELECT (c) cost FROM tt;). But adding the AS keyword fixes it.

=> SELECT c AS cost FROM tt;
 cost 
------
    1
...

I realize that cost is a keyword, but my impression is that the AS keyword is optional.

From a language perspective, why is the AS keyword required (or helpful) here? Is the PostgreSQL behavior here standard or documented somewhere?

Are there any other cases when the AS keyword is required?

4

From the documentation:

In the SQL standard, the optional key word AS can be omitted before an output column name whenever the new column name is a valid column name (that is, not the same as any reserved keyword). PostgreSQL is slightly more restrictive: AS is required if the new column name matches any keyword at all, reserved or not

cost is a keyword, so as is required.

Also:

Recommended practice is to use AS or double-quote output column names, to prevent any possible conflict against future keyword additions.

... but my preference is to not quote & instead be consistent with using as.

  • dbfiddle.uk/… – ypercubeᵀᴹ May 14 at 17:03
  • Also, using AS reserved_keyword may lead into all kinds of trouble: dbfiddle.uk/… – ypercubeᵀᴹ May 14 at 17:07
  • And avoid using reserved words as table or column names. – Colin 't Hart May 14 at 18:24
  • @Colin'tHart, while I agree it can be difficult to foresee what might be a reserved word in the future. Personally, I try to use as descriptive identifiers as possible, i.e. course_code instead of just code. Not sure why but I genuinely dislike quoted identifiers. As for as I would like to use it more than I do in reality. I especially remember a query that I could not get my head around: "select ... from T outer union select ... from ..." It took a while figuring out what the outer union meant;-) – Lennart May 14 at 19:59
  • Worth adding (like the manual does): In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities. Hence my rule of thumb: never omit the AS keyword for column aliases, but omit it at will for table aliases. (And never use reserved keywords as identifiers.) – Erwin Brandstetter May 14 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.