From 8f2c03752bff0c47a617705dd96bd16a655b4e73 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 30 Jan 2005 04:22:53 +0000 Subject: Backpatch FAQ changes to 8.0.X. --- doc/src/FAQ/FAQ.html | 418 +++++++++++++++------------------------------------ 1 file changed, 121 insertions(+), 297 deletions(-) (limited to 'doc/src/FAQ/FAQ.html') diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index ec6a92b960a..d79d62c06e4 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -10,7 +10,7 @@ alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

-

Last updated: Wed Jan 19 14:45:22 EST 2005

+

Last updated: Sat Jan 29 23:20:03 EST 2005

Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
@@ -28,117 +28,100 @@

General Questions

1.1) What is PostgreSQL? How is it pronounced?
1.2) What is the copyright on PostgreSQL?
- 1.3) What Unix platforms does PostgreSQL run - on?
- 1.4) What non-Unix ports are available?
- 1.5) Where can I get PostgreSQL?
- 1.6) Where can I get support?
- 1.7) What is the latest release?
- 1.8) What documentation is available?
- 1.9) How do I find out about known bugs or + 1.3) What platforms does PostgreSQL support?
+ 1.4) Where can I get PostgreSQL?
+ 1.5) Where can I get support?
+ 1.6) What is the latest release?
+ 1.7) What documentation is available?
+ 1.8) How do I find out about known bugs or missing features?
- 1.10) How can I learn SQL?
- 1.11) Is PostgreSQL Y2K compliant?
- 1.12) How do I join the development team?
- 1.13) How do I submit a bug report?
- 1.14) How does PostgreSQL compare to other + 1.9) How can I learn SQL?
+ 1.10) How do I join the development team?
+ 1.11) How do I submit a bug report?
+ 1.12) How does PostgreSQL compare to other DBMSs?
- 1.15) How can I financially assist + 1.13) How can I financially assist PostgreSQL?

User Client Questions

- 2.1) Are there ODBC drivers for + 2.1) What interfaces are available for PostgreSQL?
2.2) What tools are available for using PostgreSQL with Web pages?
2.3) Does PostgreSQL have a graphical user interface?
- 2.4) What languages are available to - communicate with PostgreSQL?

Administrative Questions

3.1) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
- 3.2) When I start postmaster, I get a - Bad System Call or core dumped message. Why?
- 3.3) When I try to start postmaster, I - get IpcMemoryCreate errors. Why?
- 3.4) When I try to start postmaster, I - get IpcSemaphoreCreate errors. Why?
- 3.5) How do I control connections from other + 3.2) How do I control connections from other hosts?
- 3.6) How do I tune the database engine for + 3.3) How do I tune the database engine for better performance?
- 3.7) What debugging features are available?
- 3.8) Why do I get "Sorry, too many + 3.4) What debugging features are available?
+ 3.5) Why do I get "Sorry, too many clients" when trying to connect?
- 3.9) What is in the pgsql_tmp + 3.6) What is in the pgsql_tmp directory?
- 3.10) Why do I need to do a dump and restore + 3.7) Why do I need to do a dump and restore to upgrade PostgreSQL releases?
- 3.11) What computer hardware should I use?
+ 3.8) What computer hardware should I use?

Operational Questions

