Search code examples
sqlpostgresqlidentifier

When can we use an identifier number instead of its name in PostgreSQL?

I am developing a software need to remove user provided numbers but unfortunately some elements like column names (unrelated to user input) also can be represent by numbers. So I am looking for a complete reference to know when a column name or (maybe table name) can represented in a query by an id.

For example in the query below this situation is established:

 select * from first group by 1,2,3;
Solution

  • Positional references to output columns are valid in the GROUP BY and ORDER BY clauses of a SELECT statement.

    GROUP BY Clause
    expression can be an input column name, or the name or ordinal number of an output column (SELECT list item)

    ORDER BY Clause
    Each expression can be the name or ordinal number of an output column (SELECT list item)

    And also DISTINCT ON:

    The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY.

    Example: