Why Can't Database Table or Column Names Start with Numbers?

Table or Column Names Cannot Start with Numerics: The Underlying Reason

When attempting to create a table with a name like "15909434_user," one encounters the error "Table or column name cannot start with numeric." This raises the question of why this convention exists.

According to the PostgreSQL documentation, object names in the database follow the "name type," which requires them to start with a letter or underscore and consist of alphanumeric characters and underscores thereafter.

To circumvent this restriction, double quotes can be used to wrap the table name, transforming it into a "quoted identifier." However, it's important to note that the double quotes are not considered part of the name and do not count towards the 63-character limit.

The reason behind this convention stems from the original SQL standards. The "identifier start" block in the standards specifies that an identifier must primarily begin with a simple Latin letter. This rule simplifies the parsing process for SQL interpreters.

In the case of the SELECT-list clause, which allows for both simple column names and numeric expressions, enforcing this convention helps the parser differentiate between the two. For example, if table and column names were allowed to start with numerics, "2e2" could be interpreted as both a column name and a valid numeric expression.

By having the rule that identifiers start with letters, the parser can quickly discern that "2e2" is a numeric expression and "3.4" is a numeric value, rather than table or column names.

While devising a scheme to allow numeric leading characters would be possible, it could result in more obscure rules and syntactic complexities. The current convention provides a clear and straightforward solution to the issue, ensuring efficient parsing and avoiding potential ambiguities.

The above is the detailed content of Why Can't Database Table or Column Names Start with Numbers?. For more information, please follow other related articles on the PHP Chinese website!