FAQ 36.5 KB
Newer Older
Bruce Momjian's avatar
Bruce Momjian committed
1

Bruce Momjian's avatar
Bruce Momjian committed
2
                Frequently Asked Questions (FAQ) for PostgreSQL
Bruce Momjian's avatar
Bruce Momjian committed
3
                                       
4
   Last updated: Tue Nov 13 22:37:19 EST 2007
Bruce Momjian's avatar
Bruce Momjian committed
5
   
Bruce Momjian's avatar
Bruce Momjian committed
6
   Current maintainer: Bruce Momjian (bruce@momjian.us)
Bruce Momjian's avatar
Bruce Momjian committed
7
   
Bruce Momjian's avatar
Bruce Momjian committed
8
   The most recent version of this document can be viewed at
9
   http://www.postgresql.org/files/documentation/faqs/FAQ.html.
Bruce Momjian's avatar
Bruce Momjian committed
10
   
Bruce Momjian's avatar
Bruce Momjian committed
11
   Platform-specific questions are answered at
12
   http://www.postgresql.org/docs/faq/.
Bruce Momjian's avatar
Bruce Momjian committed
13 14
     _________________________________________________________________
   
15
                             General Questions
Bruce Momjian's avatar
Bruce Momjian committed
16
                                      
Bruce Momjian's avatar
Bruce Momjian committed
17
   1.1) What is PostgreSQL? How is it pronounced?
18 19 20 21
   1.2) Who controls PostgreSQL?
   1.3) What is the copyright of PostgreSQL?
   1.4) What platforms does PostgreSQL support?
   1.5) Where can I get PostgreSQL?
22
   1.6) What is the most recent release?
23 24 25 26 27
   1.7) Where can I get support?
   1.8) How do I submit a bug report?
   1.9) How do I find out about known bugs or missing features?
   1.10) What documentation is available?
   1.11) How can I learn SQL?
28
   1.12) How do I submit a patch or join the development team?
29
   1.13) How does PostgreSQL compare to other DBMSs?
30 31
   1.14) Will PostgreSQL handle recent daylight saving time changes in
   various countries?
32 33
   
                           User Client Questions
Bruce Momjian's avatar
Bruce Momjian committed
34
                                      
35
   2.1) What interfaces are available for PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
36
   2.2) What tools are available for using PostgreSQL with Web pages?
37
   2.3) Does PostgreSQL have a graphical user interface?
38 39 40
   
                          Administrative Questions
                                      
Bruce Momjian's avatar
Bruce Momjian committed
41
   3.1) How do I install PostgreSQL somewhere other than
Bruce Momjian's avatar
Bruce Momjian committed
42
   /usr/local/pgsql?
43 44 45 46
   3.2) How do I control connections from other hosts?
   3.3) How do I tune the database engine for better performance?
   3.4) What debugging features are available?
   3.5) Why do I get "Sorry, too many clients" when trying to connect?
47
   3.6 What is the upgrade process for PostgreSQL?
48
   3.7) What computer hardware should I use?
49 50
   
                           Operational Questions
Bruce Momjian's avatar
Bruce Momjian committed
51
                                      
52 53
   4.1) How do I SELECT only the first few rows of a query? A random row?
   4.2) How do I find out what tables, indexes, databases, and users are
54
   defined? How do I see the queries used by psql to display them?
55
   4.3) How do you change a column's data type?
56 57
   4.4) What is the maximum size for a row, a table, and a database?
   4.5) How much database disk space is required to store data from a
Bruce Momjian's avatar
Bruce Momjian committed
58
   typical text file?
59
   4.6) Why are my queries slow? Why don't they use my indexes?
60
   4.7) How do I see how the query optimizer is evaluating my query?
61 62 63
   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?
64 65 66
   4.9) 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?
67
   4.10) What is the difference between the various character types?
Bruce Momjian's avatar
Bruce Momjian committed
68 69 70 71
   4.11.1) How do I create a serial/auto-incrementing field?
   4.11.2) How do I get the value of a SERIAL insert?
   4.11.3) Doesn't currval() lead to a race condition with other users?
   4.11.4) Why aren't my sequence numbers reused on transaction abort?
