Index: FAQ.html =================================================================== RCS file: /cvsroot/pgsql/doc/src/FAQ/FAQ.html,v retrieving revision 1.360 retrieving revision 1.361 diff -c -c -r1.360 -r1.361 *** FAQ.html 5 Dec 2006 23:13:41 -0000 1.360 --- FAQ.html 11 Dec 2006 22:44:53 -0000 1.361 *************** *** 10,16 **** alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

!

Last updated: Tue Dec 5 18:13:32 EST 2006

Current maintainer: Bruce Momjian (bruce@momjian.us) --- 10,16 ---- alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

!

Last updated: Mon Dec 11 17:44:33 EST 2006

Current maintainer: Bruce Momjian (bruce@momjian.us) *************** *** 86,93 **** searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?
4.9) In a query, how do I detect if a field ! is NULL? How can I sort on whether a field is ! NULL or not?
4.10) What is the difference between the various character types?
4.11.1) How do I create a --- 86,93 ---- searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?
4.9) In a query, how do I detect if a field ! is NULL? How do I concatenate possible NULLs? ! How can I sort on whether a field is NULL or not?
4.10) What is the difference between the various character types?
4.11.1) How do I create a *************** *** 823,832 **** identical values that differ only in case. To force a particular case to be stored in the column, use a CHECK constraint or a trigger.

!

4.9) In a query, how do I detect if a field ! is NULL? How can I sort on whether a field is ! NULL or not?

You test the column with IS NULL and IS NOT NULL, like this:

--- 823,836 ---- identical values that differ only in case. To force a particular case to be stored in the column, use a CHECK constraint or a trigger.

! ! 4.9) In a query, how do I detect if a field ! is NULL? How do I concatenate possible NULLs? ! How can I sort on whether a field is NULL or not?
!

4.9) In a query, how do I detect if a field ! is NULL? How do I concatenate possible NULLs? ! How can I sort on whether a field is NULL or not?

You test the column with IS NULL and IS NOT NULL, like this:

*************** *** 837,842 **** --- 841,853 ---- WHERE col IS NULL; +

To concatentate with possible NULLs, use COALESCE(), + like this:

+ 
+    SELECT COALESCE(col1, '') || COALESCE(col2, '')
+    FROM tab
+
+

To sort by the NULL status, use the IS NULL and IS NOT NULL modifiers in your ORDER BY clause. Things that are true will sort higher than things that are false,