From: | Josef Šimánek <josef(dot)simanek(at)gmail(dot)com> |
---|---|
To: | Miles Elam <miles(dot)elam(at)productops(dot)com> |
Cc: | Stanislav Motyčka <stanislav(dot)motycka(at)gmail(dot)com>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]] |
Date: | 2020-02-25 14:41:07 |
Message-ID: | CAFp7QwpCNq_wuXwaOOjvcaRjC3vVTQZrifbb3EHpPv9ZWiVz+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
út 25. 2. 2020 v 15:35 odesílatel Miles Elam <miles(dot)elam(at)productops(dot)com>
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(dot)motycka(at)gmail(dot)com> 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
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Miles Elam | 2020-02-25 14:50:34 | Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]] |
Previous Message | Laurenz Albe | 2020-02-25 14:35:45 | Re: Upgrade to 12.2 using same data directory |