Bruce Momjian's avatar
Bruce Momjian committed
72
   Why are there gaps in the numbering of my sequence/SERIAL column?
73
   4.12) What is an OID? What is a CTID?
74
   4.13) Why do I get the error "ERROR: Memory exhausted in
Bruce Momjian's avatar
Bruce Momjian committed
75
   AllocSetAlloc()"?
76
   4.14) How do I tell what PostgreSQL version I am running?
77 78 79 80
   4.15) How do I create a column that will default to the current time?
   4.16) How do I perform an outer join?
   4.17) How do I perform queries using multiple databases?
   4.18) How do I return multiple rows or columns from a function?
Bruce Momjian's avatar
Bruce Momjian committed
81 82 83 84
   4.19) Why do I get "relation with OID ##### does not exist" errors
   when accessing temporary tables in PL/PgSQL functions?
   4.20) What replication solutions are available?
   4.21) Why are my table and column names not recognized in my query?
Bruce Momjian's avatar
Bruce Momjian committed
85
   Why is capitalization not preserved?
Bruce Momjian's avatar
Bruce Momjian committed
86 87
     _________________________________________________________________
   
88 89
                             General Questions
                                      
90 91
  1.1) What is PostgreSQL? How is it pronounced?
  
92
   PostgreSQL is pronounced Post-Gres-Q-L, but can also be referred to as
93
   simply Postgres, particularly in conversation. (For those curious
Bruce Momjian's avatar
Bruce Momjian committed
94
   about how to say "PostgreSQL", an audio file is available.)
95 96 97 98 99 100 101 102 103
   
   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
Bruce Momjian's avatar
Bruce Momjian committed
104
   company. To get involved, see the developer's FAQ at
105
   http://www.postgresql.org/docs/faqs.FAQ_DEV.html
Bruce Momjian's avatar
Bruce Momjian committed
106
   
107 108 109 110 111 112 113 114 115
  1.2) 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
116
   involved in PostgreSQL development.)
117
   
118
  1.3) What is the copyright of PostgreSQL?
119
  
Bruce Momjian's avatar
Bruce Momjian committed
120 121 122 123 124 125
   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:
Bruce Momjian's avatar
Bruce Momjian committed
126 127 128
   
   PostgreSQL Data Base Management System
   
129
   Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
130 131
   Portions Copyright (c) 1994-1996 Regents of the University of
   California
Bruce Momjian's avatar
Bruce Momjian committed
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
   
   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.
   
152
  1.4) What platforms does PostgreSQL support?
153
  
Bruce Momjian's avatar
Bruce Momjian committed
154
   In general, any modern Unix-compatible platform should be able to run
Bruce Momjian's avatar
Bruce Momjian committed
155 156 157
   PostgreSQL. The platforms that had received explicit testing at the
   time of release are listed in the installation instructions.
   
158
   PostgreSQL also runs natively on Microsoft Windows NT-based operating
Bruce Momjian's avatar
Bruce Momjian committed
159
   systems like Win2000 SP4, WinXP, and Win2003. A prepackaged installer
Bruce Momjian's avatar
Bruce Momjian committed
160
   is available at http://pgfoundry.org/projects/pginstaller. MSDOS-based
161
   versions of Windows (Win95, Win98, WinMe) can run PostgreSQL using
162
   Cygwin.
163
   
Bruce Momjian's avatar
Bruce Momjian committed
164 165
   There is also a Novell Netware 6 port at http://forge.novell.com, and
   an OS/2 (eComStation) version at
Bruce Momjian's avatar
Bruce Momjian committed
166 167
   http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgre
   SQL&stype=all&sort=type&dir=%2F.
Bruce Momjian's avatar
Bruce Momjian committed
168
   
169
  1.5) Where can I get PostgreSQL?
170
  
171
   Via web browser, use http://www.postgresql.org/ftp/, and via ftp, use
172
   ftp://ftp.postgresql.org/pub/.
173
   
174
  1.6) What is the most recent release?
175
  
176
   The latest release of PostgreSQL is version 8.2.5.
177 178 179 180
   
   We plan to have a major release every year, with minor releases every
   few months.
   
181
  1.7) Where can I get support?
