Refer to a column by its number (index)

Question

I want to perform a select using an index (number) of a column. I tried:

select 1 from "user"
select '1' from "user"

but they don't do what I expect.

Of course they don’t, because they select the _values_ `1` and `'1'`, respectively. — CBroe, May 18 '14 at 05:58
While it may not be literally true, you have to assume that the table columns have no fixed order, so such a request would be poorly defined. If you need this, it seems likely that your structure needs to be rethought. — Dave, May 18 '14 at 09:20

Mureinik · Accepted Answer · 2014-05-18T12:48:29.393

20

You cannot do this in postgres. Selecting a literal (such as 1 or '1') would just return its value. The only place such indices are allowed is in the order by clause, and even there, they don't relate to the order of columns in the table, but in the select list:

SELECT   col1, col2, col3
FROM     my_table
ORDER BY 1

EDIT:
One caveat is using order by <some index> in conjunction with select *, which may seem as though the index related to the order of columns in the table. However, the * is first expanded to contain all columns, and only then is the order by clause applied. So ultimately, it does refer to the select list and not to the actual structure of the table.

EDIT2:
As @klin mentioned, postgres' group by clause also allows indices:

SELECT   col1, COUNT(*)
FROM     my_table
GROUP BY 1

edited May 18 '14 at 12:48

answered May 18 '14 at 05:57

Mureinik

297,002
52
306
350

1

what if a column has no title, how do I do select then? – Incerteza May 18 '14 at 06:03
@Alex can you give a concrete example of such a situation? – Mureinik May 18 '14 at 06:05
It's useful in any `ORDER BY`, and can actually be a really big benefit if sorting by a complex and verbose expression. – Craig Ringer May 18 '14 at 09:10
Indeces may be also used in `GROUP BY` clauses. – klin May 18 '14 at 12:37
1

It is a good response, elucidative, but it is not correct. It is possible using an alias. Like @BernardoJerez said in the other answer. – Filipe Mendes May 28 '21 at 15:41

score 15 · Answer 2 · answered Oct 26 '18 at 20:48

15

When i need the FIRST column (and i don't know the column name) , i do something like this:

SELECT uno FROM _your_table_ as t(uno);

Just use an alias for table and columns!..

if you REALLY NEED the NUMBER 1 you could write something like this:

SELECT "1" FROM _your_table_ as t("1");

double quotes... ugly, but works!!!!

answered Oct 26 '18 at 20:48

Bernardo Jerez

898
1
8
8

How does this actually work? What is this type of functionality in postgres called? – rup Dec 08 '20 at 18:56
It's only an alias... https://www.postgresql.org/docs/12/sql-select.html – Bernardo Jerez Dec 11 '20 at 12:58

score 3 · Answer 3 · edited Apr 02 '19 at 18:09

There are several ways, to do this in PostgreSQL. The easiest one (I think) is creating a "function". Dig into the PG manual for working examples to start with. It's quite easy. You can choose from various languages. The lines here are for pl/pgsql but you'll get the idea:

First you retrieve the column name from the system catalog, doing something like this:

attname := select distinct attname from pg_attribute,pg_class where attrelid = pg_class.oid and attnum = 1 and pg_class.relname='user';

Then you concatenate this into a SQL statement:

EXECUTE 'SELECT ' || attname || ' FROM ...

Refer to a column by its number (index)

3 Answers3