Postgresql – Can column name be “Group” in Postgresql or in any databases

group bypostgresqlpostgresql-9.2

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".

Any learning curve on this would be really helpful.

Thanks!

{EDIT: 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;

Best Answer

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 idenfifiers, 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: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

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.

Related Question