182
  
183 184 185 186
   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.
Bruce Momjian's avatar
Bruce Momjian committed
187
   
Bruce Momjian's avatar
Bruce Momjian committed
188
   The major IRC channel is #postgresql on Freenode (irc.freenode.net).
189
   To connect you can use the Unix program irc -c '#postgresql' "$USER"
190
   irc.freenode.net or use any other IRC clients. A Spanish one also
191 192 193
   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.
Bruce Momjian's avatar
Bruce Momjian committed
194
   
Bruce Momjian's avatar
Bruce Momjian committed
195
   A list of commercial support companies is available at
Bruce Momjian's avatar
Bruce Momjian committed
196
   http://www.postgresql.org/support/professional_support.
197
   
198
  1.8) How do I submit a bug report?
199
  
Bruce Momjian's avatar
Bruce Momjian committed
200
   Visit the PostgreSQL bug form at
Bruce Momjian's avatar
Bruce Momjian committed
201
   http://www.postgresql.org/support/submitbug. Also check out our ftp
202
   site ftp://ftp.postgresql.org/pub/ to see if there is a more recent
203
   PostgreSQL version.
Bruce Momjian's avatar
Bruce Momjian committed
204
   
205 206 207
   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
Bruce Momjian's avatar
Bruce Momjian committed
208
     * It is a known bug and is already on the TODO list
209 210 211 212 213 214 215 216 217 218
     * 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:
219 220
          + A patch is created and will be included in the next major or
            minor release
221 222 223 224
          + The bug cannot be fixed immediately and is added to the TODO
            list
       
  1.9) How do I find out about known bugs or missing features?
225
  
226 227
   PostgreSQL supports an extended subset of SQL:2003. See our TODO list
   for known bugs, missing features, and future plans.
Bruce Momjian's avatar
Bruce Momjian committed
228
   
229 230 231 232 233 234 235 236 237 238 239 240 241 242
   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
243 244 245
   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.
Bruce Momjian's avatar
Bruce Momjian committed
246
   
247
  1.10) What documentation is available?
248
  
249 250
   PostgreSQL includes extensive documentation, including a large manual,
   manual pages, and some test examples. See the /doc directory. You can
251
   also browse the manuals online at http://www.postgresql.org/docs.
Bruce Momjian's avatar
Bruce Momjian committed
252
   
Bruce Momjian's avatar
Bruce Momjian committed
253
   There are two PostgreSQL books available online at
Bruce Momjian's avatar
Bruce Momjian committed
254
   http://www.postgresql.org/docs/books/awbook.html and
Bruce Momjian's avatar
Bruce Momjian committed
255 256 257
   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
258 259 260
   http://www.postgresql.org/docs/books/. There is also a collection of
   PostgreSQL technical articles at
   http://www.postgresql.org/docs/techdocs.
Bruce Momjian's avatar
Bruce Momjian committed
261
   
Bruce Momjian's avatar
Bruce Momjian committed
262 263 264
   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.
Bruce Momjian's avatar
Bruce Momjian committed
265
   
Bruce Momjian's avatar
Bruce Momjian committed
266
   Our web site contains even more documentation.
267
   
268
  1.11) How can I learn SQL?
269
  
270 271 272 273
   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.
Bruce Momjian's avatar
Bruce Momjian committed
274
   
Bruce Momjian's avatar
Bruce Momjian committed
275 276 277 278 279 280
   There are also many nice tutorials available online:
     * http://www.intermedia.net/support/sql/sqltut.shtm
     * http://sqlcourse.com
     * http://www.w3schools.com/sql/default.asp
     * http://mysite.verizon.net/Graeme_Birchall/id1.html
       
281
  1.12) How do I submit a patch or join the development team?
282
  
283
   See the Developer's FAQ.
Bruce Momjian's avatar
Bruce Momjian committed
284
   
285
  1.13) How does PostgreSQL compare to other DBMSs?
286
  
287 288 289 290
   There are several ways of measuring software: features, performance,
   reliability, support, and price.
   
   Features
Bruce Momjian's avatar
Bruce Momjian committed
291 292 293 294
          PostgreSQL has most features present in large commercial 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,
