Re: 5 new entries for FAQ - Mailing list pgsql-general

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> 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.

I have updated the FAQ to handle three of the items you mentioned.  In
one case, I added a new FAQ entry (double-quoting identifiers with a
link to our docs), and in two other cases (indexing long columns,
case-insensitive columns) I added to existing FAQ items where
appropriate.  Patch attached and applied.

For the batch job and single-CPU issues, they did not fit into existing
FAQ entries, and I am not sure they are asked enough to be added as
FAQs.  I am interested to hear other's opinions on this.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/FAQ/FAQ.html
===================================================================
RCS file: /cvsroot/pgsql/doc/src/FAQ/FAQ.html,v
retrieving revision 1.310
diff -c -c -r1.310 FAQ.html
*** doc/src/FAQ/FAQ.html    30 May 2005 13:11:06 -0000    1.310
--- doc/src/FAQ/FAQ.html    10 Aug 2005 19:26:19 -0000
***************
*** 116,122 ****
      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>
!

      <HR>

--- 116,124 ----
      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>) Why are my table and column names not
!     recognized in my query?<BR>
!

      <HR>

***************
*** 613,618 ****
--- 617,628 ----
      <P>The maximum table size and maximum number of columns can be
      quadrupled by increasing the default block size to 32k.</P>

+     <P>One limitation is that indexes can not be created on columns
+     longer than about 2,000 characters. Fortunately, such indexes are
+     rarely needed. Uniqueness is best guaranteed using another column
+     that is an MD5 hash of the long column, and full text indexing
+     allows for searching of words within the column.</P>
+
      <H3><A name="4.5">4.5</A>) How much database disk space is required
      to store data from a typical text file?</H3>

***************
*** 740,746 ****
  <PRE>
      CREATE INDEX tabindex ON tab (lower(col));
  </PRE>
!
      <H3><A name="4.9">4.9</A>) In a query, how do I detect if a field
      is <SMALL>NULL</SMALL>?  How can I sort on whether a field is <SMALL>
      NULL</SMALL> or not?</H3>
--- 750,761 ----
  <PRE>
      CREATE INDEX tabindex ON tab (lower(col));
  </PRE>
!     <P>If the above index is created as <SMALL>UNIQUE</SMALL>, though
!     the column can store upper and lowercase characters, it can not have
!     identical values that differ only in case. To force a particular
!     case to be stored in the column, use a <SMALL>CHECK</SMALL>
!     constraint or a trigger.</P>
!
      <H3><A name="4.9">4.9</A>) In a query, how do I detect if a field
      is <SMALL>NULL</SMALL>?  How can I sort on whether a field is <SMALL>
      NULL</SMALL> or not?</H3>
***************
*** 1000,1004 ****
--- 1015,1036 ----

      <P>There are also commercial and hardware-based replication solutions
      available supporting a variety of replication models.</P>
+
+     <H3><A name="4.20">4.20</A>) Why are my table and column names not
+     recognized in my query?</H3>
+
+     <P>The most common cause is the use of double-quotes around table or
+     column names during table creation. When double-quotes are used,
+     table and column names (called identifiers) are stored <a
+     href="http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-
+     SYNTAX-IDENTIFIERS">case-sensitive</a>, meaning you must use
+     double-quotes when referencing the names in a query. Some interfaces,
+     like pgAdmin, automatically double-quote identifiers during table
+     creation. So, for identifiers to be recognized, you must either:
+     <UL>
+     <LI>Avoid double-quoting identifiers when creating tables</LI>
+     <LI>Use only lowercase characters in identifiers</LI>
+     <LI>Double-quote identifiers when referencing them in queries</LI>
+     </UL>
    </BODY>
  </HTML>