9

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group".

I tried creating it but it always throw a syntax error near the word = "group". I am really curious since the keyword in SQL is "group by" not group, so what is the reason, I cannot rename or create a column with a name "group".

Syntax and error I am using and getting:

ALTER TABLE test RENAME COLUMN sum TO group;
ERROR:  syntax error at or near "group"
LINE 1: ALTER TABLE test RENAME COLUMN sum TO group;
Paul White
  • 94,921
  • 30
  • 437
  • 687
AKIWEB
  • 625
  • 2
  • 6
  • 8

2 Answers2

15

group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier.

To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier.

ALTER TABLE test RENAME COLUMN sum TO "group";

Note that when using quoted identifiers, you need to always quote it. And it becomes case sensitive. "group" is a different column name than "GROUP".

For more details please see the manual.

In general it is a very bad idea to use names that require quoting. It will save you a lot of trouble if you can come up with a different name that does not require quoting.

mustaccio
  • 28,207
  • 24
  • 60
  • 76
0

If you are trying to use a reserved keyword as a column name make sure you use the inverted qoute. Sometimes double qoutes wont work in MYSQL.

keep group in the query within `

` is present on the left top of the keyboard. same key as tilde (~)

Harbir Basi
  • 11