Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit051b52c

Browse files
committed
Add FAQ entry to mention using COALESCE() for concatenation of possible
NULLs.
1 parent9fa12dd commit051b52c

File tree

2 files changed

+30
-11
lines changed

2 files changed

+30
-11
lines changed

‎doc/FAQ‎

Lines changed: 13 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated:Tue Dec5 18:13:32 EST 2006
4+
Last updated:Mon Dec11 17:44:33 EST 2006
55

66
Current maintainer: Bruce Momjian (bruce@momjian.us)
77

@@ -59,8 +59,9 @@
5959
4.8) How do I perform regular expression searches and case-insensitive
6060
regular expression searches? How do I use an index for
6161
case-insensitive searches?
62-
4.9) In a query, how do I detect if a field is NULL? How can I sort on
63-
whether a field is NULL or not?
62+
4.9) In a query, how do I detect if a field is NULL? How do I
63+
concatenate possible NULLs? How can I sort on whether a field is NULL
64+
or not?
6465
4.10) What is the difference between the various character types?
6566
4.11.1) How do I create a serial/auto-incrementing field?
6667
4.11.2) How do I get the value of a SERIAL insert?
@@ -631,15 +632,22 @@
631632
upper and lowercase characters, it can not have identical values that
632633
differ only in case. To force a particular case to be stored in the
633634
column, use a CHECK constraint or a trigger.
635+
4.9) In a query, how do I detect if a field is NULL? How do I
636+
concatenate possible NULLs? How can I sort on whether a field is NULL
637+
or not?
634638

635-
4.9) In a query, how do I detect if a field is NULL? Howcan Isort on
636-
whether a field is NULL or not?
639+
4.9) In a query, how do I detect if a field is NULL? Howdo Iconcatenate
640+
possible NULLs? How can I sort onwhether a field is NULL or not?
637641

638642
You test the column with IS NULL and IS NOT NULL, like this:
639643
SELECT *
640644
FROM tab
641645
WHERE col IS NULL;
642646

647+
To concatentate with possible NULLs, use COALESCE(), like this:
648+
SELECT COALESCE(col1, '') || COALESCE(col2, '')
649+
FROM tab
650+
643651
To sort by the NULL status, use the IS NULL and IS NOT NULL modifiers
644652
in your ORDER BY clause. Things that are true will sort higher than
645653
things that are false, so the following will put NULL entries at the

‎doc/src/FAQ/FAQ.html‎

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
alink="#0000ff">
1111
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1212

13-
<P>Last updated:Tue Dec 5 18:13:32 EST 2006</P>
13+
<P>Last updated:Mon Dec11 17:44:33 EST 2006</P>
1414

1515
<P>Current maintainer: Bruce Momjian (<Ahref=
1616
"mailto:bruce@momjian.us">bruce@momjian.us</A>)
@@ -86,8 +86,8 @@ <H2 align="center">Operational Questions</H2>
8686
searches and case-insensitive regular expression searches? How do I
8787
use an index for case-insensitive searches?<BR>
8888
<Ahref="#item4.9">4.9</A>) In a query, how do I detect if a field
89-
is<SMALL>NULL</SMALL>? Howcan Isort on whether a field is<SMALL>
90-
NULL</SMALL> or not?<BR>
89+
is<SMALL>NULL</SMALL>? Howdo Iconcatenate possible<SMALL>NULL</SMALL>s?
90+
How can I sort on whether a field is<SMALL>NULL</SMALL> or not?<BR>
9191
<Ahref="#item4.10">4.10</A>) What is the difference between the
9292
various character types?<BR>
9393
<Ahref="#item4.11.1">4.11.1</A>) How do I create a
@@ -823,10 +823,14 @@ <H3 id="item4.8">4.8) How do I perform regular expression
823823
identical values that differ only in case. To force a particular
824824
case to be stored in the column, use a<SMALL>CHECK</SMALL>
825825
constraint or a trigger.</P>
826-
826+
827+
<Ahref="#item4.9">4.9</A>) In a query, how do I detect if a field
828+
is<SMALL>NULL</SMALL>? How do I concatenate possible<SMALL>NULL</SMALL>s?
829+
How can I sort on whether a field is<SMALL> NULL</SMALL> or not?<BR>
830+
827831
<H3id="item4.9">4.9) In a query, how do I detect if a field
828-
is<SMALL>NULL</SMALL>? Howcan Isort on whether a field is<SMALL>
829-
NULL</SMALL> or not?</H3>
832+
is<SMALL>NULL</SMALL>? Howdo Iconcatenate possible<SMALL>NULL</SMALL>s?
833+
How can I sort on whether a field is<SMALL>NULL</SMALL> or not?</H3>
830834

831835
<P>You test the column with<SMALL>IS NULL</SMALL> and<SMALL>IS
832836
NOT NULL</SMALL>, like this:</P>
@@ -837,6 +841,13 @@ <H3 id="item4.9">4.9) In a query, how do I detect if a field
837841
WHERE col IS NULL;
838842
</PRE>
839843

844+
<P>To concatentate with possible<SMALL>NULL</SMALL>s, use<I>COALESCE()</I>,
845+
like this:</P>
846+
<PRE>
847+
SELECT COALESCE(col1, '') || COALESCE(col2, '')
848+
FROM tab
849+
</PRE>
850+
840851
<P>To sort by the<SMALL>NULL</SMALL> status, use the<SMALL>IS NULL</SMALL>
841852
and<SMALL>IS NOT NULL</SMALL> modifiers in your<SMALL>ORDER BY</SMALL> clause.
842853
Things that are<I>true</I> will sort higher than things that are<I>false</I>,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp