[Sorry for the duplicate post, sent to wrong list first time] 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 <firstname.lastname@example.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 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>
Description: PGP signature