Last updated: Tue Sep 23 16:19:49 EDT 2008
- -Current maintainer: Bruce Momjian (bruce@momjian.us) -
- -The most recent version of this document can be viewed at - http://www.postgresql.org/files/documentation/faqs/FAQ.html.
- -Platform-specific questions are answered at - http://www.postgresql.org/docs/faq/.
-PostgreSQL is pronounced Post-Gres-Q-L. (For those curious - about how to say "PostgreSQL", an audio file - is available.)
- -PostgreSQL is an object-relational database system that has the - features of traditional commercial database systems with - enhancements to be found in next-generation DBMS - systems. PostgreSQL is free and the complete source code is - available.
- -PostgreSQL development is performed by a team of mostly volunteer - developers spread throughout the world and communicating via the - Internet. It is a community project and is not controlled by any - company. To get involved, see the developer's FAQ at - http://www.postgresql.org/docs/faqs.FAQ_DEV.html -
- -Postgres is a widely-used nickname for PostgreSQL. It was the - original name of the project at Berkeley and is strongly preferred - over other nicknames. If you find 'PostgreSQL' hard to pronounce, call - it 'Postgres' instead.
- -If you are looking for a PostgreSQL gatekeeper, central committee, - or controlling company, give up --- there isn't one. We do have a - core committee and CVS committers, but these groups are more for - administrative purposes than control. The project is directed by - the community of developers and users, which anyone can join. All - you need to do is subscribe to the mailing lists and participate in the - discussions. (See the - Developer's FAQ for information on how to get involved in PostgreSQL - development.)
- -PostgreSQL is distributed under the classic BSD license. Basically, - it allows users to do anything they want with the code, including - reselling binaries without the source code. The only restriction is - that you not hold us legally liable for problems with the software. - There is also the requirement that this copyright appear in all copies - of the software. Here is the actual BSD license we use:
- -PostgreSQL Data Base Management System
- -Portions Copyright (c) 1996-2009, 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 agreement is hereby granted, provided that the above - copyright notice and this paragraph and the following two - paragraphs appear in all copies.
- -IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY - PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL - DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS - SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF - CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
- -THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY - WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES - OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE - SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE - UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, - SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
- -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.
- -PostgreSQL also runs natively on Microsoft Windows NT-based operating - systems like Win2000 SP4, WinXP, and Win2003. A prepackaged installer is - available at - http://www.postgresql.org/download/windows. MSDOS-based versions - of Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.
- -There is also a Novell Netware 6 port at - http://developer.novell.com/wiki/index.php/Postgresql, - and an OS/2 (eComStation) version at - http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgreSQL&stype=all&sort=type&dir=%2F.
- -Via web browser, use - http://www.postgresql.org/ftp/, and via ftp, use - - ftp://ftp.postgresql.org/pub/.
- -The latest release of PostgreSQL is version 8.3.3.
- -We plan to have a major release every year, with minor releases - every few months.
- -The PostgreSQL community provides assistance to many of its users - via email. The main web site to subscribe to the email lists is - - http://www.postgresql.org/community/lists/. The general - or bugs lists are a good place to start.
- -The major IRC channel is #postgresql on Freenode
- (irc.freenode.net). To connect you can use the Unix
- program irc -c '#postgresql' "$USER" irc.freenode.net
- or use any other IRC clients. A Spanish one also exists
- on the same network, (#postgresql-es), a French one,
- (#postgresqlfr), and a Brazilian one, (#postgresql-br).
- There is also a PostgreSQL channel on EFNet.
A list of commercial support companies is available at - http://www.postgresql.org/support/professional_support.
- -Visit the PostgreSQL bug form at - http://www.postgresql.org/support/submitbug. - Also check out our ftp site ftp://ftp.postgresql.org/pub/ to - see if there is a more recent PostgreSQL version.
- -Bugs submitted using the bug form or posted to any PostgreSQL mailing - list typically generates one of the following replies:
-PostgreSQL supports an extended subset of SQL:2003. - See our TODO - list for known bugs, missing features, and future plans.
- -A feature request usually results in one of the following - replies:
-PostgreSQL does not use a bug tracking system because we find - it more efficient to respond directly to email and keep the - TODO - list up-to-date. In practice, bugs don't last very long in the - software, and bugs that affect a large number of users are fixed - rapidly. The only place to find all changes, improvements, and - fixes in a PostgreSQL release is to read the - CVS - log messages. Even the release notes do not list every change - made to the software.
- -PostgreSQL includes extensive documentation, including a large - manual, manual pages, and some test examples. See the /doc - directory. You can also browse the manuals online at http://www.postgresql.org/docs. -
- -There are two PostgreSQL books available online at http://www.postgresql.org/docs/books/awbook.html - and http://www.commandprompt.com/ppbook/. - There are a number of PostgreSQL books available for purchase. One - of the most popular ones is by Korry Douglas. A list of book reviews - can be found at http://www.postgresql.org/docs/books/. - There is also a collection of PostgreSQL technical articles at http://wiki.postgresql.org/wiki/Community_Generated_Articles%2C_Guides%2C_and_Documentation.
- -The command line client program psql has some \d commands to show - information about types, operators, functions, aggregates, etc. - use \? to - display the available commands.
- -Our web site contains even more documentation.
- -First, consider the PostgreSQL-specific books mentioned above. - Many of our users also like The Practical SQL Handbook, - Bowman, Judith S., et al., Addison-Wesley. Others like The - Complete Reference SQL, Groff et al., McGraw-Hill.
- -There are also many nice tutorials available online: -
See the - Developer's FAQ.
- -There are several ways of measuring software: features, - performance, reliability, support, and price.
- -PostgreSQL is designed as a client/server architecture, which - requires separate processes for each client and server, and various - helper processes. Many embedded architectures can support such - requirements. However, if your embedded architecture requires the - database server to run inside the application process, you cannot use - Postgres and should select a lighter-weight database solution.
- -USA daylight saving time changes are included in PostgreSQL release - 8.0.[4+], and all later major releases, e.g. 8.1. Canada and Western - Australia changes are included in 8.0.[10+], 8.1.[6+], and all later - major releases. PostgreSQL releases prior to 8.0 use the operating - system's timezone database for daylight saving information.
- -The PostgreSQL Majordomo - page allows subscribing or unsubscribing from any of the PostgreSQL - email lists. (You might need to have your Majordomo password emailed - to you to log in.)
- -All PostgreSQL email lists are configured so a group reply goes to - the email list and the original email author. This is done so - users receive the quickest possible email replies. If you would prefer - not to receive duplicate email from the list in cases where you already - receive an email directly, check eliminatecc from the Majordomo - Change Settings page. You can also prevent yourself from - receiving copies of emails you post to the lists by unchecking - selfcopy.
- - -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.
- -Some programming languages like PHP include an - interface to PostgreSQL. Interfaces for languages like Perl, - TCL, Python, and many others are available at - http://pgfoundry.org.
- -A nice introduction to Database-backed Web pages can be seen at: - http://www.webreview.com
- -For Web integration, PHP (http://www.php.net) is an excellent - interface.
- -For complex cases, many use the Perl and DBD::Pg with CGI.pm or - mod_perl.
- -There are a large number of GUI Tools that are available for PostgreSQL - from both commercial and open source developers. A detailed list can be - found in the - Community Guide to PostgreSQL GUI Tools.
- -Specify the --prefix option when running - configure.
- -By default, PostgreSQL only allows connections from the local - machine using Unix domain sockets or TCP/IP connections. Other - machines will not be able to connect unless you modify - listen_addresses in the postgresql.conf file, enable - host-based authentication by modifying the - $PGDATA/pg_hba.conf file, and restart the database server.
- -There are three major areas for potential performance - improvement:
- -There are many log_* server configuration
- variables at http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html
- that enable printing of query and process statistics which
- can be very useful for debugging and performance measurements.
You have reached the default limit of 100 database sessions. You - need to increase the server's limit on how many - concurrent backend processes it can start by changing the - max_connections value in postgresql.conf and - restarting the server.
- -See http://www.postgresql.org/support/versioning - for a general discussion about upgrading, and - http://www.postgresql.org/docs/current/static/install-upgrading.html - for specific instructions.
- -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 - quality motherboards are more reliable and have better performance than - less expensive hardware. PostgreSQL will run on almost any hardware, - but if reliability and performance are important it is wise to - research your hardware options thoroughly. A disk controller with a - battery-backed cache is also useful. Our email lists can be used - to discuss hardware options and tradeoffs.
- -To retrieve only a few rows, if you know at the number of rows - needed at the time of the SELECT use - LIMIT . If an index matches the ORDER - BY it is possible the entire query does not have to be - executed. If you don't know the number of rows at - SELECT time, use a cursor and - FETCH.
- -To SELECT a random row, use:
-- SELECT col - FROM tab - ORDER BY random() - LIMIT 1; -- -
Use the \dt command to see tables in psql. For a complete list of - commands inside psql you can use \?. Alternatively you can read the source - code for psql in file pgsql/src/bin/psql/describe.c, it - contains SQL commands that generate the output for - psql's backslash commands. You can also start psql with the - -E option so it will print out the queries it uses to execute the - commands you give. PostgreSQL also provides an SQL compliant - INFORMATION SCHEMA interface you can query to get information about the - database.
- -There are also system tables beginning with pg_ that describe - these too.
- -Use psql -l will list all databases.
- -Also try the file pgsql/src/tutorial/syscat.source. It - illustrates many of the SELECTs needed to get - information from the database system tables.
- -Changing the data type of a column can be done easily in 8.0 - and later with ALTER TABLE ALTER COLUMN TYPE.
- -In earlier releases, do this:
-- BEGIN; - ALTER TABLE tab ADD COLUMN new_col new_data_type; - UPDATE tab SET new_col = CAST(old_col AS new_data_type); - ALTER TABLE tab DROP COLUMN old_col; - COMMIT; --
You might then want to do VACUUM FULL tab to reclaim the - disk space used by the expired rows.
- -These are the limits:
--- --
-- Maximum size for a database? unlimited (32 TB databases -exist) - Maximum size for a table? 32 TB - Maximum size for a row? 400 GB - Maximum size for a field? 1 GB - Maximum number of rows in a table? unlimited - Maximum number of columns in a table? 250-1600 depending -on column types - Maximum number of indexes on a -table? unlimited
Of course, these are not actually unlimited, but limited to - available disk space and memory/swap space. Performance may suffer - when these values get unusually large.
- -The maximum table size of 32 TB does not require large file - support from the operating system. Large tables are stored as - multiple 1 GB files so file system size limits are not - important.
- -The maximum table size, row size, and maximum number of columns - can be quadrupled by increasing the default block size to 32k. The - maximum table size can also be increased using table partitioning.
- -One limitation is that indexes can not be created on columns - longer than about 2,000 characters. Fortunately, such indexes are - rarely needed. Uniqueness is best guaranteed by a function index - of an MD5 hash of the long column, and full text indexing - allows for searching of words within the column.
- -A PostgreSQL database may require up to five times the disk - space to store data from a text file.
- -As an example, consider a file of 100,000 lines with an integer - and text description on each line. Suppose the text string - avergages twenty bytes in length. The flat file would be 2.8 MB. - The size of the PostgreSQL database file containing this data can - be estimated as 5.2 MB:
-- 24 bytes: each row header (approximate) - 24 bytes: one int field and one text field - + 4 bytes: pointer on page to tuple - ---------------------------------------- - 52 bytes per row - - The data page size in PostgreSQL is 8192 bytes (8 KB), so: - - 8192 bytes per page - ------------------- = 158 rows per database page (rounded down) - 52 bytes per row - - 100000 data rows - -------------------- = 633 database pages (rounded up) - 158 rows per page - -633 database pages * 8192 bytes per page = 5,185,536 bytes (5.2 MB) -- -
Indexes do not require as much overhead, but do contain the data - that is being indexed, so they can be large also.
- -NULLs are stored as bitmaps, so they - use very little space.
- -Indexes are not used by every query. Indexes are used only if the - table is larger than a minimum size, and the query selects only a - small percentage of the rows in the table. This is because the random - disk access caused by an index scan can be slower than a straight read - through the table, or sequential scan.
- -To determine if an index should be used, PostgreSQL must have - statistics about the table. These statistics are collected using - VACUUM ANALYZE, or simply ANALYZE. - Using statistics, the optimizer knows how many rows are in the - table, and can better determine if indexes should be used. - Statistics are also valuable in determining optimal join order and - join methods. Statistics collection should be performed - periodically as the contents of the table change.
- -Indexes are normally not used for ORDER BY or to - perform joins. A sequential scan followed by an explicit sort is - usually faster than an index scan of a large table. - However, LIMIT combined with ORDER BY - often will use an index because only a small portion of the table - is returned.
- -If you believe the optimizer is incorrect in choosing a
- sequential scan, use SET enable_seqscan TO 'off' and
- run query again to see if an index scan is indeed faster.
When using wild-card operators such as LIKE or - ~, indexes can only be used in certain circumstances:
-text_pattern_ops index for such cases that work only
- for LIKE indexing. It is also possible to use
- full text indexing for word searches.
- See the EXPLAIN manual page.
- -The ~ operator does regular expression matching, and - ~* does case-insensitive regular expression matching. The - case-insensitive variant of LIKE is called - ILIKE.
- -Case-insensitive equality comparisons are normally expressed - as:
-- SELECT * - FROM tab - WHERE lower(col) = 'abc'; -- This will not use an standard index. However, if you create an - expression index, it will be used: -
- CREATE INDEX tabindex ON tab (lower(col)); --
If the above index is created as UNIQUE, though - the column can store upper and lowercase characters, it can not have - 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.
- -You test the column with IS NULL and IS - NOT NULL, like this:
- -- SELECT * - FROM tab - 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, - so the following will put NULL entries at the top of the resulting list:
- -- SELECT * - FROM tab - ORDER BY (col IS NOT NULL) -- -
-- --
-- Type Internal Name Notes - VARCHAR(n) varchar size specifies maximum -length, no padding - CHAR(n) bpchar blank padded to the specified -fixed length - TEXT text no specific upper limit on -length - BYTEA bytea variable-length byte array -(null-byte safe) - "char" char one character
You will see the internal name when examining system catalogs - and in some error messages.
- -The first four types above are "varlena" types (i.e., the first - four bytes on disk are the length, followed by the data). Thus the - actual space used is slightly greater than the declared size. - However, long values are also subject to compression, so the space - on disk might also be less than expected.
- - VARCHAR(n) is best when storing variable-length - strings and it limits how long a string can be. TEXT - is for strings of unlimited length, with a maximum of one gigabyte. -CHAR(n) is for storing strings that are all the - same length. CHAR(n) pads with blanks to the specified - length, while VARCHAR(n) only stores the characters - supplied. BYTEA is for storing binary data, - particularly values that include NULL bytes. All the - types described here have similar performance characteristics.
- -PostgreSQL supports a SERIAL data type. It - auto-creates a sequence. For example, this:
-- CREATE TABLE person ( - id SERIAL, - name TEXT - ); -- - is automatically translated into this: - -
- CREATE SEQUENCE person_id_seq;
- CREATE TABLE person (
- id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
- name TEXT
- );
-
-
- Automatically created sequence are named - <table>_<serialcolumn>_seq, where - table and serialcolumn are the names of the table and - SERIAL column, respectively. See the - create_sequence manual page for more information about - sequences.
- -The simplest way is to retrieve the assigned SERIAL - value with RETURNING. Using the example table in 4.11.1, it would look like this:
- -
- INSERT INTO person (name) VALUES ('Blaise Pascal') RETURNING id;
-
-
- You can also call nextval() and use that value in the
- INSERT, or call currval() after the
- INSERT.
-
- No. currval() returns the current value assigned by your - session, not by all sessions.
- -To improve concurrency, sequence values are given out to running - transactions as needed and are not locked until the transaction - completes. This causes gaps in numbering from aborted - transactions.
- -If a table is created WITH OIDS, each row - gets a unique a OID. OIDs are - automatically 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 rows 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.
- -CTIDs are used to identify specific physical rows - with block and offset values. CTIDs change after rows - are modified or reloaded. They are used by index entries to point - to physical rows.
- -You probably have run out of virtual memory on your system, - or your kernel has a low limit for certain resources. Try this - before starting the server:
-- ulimit -d 262144 - limit datasize 256m -- - Depending on your shell, only one of these may succeed, but it will - set your process data segment limit much higher and perhaps allow - the query to complete. This command applies to the current process, - and all subprocesses created after the command is run. If you are - having a problem with the SQL client because the - backend is returning too much data, try it before starting the - client. - -
From psql, type SELECT version();
Use CURRENT_TIMESTAMP:
-- CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- -
PostgreSQL supports outer joins using the SQL standard syntax. - Here are two examples:
-- SELECT * - FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col); -- or -
- SELECT * - FROM t1 LEFT OUTER JOIN t2 USING (col); -- -
These identical queries join t1.col to t2.col, and also return - any unjoined rows in t1 (those with no match in t2). A - RIGHT join would add unjoined rows of t2. A - FULL join would return the matched rows plus all - unjoined rows from t1 and t2. The word OUTER is - optional and is assumed in LEFT, - RIGHT, and FULL joins. Ordinary joins - are called INNER joins.
- -There is no way to query a database other than the current one. - Because PostgreSQL loads database-specific system catalogs, it is - uncertain how a cross-database query should even behave.
- -contrib/dblink allows cross-database queries using - function calls. Of course, a client can also make simultaneous - connections to different databases and merge the results on the - client side.
- -It is easy using set-returning functions, - - http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions.
- -In PostgreSQL versions < 8.3, PL/PgSQL caches function scripts, and - an unfortunate side effect is that if a PL/PgSQL function accesses a - temporary table, and that table is later dropped and recreated, and - the function called again, the function will fail because the cached - function contents still point to the old temporary table. The solution - is to use EXECUTE for temporary table access in - PL/PgSQL. This will cause the query to be reparsed every time.
- -This problem does not occur in PostgreSQL 8.3 and later.
- -Though "replication" is a single term, there are several technologies - for doing replication, with advantages and disadvantages for each.
- -Master/slave replication allows a single master to receive read/write - queries, while slaves can only accept read/SELECT - queries. The most popular freely available master-slave PostgreSQL - replication solution is - Slony-I.
- -Multi-master replication allows read/write queries to be sent to - multiple replicated computers. This capability also has a severe impact - on performance due to the need to synchronize changes between servers. - PGCluster is the - most popular such solution freely available for PostgreSQL.
- -There are also commercial and hardware-based replication solutions - available supporting a variety of replication models.
- -The most common cause of unrecognized names is the use of - double-quotes around table or column names during table creation. - When double-quotes are used, table and column names (called - identifiers) are stored - case-sensitive, meaning you must use double-quotes when - referencing the names in a query. Some interfaces, like pgAdmin, - automatically double-quote identifiers during table creation. - So, for identifiers to be recognized, you must either: -