FAQ

From PostgreSQL wiki

Jump to: navigation, search
spacer Languages: 

English  • Deutsch  • Español  • 日本語  • Português

Additional FAQ Entries on this Wiki

Contents

  • 1 Translations of this Document
  • 2 Platform-specific questions
  • 3 General Questions
    • 3.1 What is PostgreSQL? How is it pronounced? What is Postgres?
    • 3.2 Who controls PostgreSQL?
    • 3.3 Who is the PostgreSQL Global Development Group?
    • 3.4 Who is the PostgreSQL Core Team?
    • 3.5 What about the various PostgreSQL foundations?
    • 3.6 What is the license of PostgreSQL?
    • 3.7 What platforms does PostgreSQL support?
    • 3.8 Where can I get PostgreSQL?
    • 3.9 What is the most recent release?
    • 3.10 Where can I get support?
    • 3.11 How do I submit a bug report?
    • 3.12 How do I find out about known bugs or missing features?
    • 3.13 A bug I'm encountering is fixed in a newer minor release of PostgreSQL, but I don't want to upgrade. Can I get a patch for just this issue?
    • 3.14 I have a program that says it wants PostgreSQL x.y.1. Can I use PostgreSQL x.y.2 instead?
    • 3.15 What documentation is available?
    • 3.16 How can I learn SQL?
    • 3.17 How do I submit a patch or join the development team?
    • 3.18 How does PostgreSQL compare to other DBMSs?
      • 3.18.1 Features
      • 3.18.2 Performance
      • 3.18.3 Reliability
      • 3.18.4 Support
      • 3.18.5 Price
    • 3.19 Can PostgreSQL be embedded?
    • 3.20 How do I unsubscribe from the PostgreSQL email lists? How do I avoid receiving duplicate emails?
  • 4 User Client Questions
    • 4.1 What interfaces are available for PostgreSQL?
    • 4.2 What tools are available for using PostgreSQL with Web pages?
    • 4.3 Does PostgreSQL have a graphical user interface?
  • 5 Administrative Questions
    • 5.1 How do I install PostgreSQL somewhere other than /usr/local/pgsql?
    • 5.2 I'm installing PostgreSQL and don't know the password for the postgres user
    • 5.3 How do I control connections from other hosts?
    • 5.4 How do I tune the database engine for better performance?
      • 5.4.1 Query Changes
      • 5.4.2 Server Configuration
      • 5.4.3 Hardware Selection
    • 5.5 What debugging features are available?
    • 5.6 Why do I get "Sorry, too many clients" when trying to connect?
    • 5.7 What is the upgrade process for PostgreSQL?
    • 5.8 Will PostgreSQL handle recent daylight saving time changes in various countries?
    • 5.9 What computer hardware should I use?
    • 5.10 How does PostgreSQL use CPU resources?
    • 5.11 Why does PostgreSQL have so many processes, even when idle?
    • 5.12 Why does PostgreSQL use so much memory?
  • 6 Operational Questions
    • 6.1 How do I SELECT only the first few rows of a query? A random row?
    • 6.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?
    • 6.3 How do you change a column's data type?
    • 6.4 What is the maximum size for a row, a table, and a database?
    • 6.5 How much database disk space is required to store data from a typical text file?
    • 6.6 Why are my queries slow? Why don't they use my indexes?
    • 6.7 How do I see how the query optimizer is evaluating my query?
    • 6.8 How do I change the sort ordering of textual data?
    • 6.9 How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?
    • 6.10 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?
    • 6.11 What is the difference between the various character types?
    • 6.12 How do I create a serial/auto-incrementing field?
    • 6.13 How do I get the value of a SERIAL insert?
    • 6.14 Doesn't currval() lead to a race condition with other users?
    • 6.15 Why are there gaps in the numbering of my sequence/SERIAL column? Why aren't my sequence numbers reused on transaction abort?
    • 6.16 What is an OID?
    • 6.17 What is a CTID?
    • 6.18 Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
    • 6.19 How do I tell what PostgreSQL version I am running?
    • 6.20 Is there a way to leave an audit trail of database operations?
    • 6.21 How do I create a column that will default to the current time?
    • 6.22 How do I perform an outer join?
    • 6.23 How do I perform queries using multiple databases?
    • 6.24 How do I return multiple rows or columns from a function?
    • 6.25 Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions?
    • 6.26 What replication solutions are available?
    • 6.27 Is possible to create a shared-storage postgresql server cluster?
    • 6.28 Why are my table and column names not recognized in my query? Why is capitalization not preserved?
    • 6.29 I lost the database password. What can I do to recover it?
    • 6.30 Does PostgreSQL have stored procedures?
    • 6.31 Why don't BEGIN, ROLLBACK and COMMIT work in stored procedures/functions?
    • 6.32 Why is "SELECT count(*) FROM bigtable;" slow?
    • 6.33 Why is my query much slower when run as a prepared query?
    • 6.34 Why is my query much slower when run in a function than standalone?
    • 6.35 Why do my strings sort incorrectly?

