Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Are there any restrictions on Postgres column alias names?

Are there any restrictions, in terms of length, ability to include non-ASCII characters, etc. on the name of a Postgres column alias? And have there been any changes to such restrictions from version 8.1 to the present?

like image 364
DNS Avatar asked Mar 11 '11 19:03

DNS


2 Answers

What a_horse_with_no_name said. In general, you can see some earlier versions of the documentation by replacing "current" in the URL with the version number. But the documentation for 8.1 is in the manual archive.

8.1

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use may render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

9.4

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

No change.

Here is the current version of this documentation. It might have changed after I wrote this answer.

like image 163
Mike Sherrill 'Cat Recall' Avatar answered Oct 20 '22 05:10

Mike Sherrill 'Cat Recall'


The rules for a column alias are no different to than those for regular column names.

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Key words and unquoted identifiers are case insensitive.

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Quoted identifiers can contain any character, except the character with code zero.

like image 42
a_horse_with_no_name Avatar answered Oct 20 '22 07:10

a_horse_with_no_name