-----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