- 4.1) What is the difference between binary - cursors and normal cursors?
- 4.2) How do I SELECT only the + 4.1) How do I SELECT only the first few rows of a query? A random row?
- 4.3) How do I find out what tables, indexes, + 4.2) How do I find out what tables, indexes, databases, and users are defined? How do I see the queries used by psql to display them?
- 4.4) How do you remove a column from a + 4.3) How do you remove a column from a table, or change its data type?
- 4.5) What is the maximum size for a row, a + 4.4) What is the maximum size for a row, a table, and a database?
- 4.6) How much database disk space is required + 4.5) How much database disk space is required to store data from a typical text file?
- 4.7) My queries are slow or don't make use of - the indexes. Why?
- 4.8) How do I see how the query optimizer is + 4.6) Why are my queries slow? Why don't they + use my indexes?
+ 4.7) How do I see how the query optimizer is evaluating my query?
- 4.9) What is an R-tree index?
- 4.10) What is the Genetic Query Optimizer?
- 4.11) How do I perform regular expression + 4.8) How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?
- 4.12) In a query, how do I detect if a field + 4.9) In a query, how do I detect if a field is NULL?
- 4.13) What is the difference between the + 4.10) What is the difference between the various character types?
- 4.14.0) How do I create a + 4.11.0) How do I create a serial/auto-incrementing field?
- 4.14.1) How do I get the value of a + 4.11.1) How do I get the value of a SERIAL insert?
- 4.14.2) Doesn't currval() + 4.11.2) Doesn't currval() lead to a race condition with other users?
- 4.14.3) Why aren't my sequence numbers + 4.11.3) Why aren't my sequence numbers reused on transaction abort? Why are there gaps in the numbering of my sequence/SERIAL column?
- 4.15) What is an OID? What is a + 4.12) What is an OID? What is a TID?
- 4.16) What is the meaning of some of the terms + 4.13) What is the meaning of some of the terms used in PostgreSQL?
- 4.17) Why do I get the error "ERROR: Memory + 4.14) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
- 4.18) How do I tell what PostgreSQL version I + 4.15) How do I tell what PostgreSQL version I am running?
- 4.19) Why does my large-object operations get + 4.16) Why does my large-object operations get "invalid large obj descriptor"?
- 4.20) How do I create a column that will + 4.17) How do I create a column that will default to the current time?
- 4.21) Why are my subqueries using - IN so slow?
- 4.22) How do I perform an outer join?
- 4.23) How do I perform queries using multiple + 4.18) How do I perform an outer join?
+ 4.19) How do I perform queries using multiple databases?
- 4.24) How do I return multiple rows or columns + 4.20) How do I return multiple rows or columns from a function?
- 4.25) Why can't I reliably create/drop + 4.21) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?
- 4.26) What encryption options are available?
+ 4.22) What encryption options are available?

Extending PostgreSQL

@@ -225,16 +208,13 @@ It has no restrictions on how the source code may be used. We like it and have no intention of changing it.

-

1.3) What Unix platforms does PostgreSQL run - on?

+

1.3) What platforms does PostgreSQL support?

In general, any modern Unix-compatible platform should be able to run PostgreSQL. The platforms that had received explicit testing at the time of release are listed in the installation instructions.

-

1.4) What non-Unix ports are available?

-

Starting with version 8.0, PostgreSQL now runs natively on Microsoft Windows NT-based operating systems like Win2000, WinXP, and Win2003. A prepackaged installer is available at http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgreSQL&stype=all&sort=type&dir=%2F.

-

1.5) Where can I get PostgreSQL?

+

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.6) Where can I get support?

+

1.5) Where can I get support?

The main mailing list is: pgsql-general@PostgreSQL.org. @@ -316,13 +296,13 @@

A list of commercial support companies is available at http://techdocs.postgresql.org/companies.php.

-

1.7) What is the latest release?

+

1.6) What is the latest release?

The latest release of PostgreSQL is version 8.0.0.

We plan to have major releases every six to eight months.

-

1.8) What documentation is available?

+

1.7) What documentation is available?

Several manuals, manual pages, and some small test examples are included in the distribution. See the /doc directory. You @@ -346,14 +326,14 @@

Our web site contains even more documentation.

-

1.9) How do I find out about known bugs or +

1.8) How do I find out about known bugs or missing features?

PostgreSQL supports an extended subset of SQL-92. See our TODO list for known bugs, missing features, and future plans.

-

1.10) How can I learn +

1.9) How can I learn SQL?

The PostgreSQL book at The Complete Reference SQL, Groff et al., McGraw-Hill.

-

1.11) Is PostgreSQL Y2K compliant?

- -

Yes, we easily handle dates past the year 2000 AD, and before - 2000 BC.

- -

1.12) How do I join the development +

1.10) How do I join the development team?

First, download the latest source and read the PostgreSQL @@ -397,7 +372,7 @@ committers to keep up, and we had confidence that patches they committed were of high quality.

-

1.13) How do I submit a bug report?

+

1.11) How do I submit a bug report?

Visit the PostgreSQL bug form at @@ -407,7 +382,7 @@ "ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub to see if there is a more recent PostgreSQL version or patches.

-

1.14) How does PostgreSQL compare to other +

