[altdatathrow]

fyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]:

> For row comparisons, (a, b) > (x, y) is equivalent to:

> (a > x) OR ((a = x) AND (b > y))

[1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...

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

[dmitryminkovsky]

That is good to know! I’ve only used this approach with PGSQL