-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
Added a new question about sorting with a NULL. Also some minor
cleanups, especially in regards to some of the http links: is
some automatic tool messing these up somehow? (many had spaces
and odd breaks in the middle)
 
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200502222021
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFCG9qnvJuQZxSWSsgRAnNXAKC8gPJ3m3kuHjfUZxOTLPGqDZ/S6ACguXQT
vKrfqPdiL1yZ9HHt8j0tRBc=
=9cAH
-----END PGP SIGNATURE-----

Index: FAQ
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/FAQ,v
retrieving revision 1.331
diff -c -r1.331 FAQ
*** FAQ	15 Feb 2005 04:35:30 -0000	1.331
--- FAQ	23 Feb 2005 01:16:56 -0000
***************
*** 79,84 ****
--- 79,85 ----
     4.20) Why do I get "missing oid" errors when accessing temporary
     tables in PL/PgSQL functions?
     4.21) What encryption options are available?
+    4.22) How can I sort on whether a field is NULL or not?
     
                              Extending PostgreSQL
                                        
***************
*** 119,125 ****
     PostgreSQL Data Base Management System
     
     Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
!    Portions Copyright (c) 1994-6 Regents of the University of California
     
     Permission to use, copy, modify, and distribute this software and its
     documentation for any purpose, without fee, and without a written
--- 120,126 ----
     PostgreSQL Data Base Management System
     
     Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
!    Portions Copyright (c) 1994-1996 Regents of the University of California
     
     Permission to use, copy, modify, and distribute this software and its
     documentation for any purpose, without fee, and without a written
***************
*** 152,166 ****
     http://pgfoundry.org/projects/pginstaller. MSDOS-based versions of
     Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.
     
!    There is also a Novell Netware 6 port at http://forge.novell.com, and
!    an OS/2 (eComStation) version at
!    http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgre
!    SQL&stype=all&sort=type&dir=%2F.
     
      1.4) Where can I get PostgreSQL?
      
     The primary anonymous ftp site for PostgreSQL is
!    ftp://ftp.PostgreSQL.org/pub. For mirror sites, see our main web site.
     
      1.5) Where can I get support?
      
--- 153,166 ----
     http://pgfoundry.org/projects/pginstaller. MSDOS-based versions of
     Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.
     
!    There is also a Novell Netware 6 port at http://forge.novell.com, 
!    and an OS/2 (eComStation) version at
!    http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&key=postgresql+port
     
      1.4) Where can I get PostgreSQL?
      
     The primary anonymous ftp site for PostgreSQL is
!    ftp://ftp.PostgreSQL.org/pub/. For mirror sites, see our main web site.
     
      1.5) Where can I get support?
      
***************
*** 177,183 ****
     EFNet.
     
     A list of commercial support companies is available at
!    http://techdocs.postg resql.org/companies.php.
     
      1.6) How do I submit a bug report?
      
--- 177,183 ----
     EFNet.
     
     A list of commercial support companies is available at
!    http://techdocs.postgresql.org/companies.php.
     
      1.6) How do I submit a bug report?
      