Bruce Momjian's avatar
Bruce Momjian committed
295
          inheritance, rules, and multi-version concurrency control to
Bruce Momjian's avatar
Bruce Momjian committed
296
          reduce lock contention.
297 298
          
   Performance
Bruce Momjian's avatar
Bruce Momjian committed
299 300
          PostgreSQL's performance is comparable to other commercial and
          open source databases. It is faster for some things, slower for
301 302
          others. Our performance is usually +/-10% compared to other
          databases.
303 304 305 306 307 308 309 310 311 312
          
   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
313 314 315 316 317 318 319
          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, commercial 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 FAQ
320
          section 1.7.)
321 322 323 324 325
          
   Price
          We are free for all use, both commercial and non-commercial.
          You can add our code to your product with no limitations,
          except those outlined in our BSD-style license stated above.
326 327 328 329
          
  1.14) Will PostgreSQL handle recent daylight saving time changes in various
  countries?
  
330 331 332 333 334
   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.
Bruce Momjian's avatar
Bruce Momjian committed
335
     _________________________________________________________________
336
   
337 338
                           User Client Questions
                                      
339 340
  2.1) What interfaces are available for PostgreSQL?
  
341 342 343 344
   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.
Bruce Momjian's avatar
Bruce Momjian committed
345
   
346 347
   Some programming languages like PHP include an interface to
   PostgreSQL. Interfaces for languages like Perl, TCL, Python, and many
348
   others are available at http://pgfoundry.org.
Bruce Momjian's avatar
Bruce Momjian committed
349
   
350 351
  2.2) What tools are available for using PostgreSQL with Web pages?
  
Bruce Momjian's avatar
Bruce Momjian committed
352
   A nice introduction to Database-backed Web pages can be seen at:
Bruce Momjian's avatar
Bruce Momjian committed
353
   http://www.webreview.com
Bruce Momjian's avatar
Bruce Momjian committed
354
   
Bruce Momjian's avatar
Bruce Momjian committed
355 356
   For Web integration, PHP (http://www.php.net) is an excellent
   interface.
Bruce Momjian's avatar
Bruce Momjian committed
357
   
Bruce Momjian's avatar
Bruce Momjian committed
358 359
   For complex cases, many use the Perl and DBD::Pg with CGI.pm or
   mod_perl.
Bruce Momjian's avatar
Bruce Momjian committed
360
   
361 362
  2.3) Does PostgreSQL have a graphical user interface?
  
Bruce Momjian's avatar
Bruce Momjian committed
363 364 365
   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 PostgreSQL Community Documentation
Bruce Momjian's avatar
Bruce Momjian committed
366 367
     _________________________________________________________________
   
368 369
                          Administrative Questions
                                      
370 371
  3.1) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
  
Bruce Momjian's avatar
Bruce Momjian committed
372
   Specify the --prefix option when running configure.
Bruce Momjian's avatar
Bruce Momjian committed
373
   
374 375
  3.2) How do I control connections from other hosts?
  
Bruce Momjian's avatar
Bruce Momjian committed
376
   By default, PostgreSQL only allows connections from the local machine
Bruce Momjian's avatar
Bruce Momjian committed
377 378
   using Unix domain sockets or TCP/IP connections. Other machines will
   not be able to connect unless you modify listen_addresses in the
379
   postgresql.conf file, enable host-based authentication by modifying
380
   the $PGDATA/pg_hba.conf file, and restart the database server.
Bruce Momjian's avatar
Bruce Momjian committed
381
   
382 383
  3.3) How do I tune the database engine for better performance?
  
384
   There are three major areas for potential performance improvement:
Bruce Momjian's avatar
Bruce Momjian committed
385
   
386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410
   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
411
          http://www.powerpostgresql.com/PerfList/ and
Bruce Momjian's avatar
Bruce Momjian committed
412
          http://momjian.us/main/writings/pgsql/hw_performance/index.html
413
          .
414
          
415 416
  3.4) What debugging features are available?
  
Bruce Momjian's avatar
Bruce Momjian committed
417 418 419 420
   There are many log_* server configuration variables that enable
   printing of query and process statistics which can be very useful for
   debugging and performance measurements.
   
