2 Frequently Asked Questions (FAQ) for PostgreSQL
4 Last updated: Thu Aug 11 09:21:58 EDT 2005
6 Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
8 The most recent version of this document can be viewed at
9 http://www.postgresql.org/files/documentation/faqs/FAQ.html.
11 Platform-specific questions are answered at
12 http://www.postgresql.org/docs/faq/.
13 _________________________________________________________________
17 1.1) What is PostgreSQL? How is it pronounced?
18 1.2) What is the copyright of PostgreSQL?
19 1.3) What platforms does PostgreSQL support?
20 1.4) Where can I get PostgreSQL?
21 1.5) Where can I get support?
22 1.6) How do I submit a bug report?
23 1.7) What is the latest release?
24 1.8) What documentation is available?
25 1.9) How do I find out about known bugs or missing features?
26 1.10) How can I learn SQL?
27 1.11) How do I join the development team?
28 1.12) How does PostgreSQL compare to other DBMSs?
29 1.13) Who controls PostgreSQL?
33 2.1) What interfaces are available for PostgreSQL?
34 2.2) What tools are available for using PostgreSQL with Web pages?
35 2.3) Does PostgreSQL have a graphical user interface?
37 Administrative Questions
39 3.1) How do I install PostgreSQL somewhere other than
41 3.2) How do I control connections from other hosts?
42 3.3) How do I tune the database engine for better performance?
43 3.4) What debugging features are available?
44 3.5) Why do I get "Sorry, too many clients" when trying to connect?
45 3.6) Why do I need to do a dump and restore to upgrade PostgreSQL
47 3.7) What computer hardware should I use?
51 4.1) How do I SELECT only the first few rows of a query? A random row?
52 4.2) How do I find out what tables, indexes, databases, and users are
53 defined? How do I see the queries used by psql to display them?
54 4.3) How do you change a column's data type?
55 4.4) What is the maximum size for a row, a table, and a database?
56 4.5) How much database disk space is required to store data from a
58 4.6) Why are my queries slow? Why don't they use my indexes?
59 4.7) How do I see how the query optimizer is evaluating my query?
60 4.8) How do I perform regular expression searches and case-insensitive
61 regular expression searches? How do I use an index for
62 case-insensitive searches?
63 4.9) In a query, how do I detect if a field is NULL? How can I sort on
64 whether a field is NULL or not?
65 4.10) What is the difference between the various character types?
66 4.11.1) How do I create a serial/auto-incrementing field?
67 4.11.2) How do I get the value of a SERIAL insert?
68 4.11.3) Doesn't currval() lead to a race condition with other users?
69 4.11.4) Why aren't my sequence numbers reused on transaction abort?
70 Why are there gaps in the numbering of my sequence/SERIAL column?
71 4.12) What is an OID? What is a CTID?
72 4.13) Why do I get the error "ERROR: Memory exhausted in
74 4.14) How do I tell what PostgreSQL version I am running?
75 4.15) How do I create a column that will default to the current time?
76 4.16) How do I perform an outer join?
77 4.17) How do I perform queries using multiple databases?
78 4.18) How do I return multiple rows or columns from a function?
79 4.19) Why do I get "relation with OID ##### does not exist" errors
80 when accessing temporary tables in PL/PgSQL functions?
81 4.20) What replication solutions are available?
82 4.21) Why are my table and column names not recognized in my query?
83 _________________________________________________________________
87 1.1) What is PostgreSQL? How is it pronounced?
89 PostgreSQL is pronounced Post-Gres-Q-L, and is also sometimes referred
90 to as just Postgres. An audio file is available in MP3 format for
91 those would like to hear the pronunciation.
93 PostgreSQL is an object-relational database system that has the
94 features of traditional commercial database systems with enhancements
95 to be found in next-generation DBMS systems. PostgreSQL is free and
96 the complete source code is available.
98 PostgreSQL development is performed by a team of mostly volunteer
99 developers spread throughout the world and communicating via the
100 Internet. It is a community project and is not controlled by any
101 company. To get involved, see the developer's FAQ at
102 http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html
104 1.2) What is the copyright of PostgreSQL?
106 PostgreSQL is distributed under the classic BSD license. It has no
107 restrictions on how the source code can be used. We like it and have
108 no intention of changing it.
110 This is the BSD license we use:
112 PostgreSQL Data Base Management System
114 Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
115 Portions Copyright (c) 1994-1996 Regents of the University of
118 Permission to use, copy, modify, and distribute this software and its
119 documentation for any purpose, without fee, and without a written
120 agreement is hereby granted, provided that the above copyright notice
121 and this paragraph and the following two paragraphs appear in all
124 IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
125 FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
126 INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND
127 ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN
128 ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
130 THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
131 INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
132 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
133 PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
134 CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,
135 UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
137 1.3) What platforms does PostgreSQL support?
139 In general, any modern Unix-compatible platform should be able to run
140 PostgreSQL. The platforms that had received explicit testing at the
141 time of release are listed in the installation instructions.
143 PostgreSQL also runs natively on Microsoft Windows NT-based operating
144 systems like Win2000, WinXP, and Win2003. A prepackaged installer is
145 available at http://pgfoundry.org/projects/pginstaller. MSDOS-based
146 versions of Windows (Win95, Win98, WinMe) can run PostgreSQL using
149 There is also a Novell Netware 6 port at http://forge.novell.com, and
150 an OS/2 (eComStation) version at
151 http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgre
152 SQL&stype=all&sort=type&dir=%2F.
154 1.4) Where can I get PostgreSQL?
156 Via web browser, use http://www.postgresql.org/ftp/, and via ftp, use
157 ftp://ftp.PostgreSQL.org/pub/.
159 1.5) Where can I get support?
161 The PostgreSQL community provides assistance to many of its users via
162 email. The main web site to subscribe to the email lists is
163 http://www.postgresql.org/community/lists/. The general or bugs lists
164 are a good place to start.
166 The major IRC channel is #postgresql on Freenode (irc.freenode.net).
167 To connect you can use the Unix program irc -c '#postgresql' "$USER"
168 irc.freenode.net or use any other IRC clients. A Spanish one also
169 exists on the same network, (#postgresql-es), and a French one,
170 (#postgresqlfr). There is also a PostgreSQL channel on EFNet.
172 A list of commercial support companies is available at
173 http://techdocs.postgresql.org/companies.php.
175 1.6) How do I submit a bug report?
177 Visit the PostgreSQL bug form at
178 http://www.postgresql.org/support/submitbug.
180 Also check out our ftp site ftp://ftp.PostgreSQL.org/pub/ to see if
181 there is a more recent PostgreSQL version.
183 1.7) What is the latest release?
185 The latest release of PostgreSQL is version 8.0.2.
187 We plan to have a major release every year, with minor releases every
190 1.8) What documentation is available?
192 PostgreSQL includes extensive documentation, including a large manual,
193 manual pages, and some test examples. See the /doc directory. You can
194 also browse the manuals online at http://www.PostgreSQL.org/docs.
196 There are two PostgreSQL books available online at
197 http://www.postgresql.org/docs/books/awbook.html and
198 http://www.commandprompt.com/ppbook/. There are a number of PostgreSQL
199 books available for purchase. One of the most popular ones is by Korry
200 Douglas. A list of book reviews can be found at
201 http://techdocs.PostgreSQL.org/techdocs/bookreviews.php. There is also
202 a collection of PostgreSQL technical articles at
203 http://techdocs.PostgreSQL.org/.
205 The command line client program psql has some \d commands to show
206 information about types, operators, functions, aggregates, etc. - use
207 \? to display the available commands.
209 Our web site contains even more documentation.
211 1.9) How do I find out about known bugs or missing features?
213 PostgreSQL supports an extended subset of SQL-92. See our TODO list
214 for known bugs, missing features, and future plans.
216 1.10) How can I learn SQL?
218 First, consider the PostgreSQL-specific books mentioned above. Another
219 one is "Teach Yourself SQL in 21 Days, Second Edition" at
220 http://members.tripod.com/er4ebus/sql/index.htm. Many of our users
221 like The Practical SQL Handbook, Bowman, Judith S., et al.,
222 Addison-Wesley. Others like The Complete Reference SQL, Groff et al.,
225 There is also a nice tutorial at
226 http://www.intermedia.net/support/sql/sqltut.shtm, at
227 http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,
228 and at http://sqlcourse.com.
230 1.11) How do I join the development team?
232 See the Developer's FAQ.
234 1.12) How does PostgreSQL compare to other DBMSs?
236 There are several ways of measuring software: features, performance,
237 reliability, support, and price.
240 PostgreSQL has most features present in large commercial DBMSs,
241 like transactions, subselects, triggers, views, foreign key
242 referential integrity, and sophisticated locking. We have some
243 features they do not have, like user-defined types,
244 inheritance, rules, and multi-version concurrency control to
245 reduce lock contention.
248 PostgreSQL's performance is comparable to other commercial and
249 open source databases. It is faster for some things, slower for
250 others. Our performance is usually +/-10% compared to other
254 We realize that a DBMS must be reliable, or it is worthless. We
255 strive to release well-tested, stable code that has a minimum
256 of bugs. Each release has at least one month of beta testing,
257 and our release history shows that we can provide stable, solid
258 releases that are ready for production use. We believe we
259 compare favorably to other database software in this area.
262 Our mailing lists provide contact with a large group of
263 developers and users to help resolve any problems encountered.
264 While we cannot guarantee a fix, commercial DBMSs do not always
265 supply a fix either. Direct access to developers, the user
266 community, manuals, and the source code often make PostgreSQL
267 support superior to other DBMSs. There is commercial
268 per-incident support available for those who need it. (See FAQ
272 We are free for all use, both commercial and non-commercial.
273 You can add our code to your product with no limitations,
274 except those outlined in our BSD-style license stated above.
276 1.13) Who controls PostgreSQL?
278 If you are looking for a PostgreSQL gatekeeper, central committee, or
279 controlling company, give up --- there isn't one. We do have a core
280 committee and CVS committers, but these groups are more for
281 administrative purposes than control. The project is directed by the
282 community of developers and users, which anyone can join. All you need
283 to do is subscribe to the mailing lists and participate in the
284 discussions. (See the Developer's FAQ for information on how to get
285 involved in PostgreSQL development.)
286 _________________________________________________________________
288 User Client Questions
290 2.1) What interfaces are available for PostgreSQL?
292 The PostgreSQL install includes only the C and embedded C interfaces.
293 All other interfaces are independent projects that are downloaded
294 separately; being separate allows them to have their own release
295 schedule and development teams.
297 Some programming languages like PHP include an interface to
298 PostgreSQL. Interfaces for languages like Perl, TCL, Python, and many
299 others are available at http://gborg.postgresql.org in the
300 Drivers/Interfaces section and via Internet search.
302 2.2) What tools are available for using PostgreSQL with Web pages?
304 A nice introduction to Database-backed Web pages can be seen at:
305 http://www.webreview.com
307 For Web integration, PHP (http://www.php.net) is an excellent
310 For complex cases, many use the Perl and DBD::Pg with CGI.pm or
313 2.3) Does PostgreSQL have a graphical user interface?
315 Yes, see http://techdocs.postgresql.org/guides/GUITools for a detailed
317 _________________________________________________________________
319 Administrative Questions
321 3.1) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
323 Specify the --prefix option when running configure.
325 3.2) How do I control connections from other hosts?
327 By default, PostgreSQL only allows connections from the local machine
328 using Unix domain sockets or TCP/IP connections. Other machines will
329 not be able to connect unless you modify listen_addresses in the
330 postgresql.conf file, enable host-based authentication by modifying
331 the $PGDATA/pg_hba.conf file, and restart the server.
333 3.3) How do I tune the database engine for better performance?
335 There are three major areas for potential performance improvement:
338 This involves modifying queries to obtain better performance:
340 + Creation of indexes, including expression and partial indexes
341 + Use of COPY instead of multiple INSERTs
342 + Grouping of multiple statements into a single transaction to
343 reduce commit overhead
344 + Use of CLUSTER when retrieving many rows from an index
345 + Use of LIMIT for returning a subset of a query's output
346 + Use of Prepared queries
347 + Use of ANALYZE to maintain accurate optimizer statistics
348 + Regular use of VACUUM or pg_autovacuum
349 + Dropping of indexes during large data changes
352 A number of postgresql.conf settings affect performance. For
353 more details, see Administration Guide/Server Run-time
354 Environment/Run-time Configuration for a full listing, and for
356 http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_co
358 http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
361 The effect of hardware on performance is detailed in
362 http://candle.pha.pa.us/main/writings/pgsql/hw_performance/inde
363 x.html and http://www.powerpostgresql.com/PerfList/.
365 3.4) What debugging features are available?
367 There are many log_* server configuration variables that enable
368 printing of query and process statistics which can be very useful for
369 debugging and performance measurements.
371 3.5) Why do I get "Sorry, too many clients" when trying to connect?
373 You have reached the default limit is 100 database sessions. You need
374 to increase the postmaster's limit on how many concurrent backend
375 processes it can start by changing the max_connections value in
376 postgresql.conf and restarting the postmaster.
378 3.6) Why do I need to do a dump and restore to upgrade between major
381 The PostgreSQL team makes only small changes between minor releases,
382 so upgrading from 7.4.0 to 7.4.1 does not require a dump and restore.
383 However, major releases (e.g. from 7.3 to 7.4) often change the
384 internal format of system tables and data files. These changes are
385 often complex, so we don't maintain backward compatibility for data
386 files. A dump outputs data in a generic format that can then be loaded
387 in using the new internal format.
389 3.7) What computer hardware should I use?
391 Because PC hardware is mostly compatible, people tend to believe that
392 all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and
393 quality motherboards are more reliable and have better performance
394 than less expensive hardware. PostgreSQL will run on almost any
395 hardware, but if reliability and performance are important it is wise
396 to research your hardware options thoroughly. Our email lists can be
397 used to discuss hardware options and tradeoffs.
398 _________________________________________________________________
400 Operational Questions
402 4.1) How do I SELECT only the first few rows of a query? A random row?
404 To retrieve only a few rows, if you know at the number of rows needed
405 at the time of the SELECT use LIMIT . If an index matches the ORDER BY
406 it is possible the entire query does not have to be executed. If you
407 don't know the number of rows at SELECT time, use a cursor and FETCH.
409 To SELECT a random row, use:
415 4.2) How do I find out what tables, indexes, databases, and users are
416 defined? How do I see the queries used by psql to display them?
418 Use the \dt command to see tables in psql. For a complete list of
419 commands inside psql you can use \?. Alternatively you can read the
420 source code for psql in file pgsql/src/bin/psql/describe.c, it
421 contains SQL commands that generate the output for psql's backslash
422 commands. You can also start psql with the -E option so it will print
423 out the queries it uses to execute the commands you give. PostgreSQL
424 also provides an SQL compliant INFORMATION SCHEMA interface you can
425 query to get information about the database.
427 There are also system tables beginning with pg_ that describe these
430 Use psql -l will list all databases.
432 Also try the file pgsql/src/tutorial/syscat.source. It illustrates
433 many of the SELECTs needed to get information from the database system
436 4.3) How do you change a column's data type?
438 Changing the data type of a column can be done easily in 8.0 and later
439 with ALTER TABLE ALTER COLUMN TYPE.
441 In earlier releases, do this:
443 ALTER TABLE tab ADD COLUMN new_col new_data_type;
444 UPDATE tab SET new_col = CAST(old_col AS new_data_type);
445 ALTER TABLE tab DROP COLUMN old_col;
448 You might then want to do VACUUM FULL tab to reclaim the disk space
449 used by the expired rows.
451 4.4) What is the maximum size for a row, a table, and a database?
453 These are the limits:
455 Maximum size for a database? unlimited (32 TB databases exist)
456 Maximum size for a table? 32 TB
457 Maximum size for a row? 1.6TB
458 Maximum size for a field? 1 GB
459 Maximum number of rows in a table? unlimited
460 Maximum number of columns in a table? 250-1600 depending on column
462 Maximum number of indexes on a table? unlimited
464 Of course, these are not actually unlimited, but limited to available
465 disk space and memory/swap space. Performance may suffer when these
466 values get unusually large.
468 The maximum table size of 32 TB does not require large file support
469 from the operating system. Large tables are stored as multiple 1 GB
470 files so file system size limits are not important.
472 The maximum table size and maximum number of columns can be quadrupled
473 by increasing the default block size to 32k.
475 One limitation is that indexes can not be created on columns longer
476 than about 2,000 characters. Fortunately, such indexes are rarely
477 needed. Uniqueness is best guaranteed by a funtion index of an MD5
478 hash of the long column, and full text indexing allows for searching
479 of words within the column.
481 4.5) How much database disk space is required to store data from a typical
484 A PostgreSQL database may require up to five times the disk space to
485 store data from a text file.
487 As an example, consider a file of 100,000 lines with an integer and
488 text description on each line. Suppose the text string avergages
489 twenty bytes in length. The flat file would be 2.8 MB. The size of the
490 PostgreSQL database file containing this data can be estimated as 6.4
492 32 bytes: each row header (approximate)
493 24 bytes: one int field and one text field
494 + 4 bytes: pointer on page to tuple
495 ----------------------------------------
498 The data page size in PostgreSQL is 8192 bytes (8 KB), so:
501 ------------------- = 136 rows per database page (rounded down)
505 -------------------- = 735 database pages (rounded up)
508 735 database pages * 8192 bytes per page = 6,021,120 bytes (6 MB)
510 Indexes do not require as much overhead, but do contain the data that
511 is being indexed, so they can be large also.
513 NULLs are stored as bitmaps, so they use very little space.
515 4.6) Why are my queries slow? Why don't they use my indexes?
517 Indexes are not used by every query. Indexes are used only if the
518 table is larger than a minimum size, and the query selects only a
519 small percentage of the rows in the table. This is because the random
520 disk access caused by an index scan can be slower than a straight read
521 through the table, or sequential scan.
523 To determine if an index should be used, PostgreSQL must have
524 statistics about the table. These statistics are collected using
525 VACUUM ANALYZE, or simply ANALYZE. Using statistics, the optimizer
526 knows how many rows are in the table, and can better determine if
527 indexes should be used. Statistics are also valuable in determining
528 optimal join order and join methods. Statistics collection should be
529 performed periodically as the contents of the table change.
531 Indexes are normally not used for ORDER BY or to perform joins. A
532 sequential scan followed by an explicit sort is usually faster than an
533 index scan of a large table.
534 However, LIMIT combined with ORDER BY often will use an index because
535 only a small portion of the table is returned. In fact, though MAX()
536 and MIN() don't use indexes, it is possible to retrieve such values
537 using an index with ORDER BY and LIMIT:
540 ORDER BY col [ DESC ]
543 If you believe the optimizer is incorrect in choosing a sequential
544 scan, use SET enable_seqscan TO 'off' and run query again to see if an
545 index scan is indeed faster.
547 When using wild-card operators such as LIKE or ~, indexes can only be
548 used in certain circumstances:
549 * The beginning of the search string must be anchored to the start
551 + LIKE patterns must not start with %.
552 + ~ (regular expression) patterns must start with ^.
553 * The search string can not start with a character class, e.g.
555 * Case-insensitive searches such as ILIKE and ~* do not utilize
556 indexes. Instead, use expression indexes, which are described in
558 * The default C locale must be used during initdb because it is not
559 possible to know the next-greatest character in a non-C locale.
560 You can create a special text_pattern_ops index for such cases
561 that work only for LIKE indexing.
563 In pre-8.0 releases, indexes often can not be used unless the data
564 types exactly match the index's column types. This was particularly
565 true of int2, int8, and numeric column indexes.
567 4.7) How do I see how the query optimizer is evaluating my query?
569 See the EXPLAIN manual page.
571 4.8) How do I perform regular expression searches and case-insensitive
572 regular expression searches? How do I use an index for case-insensitive
575 The ~ operator does regular expression matching, and ~* does
576 case-insensitive regular expression matching. The case-insensitive
577 variant of LIKE is called ILIKE.
579 Case-insensitive equality comparisons are normally expressed as:
582 WHERE lower(col) = 'abc';
584 This will not use an standard index. However, if you create a
585 expresssion index, it will be used:
586 CREATE INDEX tabindex ON tab (lower(col));
588 If the above index is created as UNIQUE, though the column can store
589 upper and lowercase characters, it can not have identical values that
590 differ only in case. To force a particular case to be stored in the
591 column, use a CHECK constraint or a trigger.
593 4.9) In a query, how do I detect if a field is NULL? How can I sort on
594 whether a field is NULL or not?
596 You test the column with IS NULL and IS NOT NULL, like this:
601 To sort by the NULL status, use the IS NULL and IS NOT NULL modifiers
602 in your ORDER BY clause. Things that are true will sort higher than
603 things that are false, so the following will put NULL entries at the
604 top of the resulting list:
607 ORDER BY (col IS NOT NULL)
609 4.10) What is the difference between the various character types?
611 Type Internal Name Notes
612 VARCHAR(n) varchar size specifies maximum length, no padding
613 CHAR(n) bpchar blank padded to the specified fixed length
614 TEXT text no specific upper limit on length
615 BYTEA bytea variable-length byte array (null-byte safe)
616 "char" char one character
618 You will see the internal name when examining system catalogs and in
621 The first four types above are "varlena" types (i.e., the first four
622 bytes on disk are the length, followed by the data). Thus the actual
623 space used is slightly greater than the declared size. However, long
624 values are also subject to compression, so the space on disk might
625 also be less than expected.
626 VARCHAR(n) is best when storing variable-length strings and it limits
627 how long a string can be. TEXT is for strings of unlimited length,
628 with a maximum of one gigabyte.
630 CHAR(n) is for storing strings that are all the same length. CHAR(n)
631 pads with blanks to the specified length, while VARCHAR(n) only stores
632 the characters supplied. BYTEA is for storing binary data,
633 particularly values that include NULL bytes. All the types described
634 here have similar performance characteristics.
636 4.11.1) How do I create a serial/auto-incrementing field?
638 PostgreSQL supports a SERIAL data type. It auto-creates a sequence.
640 CREATE TABLE person (
645 is automatically translated into this:
646 CREATE SEQUENCE person_id_seq;
647 CREATE TABLE person (
648 id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
652 See the create_sequence manual page for more information about
655 4.11.2) How do I get the value of a SERIAL insert?
657 One approach is to retrieve the next SERIAL value from the sequence
658 object with the nextval() function before inserting and then insert it
659 explicitly. Using the example table in 4.11.1, an example in a
660 pseudo-language would look like this:
661 new_id = execute("SELECT nextval('person_id_seq')");
662 execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
664 You would then also have the new value stored in new_id for use in
665 other queries (e.g., as a foreign key to the person table). Note that
666 the name of the automatically created SEQUENCE object will be named
667 <table>_< serialcolumn>_seq, where table and serialcolumn are the
668 names of your table and your SERIAL column, respectively.
670 Alternatively, you could retrieve the assigned SERIAL value with the
671 currval() function after it was inserted by default, e.g.,
672 execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
673 new_id = execute("SELECT currval('person_id_seq')");
675 4.11.3) Doesn't currval() lead to a race condition with other users?
677 No. currval() returns the current value assigned by your session, not
680 4.11.4) Why aren't my sequence numbers reused on transaction abort? Why are
681 there gaps in the numbering of my sequence/SERIAL column?
683 To improve concurrency, sequence values are given out to running
684 transactions as needed and are not locked until the transaction
685 completes. This causes gaps in numbering from aborted transactions.
687 4.12) What is an OID? What is a CTID?
689 Every row that is created in PostgreSQL gets a unique OID unless
690 created WITHOUT OIDS. OIDs are autotomatically assigned unique 4-byte
691 integers that are unique across the entire installation. However, they
692 overflow at 4 billion, and then the OIDs start being duplicated.
693 PostgreSQL uses OIDs to link its internal system tables together.
695 To uniquely number columns in user tables, it is best to use SERIAL
696 rather than OIDs because SERIAL sequences are unique only within a
697 single table. and are therefore less likely to overflow. SERIAL8 is
698 available for storing eight-byte sequence values.
700 CTIDs are used to identify specific physical rows with block and
701 offset values. CTIDs change after rows are modified or reloaded. They
702 are used by index entries to point to physical rows.
704 4.13) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
706 You probably have run out of virtual memory on your system, or your
707 kernel has a low limit for certain resources. Try this before starting
712 Depending on your shell, only one of these may succeed, but it will
713 set your process data segment limit much higher and perhaps allow the
714 query to complete. This command applies to the current process, and
715 all subprocesses created after the command is run. If you are having a
716 problem with the SQL client because the backend is returning too much
717 data, try it before starting the client.
719 4.14) How do I tell what PostgreSQL version I am running?
721 From psql, type SELECT version();
723 4.15) How do I create a column that will default to the current time?
725 Use CURRENT_TIMESTAMP:
726 CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
728 4.16) How do I perform an outer join?
730 PostgreSQL supports outer joins using the SQL standard syntax. Here
733 FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
737 FROM t1 LEFT OUTER JOIN t2 USING (col);
739 These identical queries join t1.col to t2.col, and also return any
740 unjoined rows in t1 (those with no match in t2). A RIGHT join would
741 add unjoined rows of t2. A FULL join would return the matched rows
742 plus all unjoined rows from t1 and t2. The word OUTER is optional and
743 is assumed in LEFT, RIGHT, and FULL joins. Ordinary joins are called
746 4.17) How do I perform queries using multiple databases?
748 There is no way to query a database other than the current one.
749 Because PostgreSQL loads database-specific system catalogs, it is
750 uncertain how a cross-database query should even behave.
752 contrib/dblink allows cross-database queries using function calls. Of
753 course, a client can also make simultaneous connections to different
754 databases and merge the results on the client side.
756 4.18) How do I return multiple rows or columns from a function?
758 It is easy using set-returning functions,
759 http://techdocs.postgresql.org/guides/SetReturningFunctions
762 4.19) Why do I get "relation with OID ##### does not exist" errors when
763 accessing temporary tables in PL/PgSQL functions?
765 PL/PgSQL caches function scripts, and an unfortunate side effect is
766 that if a PL/PgSQL function accesses a temporary table, and that table
767 is later dropped and recreated, and the function called again, the
768 function will fail because the cached function contents still point to
769 the old temporary table. The solution is to use EXECUTE for temporary
770 table access in PL/PgSQL. This will cause the query to be reparsed
773 4.20) What replication solutions are available?
775 Though "replication" is a single term, there are several technologies
776 for doing replication, with advantages and disadvantages for each.
778 Master/slave replication allows a single master to receive read/write
779 queries, while slaves can only accept read/SELECT queries. The most
780 popular freely available master-slave PostgreSQL replication solution
783 Multi-master replication allows read/write queries to be sent to
784 multiple replicated computers. This capability also has a severe
785 impact on performance due to the need to synchronize changes between
786 servers. Pgcluster is the most popular such solution freely available
789 There are also commercial and hardware-based replication solutions
790 available supporting a variety of replication models.
792 4.20) Why are my table and column names not recognized in my query?
794 The most common cause is the use of double-quotes around table or
795 column names during table creation. When double-quotes are used, table
796 and column names (called identifiers) are stored case-sensitive,
797 meaning you must use double-quotes when referencing the names in a
798 query. Some interfaces, like pgAdmin, automatically double-quote
799 identifiers during table creation. So, for identifiers to be
800 recognized, you must either:
801 * Avoid double-quoting identifiers when creating tables
802 * Use only lowercase characters in identifiers
803 * Double-quote identifiers when referencing them in queries