Translations of this Document

  • German
  • Portuguese
  • Spanish
  • Русский

Platform-specific questions

Windows users should also read the platform FAQ for Windows. There are FAQs for other platforms too.

General Questions

What is PostgreSQL? How is it pronounced? What is Postgres?

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 proprietary 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 FAQ.

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.

Who controls PostgreSQL?

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

Who is the PostgreSQL Global Development Group?

The "PGDG" is an international, unincorporated association of individuals and companies who have contributed to the PostgreSQL project. The PostgreSQL Core Team generally act as spokespeople for the PGDG.

Who is the PostgreSQL Core Team?

A committee of five to seven (currently six) senior contributors to PostgreSQL who do the following for the project: (a) set release dates, (b) handle confidential matters for the project, (c) act as spokespeople for the PGDG when required, and (d) arbitrate community decisions which are not settled by consensus. The current Core Team is listed on top of the contributors page

What about the various PostgreSQL foundations?

While the PostgreSQL project utilizes non-profit corporations in the USA, Europe, Brazil and Japan for fundraising and project coordination, these entities do not own the PostgreSQL code.

What is the license of PostgreSQL?

PostgreSQL is distributed under a license similar to BSD and MIT. 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 license we use: 

PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The 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.

What platforms does PostgreSQL support?

In general, any modern Unix-compatible platform should be able to run PostgreSQL. The platforms that have received recent explicit testing can be seen in the Build farm. The documentation contains more details about supported platforms at www.postgresql.org/docs/current/static/supported-platforms.html.

PostgreSQL also runs natively on Microsoft Windows NT-based operating systems like Win2000 SP4, WinXP, and Win2003. A prepackaged installer is available at www.postgresql.org/download/windows. MSDOS-based versions of Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.

Where can I get PostgreSQL?

There are binary distributions for various operating systems and platforms; see our download area.

The source code can be obtained via web browser or through ftp.

What is the most recent release?

The latest release of PostgreSQL is shown on the front page of our website.

We typically have a major release every year, with minor releases every few months. Minor releases are usually made at the same time for all supported major-release branches. For more about major versus minor releases, see www.postgresql.org/support/versioning.

Where can I get support?

The PostgreSQL community provides assistance to many of its users via email. The main web site to subscribe to the email lists is www.postgresql.org/community/lists/. The general or bugs lists are a good place to start. For best results, consider reading the guide to reporting problems before you post to make sure you include enough information for people to help you.

The major IRC channel is #postgresql on Freenode (irc.freenode.net). 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 support companies is available at www.postgresql.org/support/professional_support.

How do I submit a bug report?

Visit the PostgreSQL bug form at www.postgresql.org/support/submitbug to submit your bug report to the pgsql-bugs mailing list. Also check out our ftp site ftp://ftp.postgresql.org/pub/ to see if there is a more recent PostgreSQL version.

For a prompt and helpful response, it is important for you to read the guide to reporting problems to make sure that you include the information required to fully understand and act on your report.

Bugs submitted using the bug form or posted to any PostgreSQL mailing list typically generates one of the following replies:

  • It is not a bug, and why
  • It is a known bug and is already on the TODO list
  • The bug has been fixed in the current release
  • The bug has been fixed but is not packaged yet in an official release
  • A request is made for more detailed information:
    • Operating system
    • PostgreSQL version
    • Reproducible test case
    • Debugging information
    • Debugger backtrace output
  • The bug is new. The following might happen:
    • A patch is created and will be included in the next major or minor release
    • The bug cannot be fixed immediately and is added to the TODO list

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

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

A feature request usually results in one of the following replies:

  • The feature is already on the TODO list
  • The feature is not desired because:
    • It duplicates existing functionality that already follows the SQL standard
    • The feature would increase code complexity but add little benefit
    • The feature would be insecure or unreliable
  • The new feature is added to the TODO list

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.

A bug I'm encountering is fixed in a newer minor release of PostgreSQL, but I don't want to upgrade. Can I get a patch for just this issue?