421 422
  3.5) Why do I get "Sorry, too many clients" when trying to connect?
  
423 424
   You have reached the default limit of 100 database sessions. You need
   to increase the server's limit on how many concurrent backend
Bruce Momjian's avatar
Bruce Momjian committed
425
   processes it can start by changing the max_connections value in
426
   postgresql.conf and restarting the server.
Bruce Momjian's avatar
Bruce Momjian committed
427
   
428
  3.6) What is the upgrade process for PostgreSQL?
429
  
Bruce Momjian's avatar
Bruce Momjian committed
430
   See http://www.postgresql.org/support/versioning for a general
Bruce Momjian's avatar
Bruce Momjian committed
431
   discussion about upgrading, and
Bruce Momjian's avatar
Bruce Momjian committed
432 433
   http://www.postgresql.org/docs/current/static/install-upgrading.html
   for specific instructions.
434 435 436 437 438 439 440 441 442 443
   
  3.7) 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
   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. Our email lists can be
   used to discuss hardware options and tradeoffs.
444
     _________________________________________________________________
Bruce Momjian's avatar
Bruce Momjian committed
445
   
446 447
                           Operational Questions
                                      
448 449
  4.1) How do I SELECT only the first few rows of a query? A random row?
  
Bruce Momjian's avatar
Bruce Momjian committed
450 451 452 453
   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.
Bruce Momjian's avatar
Bruce Momjian committed
454
   
455 456 457 458 459 460
   To SELECT a random row, use:
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;

461 462 463
  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?
  
Bruce Momjian's avatar
Bruce Momjian committed
464 465 466 467 468 469
   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
470
   also provides an SQL compliant INFORMATION SCHEMA interface you can
Bruce Momjian's avatar
Bruce Momjian committed
471
   query to get information about the database.
Bruce Momjian's avatar
Bruce Momjian committed
472
   
473
   There are also system tables beginning with pg_ that describe these
474 475 476
   too.
   
   Use psql -l will list all databases.
477 478 479 480 481
   
   Also try the file pgsql/src/tutorial/syscat.source. It illustrates
   many of the SELECTs needed to get information from the database system
   tables.
   
482 483
  4.3) How do you change a column's data type?
  
484 485 486 487
   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:
488 489 490
    BEGIN;
    ALTER TABLE tab ADD COLUMN new_col new_data_type;
    UPDATE tab SET new_col = CAST(old_col AS new_data_type);
Bruce Momjian's avatar
Bruce Momjian committed
491
    ALTER TABLE tab DROP COLUMN old_col;
492 493 494 495 496
    COMMIT;

   You might then want to do VACUUM FULL tab to reclaim the disk space
   used by the expired rows.
   
497 498
  4.4) What is the maximum size for a row, a table, and a database?
  
Bruce Momjian's avatar
Bruce Momjian committed
499
   These are the limits:
500 501 502
   
   Maximum size for a database? unlimited (32 TB databases exist)
   Maximum size for a table? 32 TB
503
   Maximum size for a row? 400 GB
504 505 506 507 508 509
   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
   
Bruce Momjian's avatar
Bruce Momjian committed
510
   Of course, these are not actually unlimited, but limited to available
Bruce Momjian's avatar
Bruce Momjian committed
511 512
   disk space and memory/swap space. Performance may suffer when these
   values get unusually large.
Bruce Momjian's avatar
Bruce Momjian committed
513
   
514
   The maximum table size of 32 TB does not require large file support
Bruce Momjian's avatar
Bruce Momjian committed
515
   from the operating system. Large tables are stored as multiple 1 GB
Bruce Momjian's avatar
Bruce Momjian committed
516
   files so file system size limits are not important.
Bruce Momjian's avatar
Bruce Momjian committed
517
   
Bruce Momjian's avatar
Bruce Momjian committed
518
   The maximum table size, row size, and maximum number of columns can be
Bruce Momjian's avatar
Bruce Momjian committed
519 520
   quadrupled by increasing the default block size to 32k. The maximum
   table size can also be increased using table partitioning.
Bruce Momjian's avatar
Bruce Momjian committed
521
   
