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.

Answer 1

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

Answer 2

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

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