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

Question

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;

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

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:

Select first row in each GROUP BY group?

edited May 23 '17 at 11:44

Community

1
1

answered Jul 06 '14 at 13:30

Erwin Brandstetter

605,456
145
1,078
1,228

thank you.can we say ONLY this two Command use this type of referencing? – CoderInNetwork Jul 06 '14 at 13:41
@CoderInNetwork: You might be interested in the added `DISTINCT ON`. – Erwin Brandstetter Jan 15 '15 at 04:44

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

1 Answers1

Linked