Bruce Momjian's avatar
Bruce Momjian committed
522 523
   One limitation is that indexes can not be created on columns longer
   than about 2,000 characters. Fortunately, such indexes are rarely
Bruce Momjian's avatar
Bruce Momjian committed
524
   needed. Uniqueness is best guaranteed by a function index of an MD5
525 526
   hash of the long column, and full text indexing allows for searching
   of words within the column.
Bruce Momjian's avatar
Bruce Momjian committed
527
   
528 529 530
  4.5) How much database disk space is required to store data from a typical
  text file?
  
Bruce Momjian's avatar
Bruce Momjian committed
531 532
   A PostgreSQL database may require up to five times the disk space to
   store data from a text file.
533
   
Bruce Momjian's avatar
Bruce Momjian committed
534 535
   As an example, consider a file of 100,000 lines with an integer and
   text description on each line. Suppose the text string avergages
Bruce Momjian's avatar
Bruce Momjian committed
536
   twenty bytes in length. The flat file would be 2.8 MB. The size of the
537
   PostgreSQL database file containing this data can be estimated as 5.2
Bruce Momjian's avatar
Bruce Momjian committed
538
   MB:
539
    24 bytes: each row header (approximate)
540
    24 bytes: one int field and one text field
541 542
   + 4 bytes: pointer on page to tuple
   ----------------------------------------
543
    52 bytes per row
544

545
   The data page size in PostgreSQL is 8192 bytes (8 KB), so:
546 547

   8192 bytes per page
548 549
   -------------------   =  158 rows per database page (rounded down)
     52 bytes per row
550

Bruce Momjian's avatar
Bruce Momjian committed
551
   100000 data rows
552 553
   --------------------  =  633 database pages (rounded up)
      158 rows per page
554

555
633 database pages * 8192 bytes per page  =  5,185,536 bytes (5.2 MB)
556

Bruce Momjian's avatar
Bruce Momjian committed
557
   Indexes do not require as much overhead, but do contain the data that
558 559
   is being indexed, so they can be large also.
   
560
   NULLs are stored as bitmaps, so they use very little space.
Bruce Momjian's avatar
Bruce Momjian committed
561
   
562 563
  4.6) Why are my queries slow? Why don't they use my indexes?
  
564 565 566 567 568
   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.
Bruce Momjian's avatar
Bruce Momjian committed
569 570 571 572 573 574 575 576 577 578 579
   
   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
Bruce Momjian's avatar
Bruce Momjian committed
580 581 582
   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.
583
   
584
   If you believe the optimizer is incorrect in choosing a sequential
585 586
   scan, use SET enable_seqscan TO 'off' and run query again to see if an
   index scan is indeed faster.
587
   
Bruce Momjian's avatar
Bruce Momjian committed
588
   When using wild-card operators such as LIKE or ~, indexes can only be
589 590
   used in certain circumstances:
     * The beginning of the search string must be anchored to the start
Bruce Momjian's avatar
Bruce Momjian committed
591 592 593 594 595
       of the string, i.e.
          + LIKE patterns must not start with %.
          + ~ (regular expression) patterns must start with ^.
     * The search string can not start with a character class, e.g.
       [a-e].
Bruce Momjian's avatar
Bruce Momjian committed
596
     * Case-insensitive searches such as ILIKE and ~* do not utilize
597 598
       indexes. Instead, use expression indexes, which are described in
       section 4.8.
599
     * The default C locale must be used during initdb because it is not
600 601
       possible to know the next-greatest character in a non-C locale.
       You can create a special text_pattern_ops index for such cases
602 603
       that work only for LIKE indexing. It is also possible to use full
       text indexing for word searches.
604
       
605 606
  4.7) How do I see how the query optimizer is evaluating my query?
  
Bruce Momjian's avatar
Bruce Momjian committed
607
   See the EXPLAIN manual page.
608
   
609 610 611 612
  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?
  
Bruce Momjian's avatar
Bruce Momjian committed
613
   The ~ operator does regular expression matching, and ~* does
Bruce Momjian's avatar
Bruce Momjian committed
614
   case-insensitive regular expression matching. The case-insensitive
615
   variant of LIKE is called ILIKE.