***************
*** 318,333 ****
     For Web integration, PHP (http://www.php.net) is an excellent
     interface.
     
!    For complex cases, many use the Perl and CGI.pm or mod_perl.
     
      2.3) Does PostgreSQL have a graphical user interface?
      
     Yes, there are several graphical interfaces to PostgreSQL available.
     These include pgAdmin III (http://www.pgadmin.org, PgAccess
!    http://www.pgaccess.org), RHDB Admin (http://sources.redhat.com/rhd b/
!    ), TORA (http://www.globecom.net/tora/, partly commercial), and Rekall
!    ( http://www.rekallrevealed.org/). There is also PhpPgAdmin (
!    http://phppgadmin.sourceforge.net/ ), a web-based interface to
     PostgreSQL.
     
     See http://techdocs.postgresql.org/guides/GUITools for a more detailed
--- 318,333 ----
     For Web integration, PHP (http://www.php.net) is an excellent
     interface.
     
!    For complex cases, many use Perl and DBD::Pg with CGI.pm or mod_perl.
     
      2.3) Does PostgreSQL have a graphical user interface?
      
     Yes, there are several graphical interfaces to PostgreSQL available.
     These include pgAdmin III (http://www.pgadmin.org, PgAccess
!    (http://www.pgaccess.org), RHDB Admin (http://sources.redhat.com/rhdb/),
!    TORA (http://www.globecom.net/tora/, partly commercial), and Rekall
!    (http://www.rekallrevealed.org/). There is also PhpPgAdmin 
!    (http://phppgadmin.sourceforge.net/), a web-based interface to
     PostgreSQL.
     
     See http://techdocs.postgresql.org/guides/GUITools for a more detailed
***************
*** 353,384 ****
     There are three major areas for potential performance improvement:
     
     Query Changes
!           This involves modifying queries to obtain better performance:
            
!           + Creation of indexes, including expression and partial indexes
!           + Use of COPY instead of multiple INSERTs
!           + Grouping of multiple statements into a single transaction to
!             reduce commit overhead
!           + Use of CLUSTER when retrieving many rows from an index
!           + Use of LIMIT for returning a subset of a query's output
!           + Use of Prepared queries
!           + Use of ANALYZE to maintain accurate optimizer statistics
!           + Regular use of VACUUM or pg_autovacuum
!           + Dropping of indexes during large data changes
              
     Server Configuration
!           A number of postgresql.conf settings affect performance. For
!           more details, see Administration Guide/Server Run-time
!           Environment/Run-time Configuration for a full listing, and for
!           commentary see
!           http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_co
!           nf_e.html and
!           http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
            
     Hardware Selection
!           The effect of hardware on performance is detailed in
!           http://candle.pha.pa.us/main/writings/pgsql/hw_performance/inde
!           x.html and http://www.powerpostgresql.com/PerfList/.
            
      3.4) What debugging features are available?
      
--- 353,385 ----
     There are three major areas for potential performance improvement:
     
     Query Changes
!       This involves modifying queries to obtain better performance:
            
!       + Creation of indexes, including expression and partial indexes
!       + Use of COPY instead of multiple INSERTs
!       + Grouping of multiple statements into a single transaction to
!         reduce commit overhead
!       + Use of CLUSTER when retrieving many rows from an index
!       + Use of LIMIT for returning a subset of a query's output
!       + Use of Prepared queries
!       + Use of ANALYZE to maintain accurate optimizer statistics
!       + Regular use of VACUUM or pg_autovacuum
!       + Dropping of indexes during large data changes
              
     Server Configuration
!       A number of postgresql.conf settings affect performance. For
!       more details, see Administration Guide/Server Run-time
!       Environment/Run-time Configuration for a full listing, and for
!       commentary see
!       http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
!       and
!       http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
            
     Hardware Selection
!       The effect of hardware on performance is detailed in
!       http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html
!       and
!       http://www.powerpostgresql.com/PerfList/.
            
      3.4) What debugging features are available?
      
***************
*** 637,643 ****
      4.9) In a query, how do I detect if a field is NULL?
      
     You test the column with IS NULL and IS NOT NULL.
!    
      4.10) What is the difference between the various character types?
      
          Type    Internal Name                    Notes
--- 638,648 ----
      4.9) In a query, how do I detect if a field is NULL?
      
     You test the column with IS NULL and IS NOT NULL.
! 
!    SELECT *
!    FROM tab
!    WHERE col IS NULL;
! 
      4.10) What is the difference between the various character types?
      
          Type    Internal Name                    Notes
***************
*** 829,834 ****
--- 834,851 ----
       * Database user passwords are automatically encrypted when stored in
         the system tables.
       * The server can run using an encrypted file system.
+ 
+ 
+    4.22) How can I sort on whether a field is NULL or not?
+ 
+    You can use the IS NULL and IS NOT NULL modifiers in your WHERE clause. 
+    Things that are "true" will sort higher than things that are "false", 
+    so the following will put NULL entries at the top of the resulting list:
+ 
+    SELECT *
+    FROM tab
+    ORDER BY (col IS NOT NULL)
+ 
       _________________________________________________________________
     
                              Extending PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to