7 ms·
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 t
by altdatathrow 6y ago
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.html https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...
- djeiasbsbo 6y agoJust 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...
- rossmohax 6y agoMaybe WHERE NOT EXIST (SELECT ...) can help?
- boudin 6y agoI think the question was about excluding columns, not rows. I don't know any way to do that personally.
- dmitryminkovsky 6y agoNever heard of such a thing but maybe a good question for SO.
- bmn__ 6y agoThe 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 6y agoThanks 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.
- lkbm 6y agoI'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.)
- atombender 6y agoPostgres 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-except-one-in-postgresql/ https://blog.jooq.org/2018/05/14/selecting-all-columns-excep...
- dmitryminkovsky 6y agoThat is good to know! I’ve only used this approach with PGSQL