1.12) How does PostgreSQL compare to other DBMSs?

There are several ways of measuring software: features, @@ -460,7 +435,7 @@ community, manuals, and the source code often make PostgreSQL support superior to other DBMSs. There is commercial per-incident support available for those who need it. - (See FAQ section 1.6.)
+ (See FAQ section 1.5.)

@@ -473,7 +448,7 @@ -

1.15) How can I financially assist +

1.13) How can I financially assist PostgreSQL?

PostgreSQL has had a first-class infrastructure since we started @@ -503,26 +478,20 @@

User Client Questions

-

2.1) Are there ODBC drivers - for PostgreSQL?

- -

There are two ODBC drivers available, PsqlODBC - and OpenLink ODBC.

- -

You can download PsqlODBC from - http://gborg.postgresql.org/project/psqlodbc/projdisplay.php.

+

2.1) What interfaces are available for + PostgreSQL?

-

OpenLink ODBC can be gotten from http://www.openlinksw.com. It - works with their standard ODBC client software so - you'll have PostgreSQL ODBC available on every - client platform they support (Win, Mac, Unix, VMS).

+

The PostgreSQL install includes only the C and embedded + C interfaces. All other interfaces are independent projects + that are downloaded separately; being separate allows them to have their + own release schedule and development teams.

-

They will probably be selling this product to people who need - commercial-quality support, but a freeware version will always be - available. Please send questions to postgres95@openlink.co.uk.

+

Some programming languages like PHP include an + interface to PostgreSQL. Interfaces for languages like Perl, + TCL, Python, and many others are available at + http://gborg.postgresql.org + in the Drivers/Interfaces section and via Internet search. +

2.2) What tools are available for using PostgreSQL with Web pages?

@@ -552,34 +521,9 @@

See http://techdocs.postgresql.org/guides/GUITools for a more detailed list.

-

2.4) What languages are able to communicate with - PostgreSQL?

- -

Most popular programming languages contain an interface to - PostgreSQL. Check your programming language's list of extension - modules.

- -

The following interfaces are included in the PostgreSQL - distribution:

- - -

Additional interfaces are available at - http://gborg.postgresql.org - in the Drivers/Interfaces section. -

+

Administrative Questions

3.1) How do I install PostgreSQL somewhere @@ -588,47 +532,7 @@

Specify the --prefix option when running configure.

-

3.2) When I start postmaster, I get a - Bad System Call or core dumped message. Why?

- -

It could be a variety of problems, but first check to see that - you have System V extensions installed in your kernel. PostgreSQL - requires kernel support for shared memory and semaphores.

- -

3.3) When I try to start postmaster, I - get IpcMemoryCreate errors. Why?

- -

You either do not have shared memory configured properly in your - kernel or you need to enlarge the shared memory available in the - kernel. The exact amount you need depends on your architecture and - how many buffers and backend processes you configure for - postmaster. For most systems, with default numbers of - buffers and processes, you need a minimum of ~1 MB. See the PostgreSQL - Administrator's Guide/Server Run-time Environment/Managing Kernel Resources - section for more detailed information about shared memory and semaphores.

- -

3.4) When I try to start postmaster, I - get IpcSemaphoreCreate errors. Why?

- -

If the error message is IpcSemaphoreCreate: semget failed (No - space left on device) then your kernel is not configured with - enough semaphores. Postgres needs one semaphore per potential - backend process. A temporary solution is to start postmaster - with a smaller limit on the number of backend processes. Use - -N with a parameter less than the default of 32. A more - permanent solution is to increase your kernel's - SEMMNS and SEMMNI parameters.

- -

Inoperative semaphores can also cause crashes during heavy - database access.

- -

If the error message is something else, you might not have - semaphore support configured in your kernel at all. See the - PostgreSQL Administrator's Guide for more detailed information - about shared memory and semaphores.

- -

3.5) How do I control connections from other +

3.2) How do I control connections from other hosts?

By default, PostgreSQL only allows connections from the local @@ -638,7 +542,7 @@ host-based authentication by modifying the file $PGDATA/pg_hba.conf accordingly.

-

3.6) How do I tune the database engine for +

3.3) How do I tune the database engine for better performance?

