[djeiasbsbo]

Just in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like:

SELECT * EXCEPT FOO_ID FROM FOO;

I have always wanted this but have never vome across it...

[bmn__]

The correct answer is to just apply a view and enumerate the wanted column names once.

Otherwise, dynamic column names (that's your search engine fodder) require introspection…

    -- "Chinook" sample database
    select column_name
    from information_schema.columns
    where table_name = 'Track'
    except
    select 'UnitPrice'

… coupled with the moral equivalent of PL/pgSQL `execute` (i.e. run-time eval) statement.

[djeiasbsbo]

Thanks for answering! Currently I do use views but it's just something I had wondered about.

I mean, the information from the information_schema must be updated anyway when one deletes a column or table, so I thought maybe a function like that which looks it up could exist.

I will try with PL/pgSQL, have long wanted to familiarise myself with it anyway.

[atombender]

Postgres has a clever trick [1] you can use, though I wouldn't say it's something I would ever use.

[1] https://blog.jooq.org/2018/05/14/selecting-all-columns-excep...

[lkbm]

I've also wanted this on Postgres for years.

If there's subset of rows you frequently want, you may just be able to define a view and use that. (At one point, I defined a text macro in my terminal to list the fields I usually wanted on our "orders" table.)

[dmitryminkovsky]

Never heard of such a thing but maybe a good question for SO.

[rossmohax]

Maybe

WHERE NOT EXIST (SELECT ...)

can help?

[boudin]

I think the question was about excluding columns, not rows.

I don't know any way to do that personally.