Обсуждение: coalesce
http://www.postgresql.org/docs/9.1/static/functions-conditional.html
states: "The
COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null."I was having a terrible time understanding this, and looking at the two examples were just as confusing I could not get it to work right.
not sure if others have had the same difficulty, but here are my suggested adjustments:
Then on someones website this made perfect sense to me:
" If the results of the first argument are null, it will return the second."
so I made this example that makes sense:
COALESCE(column,substitute value)
Henry Drexler <alonup8tb@gmail.com> wrote: > "If the results of the first argument are null, it will return the > second." Unless the second is also null, in which case it will return the third. Unless the third is also null... The trick is to come up with language which recognizes that there can be any number of arguments. Personally, I think the existing language does a good job of that, and is pretty clear. > so I made this example that makes sense: > > COALESCE(column,substitute value) Perhaps a two-argument example would be helpful, as long as it doesn't mislead people into thinking the construct is limited to two arguments. -Kevin
thanks,
so I take it I am not getting those more complex examples (that are apparently beyond my use case), I will take a look at them again.
Thank you for the feedback.
On Wed, Sep 21, 2011 at 10:33 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Henry Drexler <alonup8tb@gmail.com> wrote:Unless the second is also null, in which case it will return the
> "If the results of the first argument are null, it will return the
> second."
third. Unless the third is also null...
The trick is to come up with language which recognizes that there
can be any number of arguments. Personally, I think the existing
language does a good job of that, and is pretty clear.Perhaps a two-argument example would be helpful, as long as it
> so I made this example that makes sense:
>
> COALESCE(column,substitute value)
doesn't mislead people into thinking the construct is limited to two
arguments.
-Kevin
Excerpts from Henry Drexler's message of mié sep 21 12:50:18 -0300 2011: > thanks, > > so I take it I am not getting those more complex examples (that are > apparently beyond my use case), I will take a look at them again. Maybe the fix is just to add a simpler example, without altering the wording (which seems pretty clear to me as well, but then I already know what COALESCE does). -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
>
> Excerpts from Henry Drexler's message of mié sep 21 12:50:18 -0300 2011:
> > thanks,
> >
> > so I take it I am not getting those more complex examples (that are
> > apparently beyond my use case), I will take a look at them again.
>
> Maybe the fix is just to add a simpler example, without altering the
> wording (which seems pretty clear to me as well, but then I already know
> what COALESCE does).
The current example is:
SELECT COALESCE(description, short_description, '(none)') ...
What would a simpler one look like?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Alvaro Herrera wrote: > > Excerpts from Henry Drexler's message of mié sep 21 12:50:18 -0300 2011: > > thanks, > > > > so I take it I am not getting those more complex examples (that are > > apparently beyond my use case), I will take a look at them again. > > Maybe the fix is just to add a simpler example, without altering the > wording (which seems pretty clear to me as well, but then I already know > what COALESCE does). Here is a doc patch that at least explains the example. Applied. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index 0b6a109..2f307c2 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** SELECT ... WHERE CASE WHEN x <> 0 *** 10015,10020 **** --- 10015,10022 ---- <programlisting> SELECT COALESCE(description, short_description, '(none)') ... </programlisting> + This returns <varname>description</> if it is not null, or + <varname>short_description</> if it is not null, or <literal>(none)</>. </para> <para>
Bruce Momjian <bruce@momjian.us> writes:
> Here is a doc patch that at least explains the example. Applied.
I think s/or/otherwise/ (both places) would make that read better.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Here is a doc patch that at least explains the example. Applied. > > I think s/or/otherwise/ (both places) would make that read better. Done with the attached patch. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index 2f307c2..bedd8ba *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** SELECT ... WHERE CASE WHEN x <> 0 *** 10015,10022 **** <programlisting> SELECT COALESCE(description, short_description, '(none)') ... </programlisting> ! This returns <varname>description</> if it is not null, or ! <varname>short_description</> if it is not null, or <literal>(none)</>. </para> <para> --- 10015,10022 ---- <programlisting> SELECT COALESCE(description, short_description, '(none)') ... </programlisting> ! This returns <varname>description</> if it is not null, otherwise ! <varname>short_description</> if it is not null, otherwise <literal>(none)</>. </para> <para>
That is certainly clearer - thank you.
On Tue, Oct 4, 2011 at 10:39 PM, Bruce Momjian <bruce@momjian.us> wrote:
Done with the attached patch.Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Here is a doc patch that at least explains the example. Applied.
>
> I think s/or/otherwise/ (both places) would make that read better.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +