SELECT * except nth column

Question

Is it possible to SELECT * but without n-th column, for example 2nd?

I have some view that have 4 and 5 columns (each has different column names, except for the 2nd column), but I do not want to show the second column.

SELECT * -- how to prevent 2nd column to be selected?
FROM view4
WHERE col2 = 'foo';

SELECT * -- how to prevent 2nd column to be selected?
FROM view5
WHERE col2 = 'foo';

without having to list all the columns (since they all have different column name).

Answer 1

The real answer is that you just can not practically (See LINK). This has been a requested feature for decades and the developers refuse to implement it. The best practice is to mention the column names instead of *. Using * in itself a source of performance penalties though.

However, in case you really need to use it, you might need to select the columns directly from the schema -> check LINK. Or as the below example using two PostgreSQL built-in functions: ARRAY and ARRAY_TO_STRING. The first one transforms a query result into an array, and the second one concatenates array components into a string. List components separator can be specified with the second parameter of the ARRAY_TO_STRING function;

SELECT 'SELECT ' ||
ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME='view4' AND
            COLUMN_NAME NOT IN ('col2')
    ORDER BY ORDINAL_POSITION
), ', ') || ' FROM view4';

where strings are concatenated with the standard operator ||. The COLUMN_NAME data type is information_schema.sql_identifier. This data type requires explicit conversion to CHAR/VARCHAR data type.

But that is not recommended as well, What if you add more columns in the long run but they are not necessarily required for that query? You would start pulling more column than you need.

What if the select is part of an insert as in

Insert into tableA (col1, col2, col3.. coln) Select everything but 2 columns FROM tableB

The column match will be wrong and your insert will fail.

It's possible but I still recommend writing every needed column for every select written even if nearly every column is required.

Conclusion:

Since you are already using a VIEW, the simplest and most reliable way is to alter you view and mention the column names, excluding your 2nd column..