Certainly, indexes can speed up queries. The @@ -677,7 +581,7 @@ data in tables to match an index. See the CLUSTER manual page for more details.

-

3.7) What debugging features are +

3.4) What debugging features are available?

PostgreSQL has several features that report status information @@ -735,7 +639,7 @@ file will be put in the client's current directory. Linux requires a compile with -DLINUX_PROFILE for proper profiling.

-

3.8) Why do I get "Sorry, too many +

3.5) Why do I get "Sorry, too many clients" when trying to connect?

You need to increase postmaster's limit on how many @@ -760,7 +664,7 @@ the number of allowed backend processes is so your system won't run out of resources.

-

3.9) What is in the pgsql_tmp directory?

+

3.6) What is in the pgsql_tmp directory?

This directory contains temporary files generated by the query executor. For example, if a sort needs to be done to satisfy an @@ -772,7 +676,7 @@ remain if a backend crashes during a sort. A stop and restart of the postmaster will remove files from those directories.

-

3.10) Why do I need to do a dump and restore +

3.7) Why do I need to do a dump and restore to upgrade between major PostgreSQL releases?

The PostgreSQL team makes only small changes between minor releases, @@ -788,7 +692,7 @@ The release notes mention whether pg_upgrade is available for the release.

-

3.11) What computer hardware should I use?

+

3.8) What computer hardware should I use?

Because PC hardware is mostly compatible, people tend to believe that all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and @@ -802,13 +706,7 @@

Operational Questions

-

4.1) What is the difference between binary - cursors and normal cursors?

- -

See the DECLARE manual page for a - description.

- -

4.2) How do I SELECT only the +

4.1) How do I SELECT only the first few rows of a query? A random row?

See the FETCH manual page, or use @@ -829,7 +727,7 @@ LIMIT 1; -

4.3) How do I find out what tables, indexes, +

4.2) How do I find out what tables, indexes, databases, and users are defined? How do I see the queries used by psql to display them?

@@ -850,7 +748,7 @@ illustrates many of the SELECTs needed to get information from the database system tables.

-

4.4) How do you remove a column from a +

4.3) How do you remove a column from a table, or change its data type?

DROP COLUMN functionality was added in release 7.3 @@ -881,7 +779,7 @@

You might then want to do VACUUM FULL tab to reclaim the disk space used by the expired rows.

-

4.5) What is the maximum size for a row, a +

4.4) What is the maximum size for a row, a table, and a database?

These are the limits:

@@ -907,7 +805,7 @@

The maximum table size and maximum number of columns can be quadrupled by increasing the default block size to 32k.

-

4.6) How much database disk space is required +

4.5) How much database disk space is required to store data from a typical text file?

A PostgreSQL database may require up to five times the disk @@ -944,8 +842,8 @@

NULLs are stored as bitmaps, so they use very little space.

-

4.7) My queries are slow or don't make use of - the indexes. Why?

+

4.6) Why are my queries slow? Why don't they + use my indexes?

Indexes are not automatically used by every query. Indexes are only used if the table is larger than a minimum size, and the query @@ -995,7 +893,7 @@ e.g. [a-e].

  • Case-insensitive searches such as ILIKE and ~* do not utilize indexes. Instead, use functional - indexes, which are described in section 4.11.
    • + indexes, which are described in section 4.10.
  • The default C locale must be used during initdb because it is not possible to know the next-greater character in a non-C locale. You can create a special @@ -1008,45 +906,12 @@ types exactly match the index's column types. This is particularly true of int2, int8, and numeric column indexes.

    -

    4.8) How do I see how the query optimizer is +

    4.7) How do I see how the query optimizer is evaluating my query?

    See the EXPLAIN manual page.

    -

    4.9) What is an R-tree index?

    - -

    An R-tree index is used for indexing spatial data. A hash index - can't handle range searches. A B-tree index only handles range - searches in a single dimension. R-trees can handle - multi-dimensional data. For example, if an R-tree index can be - built on an attribute of type point, the system can more - efficiently answer queries such as "select all points within a - bounding rectangle."

    - -

    The canonical paper that describes the original R-tree design - is:

    - -

    Guttman, A. "R-trees: A Dynamic Index Structure for Spatial - Searching." Proceedings of the 1984 ACM SIGMOD Int'l Conf on Mgmt - of Data, 45-57.

    - -

    You can also find this paper in Stonebraker's "Readings in - Database Systems".

    - -

    Built-in R-trees can handle polygons and boxes. In theory, - R-trees can be extended to handle higher number of dimensions. In - practice, extending R-trees requires a bit of work and we don't - currently have any documentation on how to do it.

    - -

    4.10) What is the Genetic Query - Optimizer?

    - -

    The GEQO module speeds query optimization when - joining many tables by means of a Genetic Algorithm (GA). It allows - the handling of large join queries through nonexhaustive - search.

    - -

    4.11) How do I perform regular expression +

    4.8) How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?

    @@ -1069,13 +934,13 @@ CREATE INDEX tabindex ON tab (lower(col)); -

    4.12) In a query, how do I detect if a field +

    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.13) What is the difference between the +

    4.10) What is the difference between the various character types?

     Type            Internal Name   Notes