Bruce Momjian's avatar
Bruce Momjian committed
616
   
Bruce Momjian's avatar
Bruce Momjian committed
617 618 619
   Case-insensitive equality comparisons are normally expressed as:
    SELECT *
    FROM tab
620
    WHERE lower(col) = 'abc';
Bruce Momjian's avatar
Bruce Momjian committed
621

Bruce Momjian's avatar
Bruce Momjian committed
622 623
   This will not use an standard index. However, if you create an
   expression index, it will be used:
624
    CREATE INDEX tabindex ON tab (lower(col));
Bruce Momjian's avatar
Bruce Momjian committed
625

Bruce Momjian's avatar
Bruce Momjian committed
626 627 628 629 630
   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.
   
631 632
  4.9) 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?
633
  
634
   You test the column with IS NULL and IS NOT NULL, like this:
635 636 637 638
   SELECT *
   FROM tab
   WHERE col IS NULL;

639 640 641 642
   To concatentate with possible NULLs, use COALESCE(), like this:
   SELECT COALESCE(col1, '') || COALESCE(col2, '')
   FROM tab

Bruce Momjian's avatar
Bruce Momjian committed
643
   To sort by the NULL status, use the IS NULL and IS NOT NULL modifiers
644
   in your ORDER BY clause. Things that are true will sort higher than
Bruce Momjian's avatar
Bruce Momjian committed
645 646
   things that are false, so the following will put NULL entries at the
   top of the resulting list:
647 648 649 650
   SELECT *
   FROM tab
   ORDER BY (col IS NOT NULL)

651 652
  4.10) What is the difference between the various character types?
  
653 654 655 656 657 658
        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
659
   
Bruce Momjian's avatar
Bruce Momjian committed
660 661
   You will see the internal name when examining system catalogs and in
   some error messages.
Bruce Momjian's avatar
Bruce Momjian committed
662
   
663
   The first four types above are "varlena" types (i.e., the first four
Bruce Momjian's avatar
Bruce Momjian committed
664
   bytes on disk are the length, followed by the data). Thus the actual
665 666 667
   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.
Bruce Momjian's avatar
Bruce Momjian committed
668
   VARCHAR(n) is best when storing variable-length strings and it limits
Bruce Momjian's avatar
Bruce Momjian committed
669
   how long a string can be. TEXT is for strings of unlimited length,
Bruce Momjian's avatar
Bruce Momjian committed
670
   with a maximum of one gigabyte.
Bruce Momjian's avatar
Bruce Momjian committed
671 672
   
   CHAR(n) is for storing strings that are all the same length. CHAR(n)
Bruce Momjian's avatar
Bruce Momjian committed
673 674
   pads with blanks to the specified length, while VARCHAR(n) only stores
   the characters supplied. BYTEA is for storing binary data,
675 676
   particularly values that include NULL bytes. All the types described
   here have similar performance characteristics.
Bruce Momjian's avatar
Bruce Momjian committed
677
   
678 679
  4.11.1) How do I create a serial/auto-incrementing field?
  
680 681
   PostgreSQL supports a SERIAL data type. It auto-creates a sequence.
   For example, this:
Bruce Momjian's avatar
Bruce Momjian committed
682 683 684 685
    CREATE TABLE person (
        id   SERIAL,
        name TEXT
    );
Bruce Momjian's avatar
Bruce Momjian committed
686

Bruce Momjian's avatar
Bruce Momjian committed
687
   is automatically translated into this:
Bruce Momjian's avatar
Bruce Momjian committed
688 689 690 691 692
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person (
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT
    );
Bruce Momjian's avatar
Bruce Momjian committed
693

694 695 696 697
   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.
Bruce Momjian's avatar
Bruce Momjian committed
698
   
699 700
  4.11.2) How do I get the value of a SERIAL insert?
  
701 702 703
   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;
Bruce Momjian's avatar
Bruce Momjian committed
704

705 706 707
   You can also call nextval() and use that value in the INSERT, or call
   currval() after the INSERT.
   
708 709
  4.11.3) Doesn't currval() lead to a race condition with other users?
  
710 711
   No. currval() returns the current value assigned by your session, not
   by all sessions.
