1414alink ="#0000ff ">
1515< H1 > Frequently Asked Questions (FAQ) for PostgreSQL</ H1 >
1616
17- < P > Last updated: Mon Jun 2421:45:50 EDT 2002</ P >
17+ < P > Last updated: Mon Jun 2423:32:16 EDT 2002</ P >
1818
1919< P > Current maintainer: Bruce Momjian (< A href =
2020 "mailto:pgman@candle.pha.pa.us "> pgman@candle.pha.pa.us</ A > )< BR >
@@ -282,6 +282,7 @@ <H4><A name="1.6">1.6</A>) Where can I get support?</H4>
282282 subscribe
283283 end
284284</ PRE >
285+
285286 Digests are sent out to members of this list whenever the main list
286287 has received around 30k of messages.
287288
@@ -293,6 +294,7 @@ <H4><A name="1.6">1.6</A>) Where can I get support?</H4>
293294 subscribe
294295 end
295296</ PRE >
297+
296298 There is also a developers discussion mailing list available. To
297299 subscribe to this list, send email to< A href =
298300 "mailto:pgsql-hackers-request@PostgreSQL.org "> pgsql-hackers-request@PostgreSQL.org</ A >
@@ -860,6 +862,7 @@ <H4><A name="4.5">4.5</A>) What is the maximum size for a row, a
860862 Maximum number of columns in a table? 250-1600 depending on column types
861863 Maximum number of indexes on a table? unlimited
862864</ PRE >
865+
863866 Of course, these are not actually unlimited, but limited to
864867 available disk space and memory/swap space. Performance may suffer
865868 when these values get unusually large.
@@ -951,11 +954,24 @@ <H4><A name="4.8">4.8</A>) My queries are slow or don't make use of
951954</ PRE >
952955
953956< P > When using wild-card operators such as< SMALL > LIKE</ SMALL > or
954- < I > ~</ I > , indexes can only be used if the default C local is used
955- during initdb and the beginning of the search
956- is anchored to the start of the string. Therefore, to use indexes,
957- < SMALL > LIKE</ SMALL > patterns must not start with< I > %</ I > , and
958- < I > ~</ I > (regular expression) patterns must start with< I > ^</ I > .</ P >
957+ < I > ~</ I > , indexes can only be used in certain circumstances:
958+ < UL >
959+ < LI > The beginning of the search string must be anchored to the start
960+ of the string, i.e.:</ LI >
961+ < UL >
962+ < LI > < SMALL > LIKE</ SMALL > patterns must not start with< I > %.</ I > </ LI >
963+ < LI > < I > ~</ I > (regular expression) patterns must start with
964+ < I > ^.</ I > </ LI >
965+ </ UL >
966+ < LI > The search string can not start with a character class,
967+ e.g. [a-e].</ LI >
968+ < LI > Case-insensitive searches like< SMALL > ILIKE</ SMALL > and
969+ < I > ~*</ I > can not be used. Instead, use functional
970+ indexes, which are described later in this FAQ.</ LI >
971+ < LI > The default< I > C</ I > local must have been used during
972+ < i > initdb.</ i > </ LI >
973+ </ UL >
974+ < P >
959975
960976< H4 > < A name ="4.9 "> 4.9</ A > ) How do I see how the query optimizer is
961977 evaluating my query?</ H4 >
@@ -1010,13 +1026,12 @@ <H4><A name="4.12">4.12</A>) How do I perform regular expression
10101026 SELECT *
10111027 FROM tab
10121028 WHERE lower(col) = 'abc'
1013-
10141029</ PRE >
1030+
10151031 This will not use an standard index. However, if you create a
10161032 functional index, it will be used:
10171033< PRE >
10181034 CREATE INDEX tabindex on tab (lower(col));
1019-
10201035</ PRE >
10211036
10221037< H4 > < A name ="4.13 "> 4.13</ A > ) In a query, how do I detect if a field
@@ -1066,6 +1081,7 @@ <H4><A name="4.15.1">4.15.1</A>) How do I create a
10661081 name TEXT
10671082 );
10681083</ PRE >
1084+
10691085 is automatically translated into this:
10701086< PRE >
10711087 CREATE SEQUENCE person_id_seq;
@@ -1075,6 +1091,7 @@ <H4><A name="4.15.1">4.15.1</A>) How do I create a
10751091 );
10761092 CREATE UNIQUE INDEX person_id_key ON person ( id );
10771093</ PRE >
1094+
10781095 See the< I > create_sequence</ I > manual page for more information
10791096 about sequences. You can also use each row's< I > OID</ I > field as a
10801097 unique value. However, if you need to dump and reload the database,
@@ -1093,6 +1110,7 @@ <H4><A name="4.15.2">4.15.2</A>) How do I get the value of a
10931110 new_id = output of "SELECT nextval('person_id_seq')"
10941111 INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');
10951112</ PRE >
1113+
10961114 You would then also have the new value stored in
10971115< CODE > new_id</ CODE > for use in other queries (e.g., as a foreign
10981116 key to the< CODE > person</ CODE > table). Note that the name of the
@@ -1108,6 +1126,7 @@ <H4><A name="4.15.2">4.15.2</A>) How do I get the value of a
11081126 INSERT INTO person (name) VALUES ('Blaise Pascal');
11091127 new_id = output of "SELECT currval('person_id_seq')";
11101128</ PRE >
1129+
11111130 Finally, you could use the< A href ="#4.16 "> < SMALL > OID</ SMALL > </ A >
11121131 returned from the< SMALL > INSERT</ SMALL > statement to look up the
11131132 default value, though this is probably the least portable approach.
@@ -1215,6 +1234,7 @@ <H4><A name="4.18">4.18</A>) Why do I get the error <I>"ERROR:
12151234 ulimit -d 262144
12161235 limit datasize 256m
12171236</ PRE >
1237+
12181238 Depending on your shell, only one of these may succeed, but it will
12191239 set your process data segment limit much higher and perhaps allow
12201240 the query to complete. This command applies to the current process,
@@ -1273,6 +1293,7 @@ <H4><A name="4.22">4.22</A>) Why are my subqueries using
12731293 WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
12741294</ CODE >
12751295</ PRE >
1296+
12761297 We hope to fix this limitation in a future release.
12771298
12781299< H4 > < A name ="4.23 "> 4.23</ A > ) How do I perform an outer join?</ H4 >