@@ -1107,7 +972,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     particularly values that include NULL bytes. All the
     types described here have similar performance characteristics.
    
 
-    
    4.14.1) How do I create a
+    
    4.11.1) How do I create a
     serial/auto-incrementing field?
    
 
     
    PostgreSQL supports a SERIAL data type. It
@@ -1135,13 +1000,13 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     you need to use pg_dump's -o option or COPY
     WITH OIDS option to preserve the OIDs. 
 
-    
    4.14.2) How do I get the value of a
+    
    4.11.2) How do I get the value of a
     SERIAL insert?
    
 
     
    One approach is to retrieve the next SERIAL value
     from the sequence object with the nextval() function
     before inserting and then insert it explicitly. Using the
-    example table in 4.14.1, an example in a
+    example table in 4.11.1, an example in a
     pseudo-language would look like this:
    
 
         new_id = execute("SELECT nextval('person_id_seq')");
@@ -1164,7 +1029,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     new_id = execute("SELECT currval('person_id_seq')");
 
    
     
-    
    Finally, you could use the OID
+    
    Finally, you could use the OID
     returned from the INSERT statement to look up the
     default value, though this is probably the least portable approach,
     and the oid value will wrap around when it reaches 4 billion.
@@ -1172,13 +1037,13 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     available via $sth->{pg_oid_status} after
     $sth->execute().
    
 
-    
    4.14.3) Doesn't currval()
+    
    4.11.3) Doesn't currval()
     lead to a race condition with other users?
    
 
     
    No. currval() returns the current value assigned by your
     backend, not by all users.
    
 
-    
    4.14.4) Why aren't my sequence numbers
+    
    4.11.4) Why aren't my sequence numbers
     reused on transaction abort? Why are there gaps in the numbering of
     my sequence/SERIAL column?
    
 
@@ -1187,47 +1052,30 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     completes. This causes gaps in numbering from aborted
     transactions.
    
 
-    
    4.15) What is an OID? What is
+    
    4.12) What is an OID? What is
     a TID?
    
 
-    
    OIDs are PostgreSQL's answer to unique row ids.
-    Every row that is created in PostgreSQL gets a unique
-    OID. All OIDs generated during
-    initdb are less than 16384 (from
-    include/access/transam.h). All user-created
-    OIDs are equal to or greater than this. By default,
-    all these OIDs are unique not only within a table or
-    database, but unique within the entire PostgreSQL installation.
    
-
-    
    PostgreSQL uses OIDs in its internal system
-    tables to link rows between tables. These OIDs can
-    be used to identify specific user rows and used in joins. It is
-    recommended you use column type OID to store
-    OID values. You can create an index on the
-    OID field for faster access.
    
-
-    
    OIDs are assigned to all new rows from a central
-    area that is used by all databases. If you want to change the
-    OID to something else, or if you want to make a copy
-    of the table, with the original OIDs, there is no
-    reason you can't do it:
    
-
    -        CREATE TABLE new_table(mycol int);
-        SELECT oid AS old_oid, mycol INTO tmp_table FROM old_table;
-        COPY tmp_table TO '/tmp/pgtable';
-        COPY new_table WITH OIDS FROM '/tmp/pgtable';
-        DROP TABLE tmp_table;
-
    
-    
    OIDs are stored as 4-byte integers, and will
