Hi, After going through pgsql-general a bit I figured there were a few important questions missing from the FAQ, so I wrote some. Comments welcome. I can write more, if people can suggest things to write about. I was thinking something about collation and locales but I'm sure sure I understand them myself. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Index: FAQ.html
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/FAQ/FAQ.html,v
retrieving revision 1.310
diff -u -r1.310 FAQ.html
--- FAQ.html 30 May 2005 13:11:06 -0000 1.310
+++ FAQ.html 10 Aug 2005 07:34:41 -0000
@@ -116,7 +116,11 @@
does not exist" errors when accessing temporary tables in PL/PgSQL
functions?<BR>
<A href="#4.20">4.20</A>) What replication solutions are available?<BR>
-
+ <A href="#4.21">4.21</A>) How can I make PostgreSQL compare strings ignoring case like some other databases?<BR>
+ <A href="#4.22">4.22</A>) Why are PostgreSQL tables names case-sensitive?<BR>
+ <A href="#4.23">4.23</A>) Why is PostgreSQL only using one CPU to execute my query?<BR>
+ <A href="#4.24">4.24</A>) How can I arrange to have a query run at a certain time of day? Regular intervals?<BR>
+ <A href="#4.25">4.25</A>) What does 'index row size ... exceeds btree maximum, 2713' mean?
<HR>
@@ -1000,5 +1004,114 @@
<P>There are also commercial and hardware-based replication solutions
available supporting a variety of replication models.</P>
+
+ <H3><A name="4.21">4.21</A>) How can I make PostgreSQL compare strings
+ ignoring case like some other databases?</H3>
+
+ <P>All text types in PostgreSQL are case-sensitive. This is generally
+ what you want. If you have an identifier in a table that must be a
+ particular format, it's better to use a <SMALL>CHECK</SMALL> constraint.
+ If you want to avoid duplicates that differ only in case, create a
+ UNIQUE index like so:
+
+<PRE>
+ CREATE UNIQUE INDEX mytable_myfield ON mytable ( lower(myfield) );
+</PRE>
+
+ <P>If you want to do case-insensitive searches then that index can help
+ you also (in a non-unique form). For regular expressions, see <A
+ href="#4.8">FAQ 4.8</A>.
+
+ <P>If you really need to have a column that is case-insensitive (perhaps
+ for porting) there is a project on <A
+ href="http://gborg.postgresql.org/";>GBorg</A> called <A
+ href="http://gborg.postgresql.org/project/citext/projdisplay.php";>citext</A>
+ which can give you a case insensitive text data type.
+
+ <H3><A name="4.22">4.22</A>) Why are PostgreSQL table names case-sensitive?</H3>
+
+ <P>Actually, what happens is that unquoted identifiers are folded to a
+ single case before comparison. The SQL standard requires folding to
+ uppercase, PostgreSQL folds to lowercase. What this means is that the
+ identifiers <SMALL>MyTable</SMALL>, <SMALL>Mytable</SMALL> and
+ <SMALL>"mytable"</SMALL> all compare the same. But
+ <SMALL>"MyTable"</SMALL>, <SMALL>"Mytable"</SMALL> and
+ <SMALL>"MYTABLE"</SMALL> are all different, even in totally SQL
+ compliant databases.
+
+ <P>The usual cause for this complaint is that people use a GUI admin
+ interface to create their tables and it has helpfully quoted the table
+ and field names so that only identical quoting everywhere else can match
+ it.
+
+ <P>A good rule of thumb is to either <b>always</b> quote your identifiers
+ (in which case you must get the case right all the time) or <b>never</b>
+ quote them (in which case the case never matters).
+
+ <P>If this is your problem, then you can use your admin interface to rename
+ all the columns to all-lowercase names. Or execute queries like those
+ below.
+
+<PRE>
+ ALTER TABLE "MyTable" RENAME TO MyTable;
+ ALTER TABLE MyTable RENAME "MyField" TO MyField;
+</PRE>
+
+ <H3><A name="4.23">4.23</A>) Why is PostgreSQL only using one CPU to execute my query?</H3>
+
+ <P>The PostgreSQL process model has one single threaded backend handling
+ each client. This means that multiple queries from different clients
+ will have their loads spread out as determined most appropriate by the
+ operating system. But within a single query there is only a single
+ thread of execution, so a single query is run only on one CPU (though
+ the OS can move it around, PostgreSQL is unaware of this).
+
+ <P>Usually this is not a problem as the system is generally I/O bound
+ (limited by disk speed), not CPU bound. However, for some unusual
+ workloads you may need to think about connecting more than once to the
+ same database so you can have different portions work in parallel.
+
+ <H3><A name="4.24">4.24</A>) How can I arrange to have a query run at a certain time of day? Regular intervals?</H3>
+
+ <P>PostgreSQL itself has no facility fire jobs off at regular intervals,
+ it is recommended to use the operating system to handle this. For UNIX
+ systems this generally means <SMALL>cron</SMALL>, for Windows systems it
+ may mean the Task Scheduler.
+
+ <P>A seperate project has started with the goal to create a job
+ scheduler for PostgreSQL, see <a
+ href="http://gborg.postgresql.org/project/pgjobs/projdisplay.php";>pgjobs</a>.
+
+ <H3><A name="4.25">4.25</A>) What does 'index row size ... exceeds btree maximum, 2713' mean?</H3>
+
+ <P>Unlike some databases, PostgreSQL allows you to create an index on
+ any column, including unlimited text fields. However, B-Tree indexes
+ need to be able to hold at least three key values per page and since the
+ pagesize defaults to 8K minus some overhead, this means the maximum key
+ size is 2713 bytes.
+
+ <P>Usually it serves no purpose to have an index on such a column. If
+ you're doing it for searches, remember that a normal index doesn't help
+ searching within a string (eg <SMALL>LIKE '%abc%'</SMALL>). What you are
+ probably looking for is Full Text Indexing, see <a
+ href="http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/";>TSearch2</A>
+ and <A href="http://openfts.sourceforge.net/";>OpenFTS</A>. If you're not
+ actually doing searching in that column then perhaps you don't need that
+ index at all.
+
+ <P>If all you want to do is make sure the values are unique, the
+ recommended technique is to use a trigger to calculate an MD5 of the
+ value and put a unique index on this. Obviously this won't help searching.
+
+ <P>If you're really desparate, you can increase the pagesize a bit (to
+ 32K) but this requires recompiling and reinstalling your database from
+ scratch. From then on it will be incompatable with other people's
+ databases, so think carefully whether your successor will remember to do
+ this next time they upgrade.
+
+ <P>Also note, that no index type can ever exceed the pagesize limit,
+ although hash indexes can go up to just under one page. Full text
+ indexing works because it doesn't store the whole key directly.
+
</BODY>
</HTML>
Attachment:
pgphU1NWkHZeF.pgp
Description: PGP signature
