Search Postgresql Archives

Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





út 25. 2. 2020 v 15:35 odesílatel Miles Elam <miles.elam@xxxxxxxxxxxxxx> napsal:
How do you see this syntax working in a JOIN query?

SELECT x.* EXCEPT x.col1, x.col2, y.col1
FROM tablex AS x
  LEFT JOIN tabley AS y;

The column(s) you want to exclude become ambiguous.

Can you explain how are those column(s) ambiguous in your example? I would expect to select everything from table x (as SELECT x.* should do) except x.col1 and x.col2. Nothing is selected from table y thus y.col1 is not relevant here (the question is if this is problem or not - raise, ignore?).
 
Parentheses?

SELECT x.* EXCEPT (x.col1, x.col2), y.col1
FROM tablex AS x
  LEFT JOIN tabley AS y;

Could work, but this is encouraging the use of the wildcard selector, which I'm not sure is a productive or maintainable goal. In exchange for flexibility, you've added a non-trivial amount of comprehension complexity. I'm not a big fan of the wildcard selector except in the most trivial cases and even then only as part of development toward a final query with all columns specified. Then again I try not to have tables with hundreds of columns (or even tens in most cases), so my own use cases may bias me. Personally I just don't like queries where I cannot clearly see what it being returned to me. Anything that makes that ambiguity more popular will be viewed with a skeptical eye.


On Tue, Feb 25, 2020 at 2:18 AM Stanislav Motyčka <stanislav.motycka@xxxxxxxxx> wrote:
Hello,

Sometimes (for tables with many columns) it would be better and easier to write "SELECT" statement with clause "EXCEPT":
"SELECT * [EXCEPT col1 [,col2]] FROM ..."

It's easier to write "except" one or two columns from all (*) as to write names of all columns besides one or two.
What do you thin about it?

Best regards
Stano Motycka


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux