1

I am learning SQL and was wondering about the use of single quotes and double quotes for field names.

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year_rank <= 10
   AND "group" LIKE '%Ludacris%'

Why does the removal of the double quote on group not work, but it accepts year with or without the quotes?

underscore_d
  • 6,309
  • 3
  • 38
  • 64
John Doe
  • 637
  • 2
  • 7
  • 14
  • 1
    Which database? I know PostgreSQL treats quoted and unquoted identifiers differently for example: unquoted are mapped to lower case whereas quoted preserve case and are case sensitive. (So effectively if you declared a column with quotes when you created the table you have to keep using quotes for it.) However that doesn't look like it'd be your problem here. – Rup Jan 10 '20 at 14:59
  • Am learning SQL through a website called MODE.Not sure what kind of SQL it does operate on... – John Doe Jan 10 '20 at 15:00
  • 2
    `group` is a reserved word so must be quoted if used as an identifier. `year_Whatever` is not, because `year` is part of a larger identifier, so it doesn't matter that there happens to be a reserved word called just `year`. – underscore_d Jan 10 '20 at 15:00
  • 2
    @Rup: which is pretty much what the SQL standard defines except that the SQL standard requires un-quoted identifiers to be folded to lower case –  Jan 10 '20 at 15:03
  • @a_horse_with_no_name Oh OK, thanks. I've only ever run into it when working with somebody else's PostgreSQL code. – Rup Jan 10 '20 at 15:04

1 Answers1

3

group is a reserved key and thus can not be used as a regular identifier.

Identifier that either contain illegal characters (e.g. a space) or are reserved keywords have to be quoted with double quotes. See for example the explanation in the Postgres manual

In the SQL standard those are referred to as "delimited identifiers", but the term "quoted identifier" is also very common.

Single quotes are only used for string values in SQL (although some DBMS products allow to use double quotes for strings as well and single quotes for identifiers, that is non-standard SQL)

Note that according to the SQL standard, quoted identifiers are case-sensitive, so "Group" and "GROUP" are two different names (again some DBMS products ignore this as well).

  • That seems to be the case. Does that mean I can use double quotes for all the fields that I need to access. In this case I would use "year" and all other fields as standard practise – John Doe Jan 10 '20 at 15:07
  • 1
    In theory yes, but I would strongly recommend to **never** use double quotes in SQL. That will make your life a lot easier in the long run. –  Jan 10 '20 at 15:08