No. Nobody will make a custom patch for you so you can (say) extract a fix from 8.4.3 and apply it to 8.4.1 . That's because there should never be any need to do that.

PostgreSQL has a strict policy that only bug fixes are back-patched into point releases, as per the version policy. It is safe to upgrade from 8.4.1 to 8.4.3, for example. Binary compatibility will be maintained, no dump and reload is required, nothing will break, but bugs that might cause problems have been fixed. Even if you are not yet encountering a particular bug, you might later, and it is wise to upgrade promptly. You just have to install the update and re-start the database server, nothing more.

Upgrading from 8.3 to 8.4, or 8.4 to 9.0, is a major upgrade that does not come with the same guarantees. However, if a bug is discovered in 9.0 then it will generally be fixed in all maintained older versions like 8.4 and 8.3 if it is safe and practical to do so.

This means that if you're running 8.1.0, upgrading to 8.1.21 is strongly recommended and very safe. On the other hand, upgrading to the next major release, 8.2.x, may require changes to your app, and will certainly require a dump and reload.

If you want to be careful about all upgrades, you should read the release notes for each point release between your current one and the latest minor version of the same major release carefully. If you're exceptionally paranoid about upgrades, you can fetch the source code to each set of point release changes from PostgreSQL's git repository and examine it.

It is strongly recommended that you always upgrade to the latest minor release. Avoid trying to extract and apply individual fixes from point releases; by doing so you're bypassing all the QA done by the PostgreSQL team when they prepare a release, and are creating your own custom version that nobody else has ever used. It's a lot safer to just update to the latest tested, safe release. Patching your own custom, non-standard build will also take more time/effort, and will require the same amount of downtime as a normal upgrade.

I have a program that says it wants PostgreSQL x.y.1. Can I use PostgreSQL x.y.2 instead?

Any program that works with a particular version, like 8.4.1, should work with any other minor version in the same major version. That means that if a program says it wants (eg) 8.4.1, you can and should install the latest in the 8.4 series instead.

See the previous question for more details.

What documentation is available?

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 www.postgresql.org/docs.

There are a number of PostgreSQL books available for purchase; two of them are also available online. A list of books can be found at www.postgresql.org/docs/books/. One of the most popular ones is the one by Korry & Susan Douglas.

There is also a collection of PostgreSQL technical articles on the wiki.

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.

How can I learn SQL?

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.

Many people consider the PostgreSQL documentation to be an excellent guide for learning SQL its self, as well as for PostgreSQL's implementation of it. For best results use PostgreSQL alongside another full-featured SQL database as you learn, so you get used to SQL without becoming reliant on PostgreSQL-specific features. The PostgreSQL documentation generally mentions when features are PostgreSQL extensions of the standard.

There are also many nice tutorials available online:

  • www.intermedia.net/support/sql/sqltut.shtm
  • sqlcourse.com
  • www.w3schools.com/sql/default.asp
  • mysite.verizon.net/Graeme_Birchall/id1.html
  • sqlzoo.net

How do I submit a patch or join the development team?

See the Developer's FAQ.

How does PostgreSQL compare to other DBMSs?

There are several ways of measuring software: features, performance, reliability, support, and price.

Features

PostgreSQL has most features present in large proprietary DBMSs, like transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking. We have some features they do not have, like user-defined types, inheritance, rules, and multi-version concurrency control to reduce lock contention.

Performance

PostgreSQL's performance is comparable to other proprietary and open source databases. It is faster for some things, slower for others. Our performance is usually +/-10% compared to other databases.

Reliability

We realize that a DBMS must be reliable, or it is worthless. We strive to release well-tested, stable code that has a minimum of bugs. Each release has at least one month of beta testing, and our release history shows that we can provide stable, solid releases that are ready for production use. We believe we compare favorably to other database software in this area.

Support

Our mailing lists provide contact with a large group of developers and users to help resolve any problems encountered. While we cannot guarantee a fix, proprietary DBMSs do not always supply a fix either. Direct access to developers, the user 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 section 1.7).

Price

We are free for all use, both proprietary and open source. You can add our code to your product with no limitations, except those outlined in our BSD-style license stated above.

Can PostgreSQL be embedded?

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.

Popular embeddable options include SQLite and Firebird SQL.

How do I unsubscribe from the PostgreSQL email lists? How do I avoid receiving duplicate emails?

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.

User Client Questions

What interfaces are available for PostgreSQL?

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 pgfoundry.org.

Related searches:
release available support database version
gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.