-    overflow at 4 billion. No one has reported this ever happening, and
-    we plan to have the limit removed before anyone does.
    
+    
    Every row that is created in PostgreSQL gets a unique
+    OID unless created WITHOUT OIDS.
+    OIDs are autotomatically assigned unique 4-byte
+    integers that are unique across the entire installation.  However,
+    they overflow at 4 billion, and then the OIDs start
+    being duplicated. PostgreSQL uses OIDs to link its
+    internal system tables together.
    
+
+    
    To uniquely number columns in user tables, it is best to use
+    SERIAL rather than OIDs because
+    SERIAL sequences are unique only within a single
+    table. and are therefore less likely to overflow.
+    SERIAL8 is available for storing eight-byte sequence
+    values.
    
 
     
    TIDs are used to identify specific physical rows
     with block and offset values. TIDs change after rows
     are modified or reloaded. They are used by index entries to point
     to physical rows.
    
 
-    
    4.16) What is the meaning of some of the
+    
    4.13) What is the meaning of some of the
     terms used in PostgreSQL?
    
 
     
    Some of the source code and older documentation use terms that
@@ -1256,7 +1104,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     
    A list of general database terms can be found at: http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html
    
 
-    
    4.17) Why do I get the error "ERROR:
+    
    4.14) Why do I get the error "ERROR:
     Memory exhausted in AllocSetAlloc()"?
    
 
     
    You probably have run out of virtual memory on your system,
@@ -1275,12 +1123,12 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     backend is returning too much data, try it before starting the
     client.
 
-    
    4.18) How do I tell what PostgreSQL version
+    
    4.15) How do I tell what PostgreSQL version
     I am running?
    
 
     
    From psql, type SELECT version();
    
 
-    
    4.19) Why does my large-object operations
+    
    4.16) Why does my large-object operations
     get "invalid large obj descriptor"?
    
 
     
    You need to put BEGIN WORK and COMMIT
@@ -1296,7 +1144,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     
    If you are using a client interface like ODBC you
     may need to set auto-commit off.
    
 
-    
    4.20) How do I create a column that will
+    
    4.17) How do I create a column that will
     default to the current time?
    
 
     
    Use CURRENT_TIMESTAMP:
    
@@ -1305,31 +1153,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
    -

    4.21) Why are my subqueries using - IN so slow?

    - -

    In versions prior to 7.4, subqueries were joined to outer queries - by sequentially scanning the result of the subquery for each row of - the outer query. If the subquery returns only a few rows and the outer - query returns many rows, IN is fastest. To - speed up other queries, replace IN with - EXISTS:

    -
        SELECT *
-    FROM tab
-    WHERE col IN (SELECT subcol FROM subtab);
-
    - to: -
        SELECT *
-    FROM tab
-    WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
-
    - - For this to be fast, subcol should be an indexed column. -

    In version 7.4 and later, IN actually uses the same - sophisticated join techniques as normal queries, and is prefered - to using EXISTS. - -

    4.22) How do I perform an outer join?

    +

    4.18) How do I perform an outer join?

    PostgreSQL supports outer joins using the SQL standard syntax. Here are two examples:

    @@ -1369,7 +1193,7 @@ BYTEA bytea variable-length byte array (null-byte safe) ORDER BY col1 -

    4.23) How do I perform queries using +

    4.19) How do I perform queries using multiple databases?

    There is no way to query a database other than the current one. @@ -1381,7 +1205,7 @@ BYTEA bytea variable-length byte array (null-byte safe) connections to different databases and merge the results on the client side.

    -

    4.24) How do I return multiple rows or +

    4.20) How do I return multiple rows or columns from a function?

    In 7.3, you can easily return multiple rows or columns from a @@ -1389,7 +1213,7 @@ BYTEA bytea variable-length byte array (null-byte safe) http://techdocs.postgresql.org/guides/SetReturningFunctions. -

    4.25) Why can't I reliably create/drop +

    4.21) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?

    PL/PgSQL caches function contents, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that @@ -1399,7 +1223,7 @@ BYTEA bytea variable-length byte array (null-byte safe) EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

    -

    4.26) What encryption options are available? +

    4.22) What encryption options are available?

    • contrib/pgcrypto contains many encryption functions for -- cgit v1.2.3