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

Answer 2

-- my table with 2 rows and 4 columns 
DROP TABLE IF EXISTS t_target_table;
CREATE TEMP TABLE t_target_table as 
SELECT 1 as id, 1 as v1 ,2 as v2,3 as v3,4 as v4
UNION ALL 
SELECT 2 as id, 5 as v1 ,-6 as v2,7 as v3,8 as v4
;

-- my computation and stuff that i have to messure, any logic could be done here !
DROP TABLE IF EXISTS t_processing;
CREATE TEMP TABLE t_processing as 
SELECT *, md5(t_target_table::text) as row_hash, case when v2 < 0 THEN true else false end as has_negative_value_in_v2
FROM t_target_table
;

-- now we want to insert that stuff into the t_target_table 

-- this is standard
-- INSERT INTO t_target_table (id, v1, v2, v3, v4) SELECT id, v1, v2, v3, v4 FROM t_processing;

-- this is andvanced ;-) 

INSERT INTO t_target_table 
-- the following row select only the columns that are pressent in the target table, and ignore the others.
SELECT r.* FROM (SELECT to_jsonb(t_processing) as d FROM t_processing) t JOIN LATERAL jsonb_populate_record(NULL::t_target_table, d) as r ON TRUE
;
-- WARNING : you need a object that represent the target structure, an exclusion of a single column is not possible