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.

Tillett

Of course they don’t, because they select the values 1 and '1', respectively. – Disassociate

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. – Flowerless

Answer 1

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

Sanious

what if a column has no title, how do I do select then? – Tillett

@Alex can you give a concrete example of such a situation? – Sanious

It's useful in any ORDER BY, and can actually be a really big benefit if sorting by a complex and verbose expression. – Thorin

Indeces may be also used in GROUP BY clauses. – Strait

It is a good response, elucidative, but it is not correct. It is possible using an alias. Like @BernardoJerez said in the other answer. – Vinegarish

Submitted a revision to this answer to make it correct as originally noted by @felipe-mendes. – Anglophobe

Answer 2

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!!!!

Kennethkennett

How does this actually work? What is this type of functionality in postgres called? – Hilmahilt

It's only an alias... postgresql.org/docs/12/sql-select.html – Kennethkennett

Answer 3

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 ...

Resident