11
22 Frequently Asked Questions (FAQ) for PostgreSQL
33
4- Last updated: Sat Jan 2922:59:12 EST 2005
4+ Last updated: Sat Jan 2923:02:37 EST 2005
55
66 Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77
4949
5050 Operational Questions
5151
52- 4.1) What is the difference between binary cursors and normal cursors?
53- 4.2) How do I SELECT only the first few rows of a query? A random row?
54- 4.3) How do I find out what tables, indexes, databases, and users are
52+ 4.1) How do I SELECT only the first few rows of a query? A random row?
53+ 4.2) How do I find out what tables, indexes, databases, and users are
5554 defined? How do I see the queries used by psql to display them?
56- 4.4 ) How do you remove a column from a table, or change its data type?
57- 4.5 ) What is the maximum size for a row, a table, and a database?
58- 4.6 ) How much database disk space is required to store data from a
55+ 4.3 ) How do you remove a column from a table, or change its data type?
56+ 4.4 ) What is the maximum size for a row, a table, and a database?
57+ 4.5 ) How much database disk space is required to store data from a
5958 typical text file?
60- 4.7 ) My queries are slow or don't make use of the indexes. Why?
61- 4.8 ) How do I see how the query optimizer is evaluating my query?
62- 4.9 ) What is an R-tree index?
63- 4.10 ) What is the Genetic Query Optimizer?
64- 4.11 ) How do I perform regular expression searches and
59+ 4.6 ) My queries are slow or don't make use of the indexes. Why?
60+ 4.7 ) How do I see how the query optimizer is evaluating my query?
61+ 4.8 ) What is an R-tree index?
62+ 4.9 ) What is the Genetic Query Optimizer?
63+ 4.10 ) How do I perform regular expression searches and
6564 case-insensitive regular expression searches? How do I use an index
6665 for case-insensitive searches?
67- 4.12 ) In a query, how do I detect if a field is NULL?
68- 4.13 ) What is the difference between the various character types?
69- 4.14 .0) How do I create a serial/auto-incrementing field?
70- 4.14 .1) How do I get the value of a SERIAL insert?
71- 4.14 .2) Doesn't currval() lead to a race condition with other users?
72- 4.14 .3) Why aren't my sequence numbers reused on transaction abort?
66+ 4.11 ) In a query, how do I detect if a field is NULL?
67+ 4.12 ) What is the difference between the various character types?
68+ 4.13 .0) How do I create a serial/auto-incrementing field?
69+ 4.13 .1) How do I get the value of a SERIAL insert?
70+ 4.13 .2) Doesn't currval() lead to a race condition with other users?
71+ 4.13 .3) Why aren't my sequence numbers reused on transaction abort?
7372 Why are there gaps in the numbering of my sequence/SERIAL column?
74- 4.15 ) What is an OID? What is a TID?
75- 4.16 ) What is the meaning of some of the terms used in PostgreSQL?
76- 4.17 ) Why do I get the error "ERROR: Memory exhausted in
73+ 4.14 ) What is an OID? What is a TID?
74+ 4.15 ) What is the meaning of some of the terms used in PostgreSQL?
75+ 4.16 ) Why do I get the error "ERROR: Memory exhausted in
7776 AllocSetAlloc()"?
78- 4.18 ) How do I tell what PostgreSQL version I am running?
79- 4.19 ) Why does my large-object operations get "invalid large obj
77+ 4.17 ) How do I tell what PostgreSQL version I am running?
78+ 4.18 ) Why does my large-object operations get "invalid large obj
8079 descriptor"?
81- 4.20 ) How do I create a column that will default to the current time?
82- 4.21 ) Why are my subqueries using IN so slow?
83- 4.22 ) How do I perform an outer join?
84- 4.23 ) How do I perform queries using multiple databases?
85- 4.24 ) How do I return multiple rows or columns from a function?
86- 4.25 ) Why can't I reliably create/drop temporary tables in PL/PgSQL
80+ 4.19 ) How do I create a column that will default to the current time?
81+ 4.20 ) Why are my subqueries using IN so slow?
82+ 4.21 ) How do I perform an outer join?
83+ 4.22 ) How do I perform queries using multiple databases?
84+ 4.23 ) How do I return multiple rows or columns from a function?
85+ 4.24 ) Why can't I reliably create/drop temporary tables in PL/PgSQL
8786 functions?
88- 4.26 ) What encryption options are available?
87+ 4.25 ) What encryption options are available?
8988
9089 Extending PostgreSQL
9190
@@ -567,11 +566,7 @@ log_*
567566
568567 Operational Questions
569568
570- 4.1) What is the difference between binary cursors and normal cursors?
571-
572- See the DECLARE manual page for a description.
573-
574- 4.2) How do I SELECT only the first few rows of a query? A random row?
569+ 4.1) How do I SELECT only the first few rows of a query? A random row?
575570
576571 See the FETCH manual page, or use SELECT ... LIMIT....
577572
@@ -587,7 +582,7 @@ log_*
587582 ORDER BY random()
588583 LIMIT 1;
589584
590- 4.3 ) How do I find out what tables, indexes, databases, and users are
585+ 4.2 ) How do I find out what tables, indexes, databases, and users are
591586 defined? How do I see the queries used by psql to display them?
592587
593588 Use the \dt command to see tables in psql. For a complete list of
@@ -606,7 +601,7 @@ log_*
606601 many of the SELECTs needed to get information from the database system
607602 tables.
608603
609- 4.4 ) How do you remove a column from a table, or change its data type?
604+ 4.3 ) How do you remove a column from a table, or change its data type?
610605
611606 DROP COLUMN functionality was added in release 7.3 with ALTER TABLE
612607 DROP COLUMN. In earlier versions, you can do this:
@@ -632,7 +627,7 @@ log_*
632627 You might then want to do VACUUM FULL tab to reclaim the disk space
633628 used by the expired rows.
634629
635- 4.5 ) What is the maximum size for a row, a table, and a database?
630+ 4.4 ) What is the maximum size for a row, a table, and a database?
636631
637632 These are the limits:
638633 Maximum size for a database? unlimited (32 TB databases exist)
@@ -654,7 +649,7 @@ log_*
654649 The maximum table size and maximum number of columns can be quadrupled
655650 by increasing the default block size to 32k.
656651
657- 4.6 ) How much database disk space is required to store data from a typical
652+ 4.5 ) How much database disk space is required to store data from a typical
658653 text file?
659654
660655 A PostgreSQL database may require up to five times the disk space to
@@ -688,7 +683,7 @@ log_*
688683
689684 NULLs are stored as bitmaps, so they use very little space.
690685
691- 4.7 ) My queries are slow or don't make use of the indexes. Why?
686+ 4.6 ) My queries are slow or don't make use of the indexes. Why?
692687
693688 Indexes are not automatically used by every query. Indexes are only
694689 used if the table is larger than a minimum size, and the query selects
@@ -730,7 +725,7 @@ log_*
730725 [a-e].
731726 * Case-insensitive searches such as ILIKE and ~* do not utilize
732727 indexes. Instead, use functional indexes, which are described in
733- section 4.11 .
728+ section 4.10 .
734729 * The default C locale must be used during initdb because it is not
735730 possible to know the next-greater character in a non-C locale. You
736731 can create a special
@@ -743,11 +738,11 @@ LIKE
743738 types exactly match the index's column types. This is particularly
744739 true of int2, int8, and numeric column indexes.
745740
746- 4.8 ) How do I see how the query optimizer is evaluating my query?
741+ 4.7 ) How do I see how the query optimizer is evaluating my query?
747742
748743 See the EXPLAIN manual page.
749744
750- 4.9 ) What is an R-tree index?
745+ 4.8 ) What is an R-tree index?
751746
752747 An R-tree index is used for indexing spatial data. A hash index can't
753748 handle range searches. A B-tree index only handles range searches in a
@@ -770,13 +765,13 @@ LIKE
770765 extending R-trees requires a bit of work and we don't currently have
771766 any documentation on how to do it.
772767
773- 4.10 ) What is the Genetic Query Optimizer?
768+ 4.9 ) What is the Genetic Query Optimizer?
774769
775770 The GEQO module speeds query optimization when joining many tables by
776771 means of a Genetic Algorithm (GA). It allows the handling of large
777772 join queries through nonexhaustive search.
778773
779- 4.11 ) How do I perform regular expression searches and case-insensitive
774+ 4.10 ) How do I perform regular expression searches and case-insensitive
780775 regular expression searches? How do I use an index for case-insensitive
781776 searches?
782777
@@ -793,11 +788,11 @@ LIKE
793788 functional index, it will be used:
794789 CREATE INDEX tabindex ON tab (lower(col));
795790
796- 4.12 ) In a query, how do I detect if a field is NULL?
791+ 4.11 ) In a query, how do I detect if a field is NULL?
797792
798793 You test the column with IS NULL and IS NOT NULL.
799794
800- 4.13 ) What is the difference between the various character types?
795+ 4.12 ) What is the difference between the various character types?
801796
802797Type Internal Name Notes
803798--------------------------------------------------
@@ -825,7 +820,7 @@ BYTEA bytea variable-length byte array (null-byte safe)
825820 particularly values that include NULL bytes. All the types described
826821 here have similar performance characteristics.
827822
828- 4.14 .1) How do I create a serial/auto-incrementing field?
823+ 4.13 .1) How do I create a serial/auto-incrementing field?
829824
830825 PostgreSQL supports a SERIAL data type. It auto-creates a sequence.
831826 For example, this:
@@ -846,11 +841,11 @@ BYTEA bytea variable-length byte array (null-byte safe)
846841 However, if you need to dump and reload the database, you need to use
847842 pg_dump's -o option or COPY WITH OIDS option to preserve the OIDs.
848843
849- 4.14 .2) How do I get the value of a SERIAL insert?
844+ 4.13 .2) How do I get the value of a SERIAL insert?
850845
851846 One approach is to retrieve the next SERIAL value from the sequence
852847 object with the nextval() function before inserting and then insert it
853- explicitly. Using the example table in 4.14 .1, an example in a
848+ explicitly. Using the example table in 4.13 .1, an example in a
854849 pseudo-language would look like this:
855850 new_id = execute("SELECT nextval('person_id_seq')");
856851 execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
@@ -872,19 +867,19 @@ BYTEA bytea variable-length byte array (null-byte safe)
872867 billion. In Perl, using DBI with the DBD::Pg module, the oid value is
873868 made available via $sth->{pg_oid_status} after $sth->execute().
874869
875- 4.14 .3) Doesn't currval() lead to a race condition with other users?
870+ 4.13 .3) Doesn't currval() lead to a race condition with other users?
876871
877872 No. currval() returns the current value assigned by your backend, not
878873 by all users.
879874
880- 4.14 .4) Why aren't my sequence numbers reused on transaction abort? Why are
875+ 4.13 .4) Why aren't my sequence numbers reused on transaction abort? Why are
881876 there gaps in the numbering of my sequence/SERIAL column?
882877
883878 To improve concurrency, sequence values are given out to running
884879 transactions as needed and are not locked until the transaction
885880 completes. This causes gaps in numbering from aborted transactions.
886881
887- 4.15 ) What is an OID? What is a TID?
882+ 4.14 ) What is an OID? What is a TID?
888883
889884 Every row that is created in PostgreSQL gets a unique OID unless
890885 created WITHOUT OIDS. OIDs are autotomatically assigned unique 4-byte
@@ -901,7 +896,7 @@ BYTEA bytea variable-length byte array (null-byte safe)
901896 values. TIDs change after rows are modified or reloaded. They are used
902897 by index entries to point to physical rows.
903898
904- 4.16 ) What is the meaning of some of the terms used in PostgreSQL?
899+ 4.15 ) What is the meaning of some of the terms used in PostgreSQL?
905900
906901 Some of the source code and older documentation use terms that have
907902 more common usage. Here are some:
@@ -919,7 +914,7 @@ BYTEA bytea variable-length byte array (null-byte safe)
919914 http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary
920915 /glossary.html
921916
922- 4.17 ) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
917+ 4.16 ) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
923918
924919 You probably have run out of virtual memory on your system, or your
925920 kernel has a low limit for certain resources. Try this before starting
@@ -934,11 +929,11 @@ BYTEA bytea variable-length byte array (null-byte safe)
934929 problem with the SQL client because the backend is returning too much
935930 data, try it before starting the client.
936931
937- 4.18 ) How do I tell what PostgreSQL version I am running?
932+ 4.17 ) How do I tell what PostgreSQL version I am running?
938933
939934 From psql, type SELECT version();
940935
941- 4.19 ) Why does my large-object operations get "invalid large obj
936+ 4.18 ) Why does my large-object operations get "invalid large obj
942937 descriptor"?
943938
944939 You need to put BEGIN WORK and COMMIT around any use of a large object
@@ -953,12 +948,12 @@ BYTEA bytea variable-length byte array (null-byte safe)
953948 If you are using a client interface like ODBC you may need to set
954949 auto-commit off.
955950
956- 4.20 ) How do I create a column that will default to the current time?
951+ 4.19 ) How do I create a column that will default to the current time?
957952
958953 Use CURRENT_TIMESTAMP:
959954CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
960955
961- 4.21 ) Why are my subqueries using IN so slow?
956+ 4.20 ) Why are my subqueries using IN so slow?
962957
963958 In versions prior to 7.4, subqueries were joined to outer queries by
964959 sequentially scanning the result of the subquery for each row of the
@@ -979,7 +974,7 @@ CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
979974 In version 7.4 and later, IN actually uses the same sophisticated join
980975 techniques as normal queries, and is prefered to using EXISTS.
981976
982- 4.22 ) How do I perform an outer join?
977+ 4.21 ) How do I perform an outer join?
983978
984979 PostgreSQL supports outer joins using the SQL standard syntax. Here
985980 are two examples:
@@ -1009,7 +1004,7 @@ CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
10091004 WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
10101005 ORDER BY col1
10111006
1012- 4.23 ) How do I perform queries using multiple databases?
1007+ 4.22 ) How do I perform queries using multiple databases?
10131008
10141009 There is no way to query a database other than the current one.
10151010 Because PostgreSQL loads database-specific system catalogs, it is
@@ -1019,12 +1014,12 @@ CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
10191014 course, a client can make simultaneous connections to different
10201015 databases and merge the results on the client side.
10211016
1022- 4.24 ) How do I return multiple rows or columns from a function?
1017+ 4.23 ) How do I return multiple rows or columns from a function?
10231018
10241019 In 7.3, you can easily return multiple rows or columns from a
10251020 function, http://techdocs.postgresql.org/guides/SetReturningFunctions.
10261021
1027- 4.25 ) Why can't I reliably create/drop temporary tables in PL/PgSQL
1022+ 4.24 ) Why can't I reliably create/drop temporary tables in PL/PgSQL
10281023 functions?
10291024
10301025 PL/PgSQL caches function contents, and an unfortunate side effect is
@@ -1035,7 +1030,7 @@ CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
10351030 table access in PL/PgSQL. This will cause the query to be reparsed
10361031 every time.
10371032
1038- 4.26 ) What encryption options are available?
1033+ 4.25 ) What encryption options are available?
10391034
10401035 * contrib/pgcrypto contains many encryption functions for use in SQL
10411036 queries.