[Sorry for the duplicate post, sent to wrong list first time]


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@svana.org>   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
      <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 
+     <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?
@@ -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:
+    CREATE UNIQUE INDEX mytable_myfield ON mytable ( lower(myfield) );
+    <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
+    which can give you a case insensitive text data type.
+    <H3><A name="4.22">4.22</A>) Why are PostgreSQL table names 
+    <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.
+    ALTER TABLE "MyTable" RENAME TO MyTable;
+    ALTER TABLE MyTable RENAME "MyField" TO MyField;
+    <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
+    <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.

Attachment: pgpRZKHw9IPsl.pgp
Description: PGP signature

Reply via email to