11
22 Frequently Asked Questions (FAQ) for PostgreSQL
33
4- Last updated:Wed Dec 5 00:41:12 EST2001
4+ Last updated:Fri Jan 4 00:44:42 EST2002
55
66 Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77
5858
5959 Operational Questions
6060
61- 4.1) Why is the system confused about commas, decimal points, and date
62- formats.
63- 4.2) What is the exact difference between binary cursors and normal
64- cursors?
65- 4.3) How do I SELECT only the first few rows of a query?
66- 4.4) How do I get a list of tables or other things I can see in psql?
67- 4.5) How do you remove a column from a table?
68- 4.6) What is the maximum size for a row, table, database?
69- 4.7) How much database disk space is required to store data from a
61+ 4.1) What is the difference between binary cursors and normal cursors?
62+ 4.2) How do I SELECT only the first few rows of a query?
63+ 4.3) How do I get a list of tables or other things I can see in psql?
64+ 4.4) How do you remove a column from a table?
65+ 4.5) What is the maximum size for a row, table, database?
66+ 4.6) How much database disk space is required to store data from a
7067 typical text file?
71- 4.8 ) How do I find out what tables or indexes are defined in the
68+ 4.7 ) How do I find out what tables or indexes are defined in the
7269 database?
73- 4.9 ) My queries are slow or don't make use of the indexes. Why?
74- 4.10 ) How do I see how the query optimizer is evaluating my query?
75- 4.11 ) What is an R-tree index?
76- 4.12 ) What is the Genetic Query Optimizer?
77- 4.13 ) How do I perform regular expression searches and
70+ 4.8 ) My queries are slow or don't make use of the indexes. Why?
71+ 4.9 ) How do I see how the query optimizer is evaluating my query?
72+ 4.10 ) What is an R-tree index?
73+ 4.11 ) What is the Genetic Query Optimizer?
74+ 4.12 ) How do I perform regular expression searches and
7875 case-insensitive regular expression searches? How do I use an index
7976 for case-insensitive searches?
80- 4.14 ) In a query, how do I detect if a field is NULL?
81- 4.15 ) What is the difference between the various character types?
82- 4.16 .1) How do I create a serial/auto-incrementing field?
83- 4.16 .2) How do I get the value of a SERIAL insert?
84- 4.16 .3) Don't currval() and nextval() lead to a race condition with
77+ 4.13 ) In a query, how do I detect if a field is NULL?
78+ 4.14 ) What is the difference between the various character types?
79+ 4.15 .1) How do I create a serial/auto-incrementing field?
80+ 4.15 .2) How do I get the value of a SERIAL insert?
81+ 4.15 .3) Don't currval() and nextval() lead to a race condition with
8582 other users?
86- 4.17 ) What is an OID? What is a TID?
87- 4.18 ) What is the meaning of some of the terms used in PostgreSQL?
88- 4.19 ) Why do I get the error "ERROR: Memory exhausted in
83+ 4.16 ) What is an OID? What is a TID?
84+ 4.17 ) What is the meaning of some of the terms used in PostgreSQL?
85+ 4.18 ) Why do I get the error "ERROR: Memory exhausted in
8986 AllocSetAlloc()?"
90- 4.20 ) How do I tell what PostgreSQL version I am running?
91- 4.21 ) My large-object operations get invalid large obj descriptor.
87+ 4.19 ) How do I tell what PostgreSQL version I am running?
88+ 4.20 ) My large-object operations get invalid large obj descriptor.
9289 Why?
93- 4.22 ) How do I create a column that will default to the current time?
94- 4.23 ) Why are my subqueries using IN so slow?
95- 4.24 ) How do I perform an outer join?
96- 4.25 ) How do I perform queries using multiple databases?
90+ 4.21 ) How do I create a column that will default to the current time?
91+ 4.22 ) Why are my subqueries using IN so slow?
92+ 4.23 ) How do I perform an outer join?
93+ 4.24 ) How do I perform queries using multiple databases?
9794
9895 Extending PostgreSQL
9996
611608
612609 Operational Questions
613610
614- 4.1) Why is system confused about commas, decimal points, and date formats.
615-
616- Check your locale configuration. PostgreSQL uses the locale setting of
617- the user that ran the postmaster process. There are postgres and psql
618- SET commands to control the date format. Set those accordingly for
619- your operating environment.
620-
621- 4.2) What is the exact difference between binary cursors and normal
622- cursors?
611+ 4.1) What is the difference between binary cursors and normal cursors?
623612
624613 See the DECLARE manual page for a description.
625614
626- 4.3 ) How do I SELECT only the first few rows of a query?
615+ 4.2 ) How do I SELECT only the first few rows of a query?
627616
628617 See the FETCH manual page, or use SELECT ... LIMIT....
629618
633622 only the first few records requested, or the entire query may have to
634623 be evaluated until the desired rows have been generated.
635624
636- 4.4 ) How do I get a list of tables or other things I can see in psql?
625+ 4.3 ) How do I get a list of tables or other things I can see in psql?
637626
638627 You can read the source code for psql in file
639628 pgsql/src/bin/psql/describe.c. It contains SQL commands that generate
640629 the output for psql's backslash commands. You can also start psql with
641630 the -E option so it will print out the queries it uses to execute the
642631 commands you give.
643632
644- 4.5 ) How do you remove a column from a table?
633+ 4.4 ) How do you remove a column from a table?
645634
646635 We do not support ALTER TABLE DROP COLUMN, but do this:
647636 SELECT ... -- select all columns but the one you want to remove
650639 DROP TABLE old_table;
651640 ALTER TABLE new_table RENAME TO old_table;
652641
653- 4.6 ) What is the maximum size for a row, table, database?
642+ 4.5 ) What is the maximum size for a row, table, database?
654643
655644 These are the limits:
656645 Maximum size for a database? unlimited (60GB databases exist)
672661 The maximum table size and maximum number of columns can be increased
673662 if the default block size is increased to 32k.
674663
675- 4.7 ) How much database disk space is required to store data from a typical
664+ 4.6 ) How much database disk space is required to store data from a typical
676665 text file?
677666
678667 A PostgreSQL database may need six-and-a-half times the disk space
702691 Indexes do not require as much overhead, but do contain the data that
703692 is being indexed, so they can be large also.
704693
705- 4.8 ) How do I find out what tables or indexes are defined in the database?
694+ 4.7 ) How do I find out what tables or indexes are defined in the database?
706695
707696 psql has a variety of backslash commands to show such information. Use
708697 \? to see them.
711700 many of the SELECTs needed to get information from the database system
712701 tables.
713702
714- 4.9 ) My queries are slow or don't make use of the indexes. Why?
703+ 4.8 ) My queries are slow or don't make use of the indexes. Why?
715704
716705 PostgreSQL does not automatically maintain statistics. VACUUM must be
717706 run to update the statistics. After statistics are updated, the
736725 string. So, to use indices, LIKE searches should not begin with %, and
737726 ~(regular expression searches) should start with ^.
738727
739- 4.10 ) How do I see how the query optimizer is evaluating my query?
728+ 4.9 ) How do I see how the query optimizer is evaluating my query?
740729
741730 See the EXPLAIN manual page.
742731
743- 4.11 ) What is an R-tree index?
732+ 4.10 ) What is an R-tree index?
744733
745734 An R-tree index is used for indexing spatial data. A hash index can't
746735 handle range searches. A B-tree index only handles range searches in a
763752 extending R-trees requires a bit of work and we don't currently have
764753 any documentation on how to do it.
765754
766- 4.12 ) What is the Genetic Query Optimizer?
755+ 4.11 ) What is the Genetic Query Optimizer?
767756
768757 The GEQO module speeds query optimization when joining many tables by
769758 means of a Genetic Algorithm (GA). It allows the handling of large
770759 join queries through nonexhaustive search.
771760
772- 4.13 ) How do I perform regular expression searches and case-insensitive
761+ 4.12 ) How do I perform regular expression searches and case-insensitive
773762 regular expression searches? How do I use an index for case-insensitive
774763 searches?
775764
788777 CREATE INDEX tabindex on tab (lower(col));
789778
790779
791- 4.14 ) In a query, how do I detect if a field is NULL?
780+ 4.13 ) In a query, how do I detect if a field is NULL?
792781
793782 You test the column with IS NULLIS NOT NULL.
794783
795- 4.15 ) What is the difference between the various character types?
784+ 4.14 ) What is the difference between the various character types?
796785
797786Type Internal Name Notes
798787--------------------------------------------------
@@ -817,7 +806,7 @@ BYTEA bytea variable-length byte array (null-safe)
817806 maximum 1 gigabyte. BYTEA is for storing binary data, particularly
818807 values that include NULL bytes.
819808
820- 4.16 .1) How do I create a serial/auto-incrementing field?
809+ 4.15 .1) How do I create a serial/auto-incrementing field?
821810
822811 PostgreSQL supports a SERIAL data type. It auto-creates a sequence and
823812 index on the column. For example, this:
@@ -841,11 +830,11 @@ BYTEA bytea variable-length byte array (null-safe)
841830
842831 Numbering Rows.
843832
844- 4.16 .2) How do I get the value of a SERIAL insert?
833+ 4.15 .2) How do I get the value of a SERIAL insert?
845834
846835 One approach is to to retrieve the next SERIAL value from the sequence
847836 object with the nextval() function before inserting and then insert it
848- explicitly. Using the example table in 4.16 .1, that might look like
837+ explicitly. Using the example table in 4.15 .1, that might look like
849838 this in Perl:
850839 new_id = output of "SELECT nextval('person_id_seq')"
851840 INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');
@@ -867,13 +856,13 @@ BYTEA bytea variable-length byte array (null-safe)
867856 oid value is made available via $sth->{pg_oid_status} after
868857 $sth->execute().
869858
870- 4.16 .3) Don't currval() and nextval() lead to a race condition with other
859+ 4.15 .3) Don't currval() and nextval() lead to a race condition with other
871860 users?
872861
873862 No. Currval() returns the current value assigned by your backend, not
874863 by all users.
875864
876- 4.17 ) What is an OID? What is a TID?
865+ 4.16 ) What is an OID? What is a TID?
877866
878867 OIDs are PostgreSQL's answer to unique row ids. Every row that is
879868 created in PostgreSQL gets a unique OID. All OIDs generated during
@@ -906,7 +895,7 @@ BYTEA bytea variable-length byte array (null-safe)
906895 values. Tids change after rows are modified or reloaded. They are used
907896 by index entries to point to physical rows.
908897
909- 4.18 ) What is the meaning of some of the terms used in PostgreSQL?
898+ 4.17 ) What is the meaning of some of the terms used in PostgreSQL?
910899
911900 Some of the source code and older documentation use terms that have
912901 more common usage. Here are some:
@@ -923,7 +912,7 @@ BYTEA bytea variable-length byte array (null-safe)
923912 A list of general database terms can be found at:
924913 http://www.comptechnews.com/~reaster/dbdesign.html
925914
926- 4.19 ) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()?"
915+ 4.18 ) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()?"
927916
928917 If you are running a version older than 7.1, an upgrade may fix the
929918 problem. Also it is possible you have run out of virtual memory on
@@ -939,11 +928,11 @@ BYTEA bytea variable-length byte array (null-safe)
939928 problem with the SQL client because the backend is returning too much
940929 data, try it before starting the client.
941930
942- 4.20 ) How do I tell what PostgreSQL version I am running?
931+ 4.19 ) How do I tell what PostgreSQL version I am running?
943932
944933 From psql, type select version();
945934
946- 4.21 ) My large-object operations get invalid large obj descriptor. Why?
935+ 4.20 ) My large-object operations get invalid large obj descriptor. Why?
947936
948937 You need to put BEGIN WORK and COMMIT around any use of a large object
949938 handle, that is, surrounding lo_open ... lo_close.
@@ -957,12 +946,12 @@ BYTEA bytea variable-length byte array (null-safe)
957946 If you are using a client interface like ODBC you may need to set
958947 auto-commit off.
959948
960- 4.22 ) How do I create a column that will default to the current time?
949+ 4.21 ) How do I create a column that will default to the current time?
961950
962951 Use CURRENT_TIMESTAMP:
963952CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
964953
965- 4.23 ) Why are my subqueries using IN so slow?
954+ 4.22 ) Why are my subqueries using IN so slow?
966955
967956 Currently, we join subqueries to outer queries by sequentially
968957 scanning the result of the subquery for each row of the outer query. A
@@ -978,7 +967,7 @@ SELECT *
978967
979968 We hope to fix this limitation in a future release.
980969
981- 4.24 ) How do I perform an outer join?
970+ 4.23 ) How do I perform an outer join?
982971
983972 PostgreSQL 7.1 and later supports outer joins using the SQL standard
984973 syntax. Here are two examples:
@@ -1008,7 +997,7 @@ SELECT *
1008997 WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
1009998 ORDER BY col1
1010999
1011- 4.25 ) How do I perform queries using multiple databases?
1000+ 4.24 ) How do I perform queries using multiple databases?
10121001
10131002 There is no way to query any database except the current one. Because
10141003 PostgreSQL loads database-specific system catalogs, it is uncertain