Bruce Momjian's avatar
Bruce Momjian committed
712
   
713 714 715
  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?
  
Bruce Momjian's avatar
Bruce Momjian committed
716
   To improve concurrency, sequence values are given out to running
Bruce Momjian's avatar
Bruce Momjian committed
717
   transactions as needed and are not locked until the transaction
Bruce Momjian's avatar
Bruce Momjian committed
718 719
   completes. This causes gaps in numbering from aborted transactions.
   
720
  4.12) What is an OID? What is a CTID?
721
  
722 723 724 725 726
   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.
Bruce Momjian's avatar
Bruce Momjian committed
727
   
728
   To uniquely number rows in user tables, it is best to use SERIAL
Bruce Momjian's avatar
Bruce Momjian committed
729 730 731
   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.
Bruce Momjian's avatar
Bruce Momjian committed
732
   
733 734 735
   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.
Bruce Momjian's avatar
Bruce Momjian committed
736
   
737 738
  4.13) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
  
739 740
   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
741
   the server:
Bruce Momjian's avatar
Bruce Momjian committed
742 743 744 745 746 747 748 749 750 751
    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.
   
752 753
  4.14) How do I tell what PostgreSQL version I am running?
  
754
   From psql, type SELECT version();
Bruce Momjian's avatar
Bruce Momjian committed
755
   
756
  4.15) How do I create a column that will default to the current time?
757
  
Bruce Momjian's avatar
Bruce Momjian committed
758
   Use CURRENT_TIMESTAMP:
759
    CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Bruce Momjian's avatar
Bruce Momjian committed
760

761
  4.16) How do I perform an outer join?
762
  
763 764
   PostgreSQL supports outer joins using the SQL standard syntax. Here
   are two examples:
Bruce Momjian's avatar
Bruce Momjian committed
765 766 767 768
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

   or
Bruce Momjian's avatar
Bruce Momjian committed
769 770 771
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);

Bruce Momjian's avatar
Bruce Momjian committed
772 773 774 775 776 777
   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.
Bruce Momjian's avatar
Bruce Momjian committed
778
   
779
  4.17) How do I perform queries using multiple databases?
780
  
781 782 783
   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.
Bruce Momjian's avatar
Bruce Momjian committed
784
   
Bruce Momjian's avatar
Bruce Momjian committed
785
   contrib/dblink allows cross-database queries using function calls. Of
786
   course, a client can also make simultaneous connections to different
787
   databases and merge the results on the client side.
Bruce Momjian's avatar
Bruce Momjian committed
788
   
789
  4.18) How do I return multiple rows or columns from a function?
790
  
791
   It is easy using set-returning functions,
792
   http://www.postgresql.org/docs/techdocs.17.
793
   
Bruce Momjian's avatar
Bruce Momjian committed
794 795 796 797 798 799 800 801 802 803 804 805 806 807
  4.19) Why do I get "relation with OID ##### does not exist" errors when
  accessing temporary tables in 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.
   
  4.20) What replication solutions are available?
Bruce Momjian's avatar
Bruce Momjian committed
808
  
Bruce Momjian's avatar
Bruce Momjian committed
809 810 811 812 813 814 815 816 817 818 819
   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
820
   servers. PGCluster is the most popular such solution freely available
Bruce Momjian's avatar
Bruce Momjian committed
821 822 823 824
   for PostgreSQL.
   
   There are also commercial and hardware-based replication solutions
   available supporting a variety of replication models.
Bruce Momjian's avatar
Bruce Momjian committed
825
   
Bruce Momjian's avatar
Bruce Momjian committed
826
  4.21) Why are my table and column names not recognized in my query? Why is
Bruce Momjian's avatar
Bruce Momjian committed
827
  capitalization not preserved?
Bruce Momjian's avatar
Bruce Momjian committed
828
  
Bruce Momjian's avatar
Bruce Momjian committed
829 830 831 832 833 834 835
   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:
Bruce Momjian's avatar
Bruce Momjian committed
836 837 838
     * Avoid double-quoting identifiers when creating tables
     * Use only lowercase characters in identifiers
     * Double-quote identifiers when referencing them in queries