FAQ 46.4 KB
Newer Older
B
Bruce Momjian 已提交
1

B
Bruce Momjian 已提交
2
                Frequently Asked Questions (FAQ) for PostgreSQL
B
Bruce Momjian 已提交
3
                                       
B
Bruce Momjian 已提交
4
   Last updated: Mon Jun 10 15:49:44 EDT 2002
B
Bruce Momjian 已提交
5
   
B
Bruce Momjian 已提交
6
   Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
B
Bruce Momjian 已提交
7
   
B
Bruce Momjian 已提交
8 9
   The most recent version of this document can be viewed at
   http://www.PostgreSQL.org/docs/faq-english.html.
B
Bruce Momjian 已提交
10
   
B
Bruce Momjian 已提交
11
   Platform-specific questions are answered at
B
Bruce Momjian 已提交
12
   http://www.PostgreSQL.org/users-lounge/docs/faq.html.
B
Bruce Momjian 已提交
13 14
     _________________________________________________________________
   
B
Bruce Momjian 已提交
15
                             General Questions
B
Bruce Momjian 已提交
16
                                      
B
Bruce Momjian 已提交
17 18
   1.1) What is PostgreSQL? How is it pronounced?
   1.2) What is the copyright on PostgreSQL?
B
Bruce Momjian 已提交
19
   1.3) What Unix platforms does PostgreSQL run on?
B
Bruce Momjian 已提交
20
   1.4) What non-Unix ports are available?
B
Bruce Momjian 已提交
21
   1.5) Where can I get PostgreSQL?
B
Bruce Momjian 已提交
22 23 24
   1.6) Where can I get support?
   1.7) What is the latest release?
   1.8) What documentation is available?
B
Bruce Momjian 已提交
25 26 27 28 29
   1.9) How do I find out about known bugs or missing features?
   1.10) How can I learn SQL?
   1.11) Is PostgreSQL Y2K compliant?
   1.12) How do I join the development team?
   1.13) How do I submit a bug report?
B
Bruce Momjian 已提交
30
   1.14) How does PostgreSQL compare to other DBMSs?
B
Bruce Momjian 已提交
31
   1.15) How can I financially assist PostgreSQL?
B
Bruce Momjian 已提交
32 33
   
                           User Client Questions
B
Bruce Momjian 已提交
34
                                      
B
Bruce Momjian 已提交
35
   2.1) Are there ODBC drivers for PostgreSQL?
B
Bruce Momjian 已提交
36
   2.2) What tools are available for using PostgreSQL with Web pages?
B
Bruce Momjian 已提交
37 38 39 40 41 42
   2.3) Does PostgreSQL have a graphical user interface? A report
   generator? An embedded query language interface?
   2.4) What languages are available to communicate with PostgreSQL?
   
                          Administrative Questions
                                      
B
Bruce Momjian 已提交
43
   3.1) How do I install PostgreSQL somewhere other than
B
Bruce Momjian 已提交
44
   /usr/local/pgsql?
B
Bruce Momjian 已提交
45 46 47 48 49
   3.2) When I start postmaster, I get a Bad System Call or core dumped
   message. Why?
   3.3) When I try to start postmaster, I get IpcMemoryCreate errors.
   Why?
   3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors.
B
Bruce Momjian 已提交
50
   Why?
B
Bruce Momjian 已提交
51 52 53 54 55
   3.5) How do I control connections from other hosts?
   3.6) How do I tune the database engine for better performance?
   3.7) What debugging features are available?
   3.8) Why do I get "Sorry, too many clients" when trying to connect?
   3.9) What are the pg_sorttempNNN.NN files in my database directory?
B
Bruce Momjian 已提交
56 57
   
                           Operational Questions
B
Bruce Momjian 已提交
58
                                      
B
Bruce Momjian 已提交
59 60 61 62
   4.1) What is the difference between binary cursors and normal cursors?
   4.2) How do I SELECT only the first few rows of a query?
   4.3) How do I get a list of tables or other things I can see in psql?
   4.4) How do you remove a column from a table?
B
Bruce Momjian 已提交
63
   4.5) What is the maximum size for a row, a table, and a database?
B
Bruce Momjian 已提交
64
   4.6) How much database disk space is required to store data from a
B
Bruce Momjian 已提交
65
   typical text file?
66 67
   4.7) How do I find out what tables, indexes, databases, and users are
   defined?
B
Bruce Momjian 已提交
68 69 70 71 72
   4.8) My queries are slow or don't make use of the indexes. Why?
   4.9) How do I see how the query optimizer is evaluating my query?
   4.10) What is an R-tree index?
   4.11) What is the Genetic Query Optimizer?
   4.12) How do I perform regular expression searches and
B
Bruce Momjian 已提交
73 74
   case-insensitive regular expression searches? How do I use an index
   for case-insensitive searches?
B
Bruce Momjian 已提交
75 76 77 78 79
   4.13) In a query, how do I detect if a field is NULL?
   4.14) What is the difference between the various character types?
   4.15.1) How do I create a serial/auto-incrementing field?
   4.15.2) How do I get the value of a SERIAL insert?
   4.15.3) Don't currval() and nextval() lead to a race condition with
B
Bruce Momjian 已提交
80
   other users?
B
Bruce Momjian 已提交
81
   4.15.4) Why aren't my sequence numbers reused on transaction abort?
B
Bruce Momjian 已提交
82
   Why are there gaps in the numbering of my sequence/SERIAL column?
B
Bruce Momjian 已提交
83 84 85
   4.16) What is an OID? What is a TID?
   4.17) What is the meaning of some of the terms used in PostgreSQL?
   4.18) Why do I get the error "ERROR: Memory exhausted in
B
Bruce Momjian 已提交
86
   AllocSetAlloc()"?
B
Bruce Momjian 已提交
87
   4.19) How do I tell what PostgreSQL version I am running?
B
Bruce Momjian 已提交
88 89
   4.20) Why does my large-object operations get "invalid large obj
   descriptor"?
B
Bruce Momjian 已提交
90 91 92 93
   4.21) How do I create a column that will default to the current time?
   4.22) Why are my subqueries using IN so slow?
   4.23) How do I perform an outer join?
   4.24) How do I perform queries using multiple databases?
B
Bruce Momjian 已提交
94
   4.25) How do I return multiple rows or columns from a function?
95 96
   4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
   functions?
B
Bruce Momjian 已提交
97
   
B
Bruce Momjian 已提交
98
                            Extending PostgreSQL
B
Bruce Momjian 已提交
99
                                      
B
Bruce Momjian 已提交
100
   5.1) I wrote a user-defined function. When I run it in psql, why does
B
Bruce Momjian 已提交
101
   it dump core?
B
Bruce Momjian 已提交
102
   5.2) How can I contribute some nifty new types and functions to
B
Bruce Momjian 已提交
103
   PostgreSQL?
B
Bruce Momjian 已提交
104
   5.3) How do I write a C function to return a tuple?
B
Bruce Momjian 已提交
105
   5.4) I have changed a source file. Why does the recompile not see the
B
Bruce Momjian 已提交
106
   change?
B
Bruce Momjian 已提交
107 108
     _________________________________________________________________
   
B
Bruce Momjian 已提交
109 110 111 112
                             General Questions
                                      
    1.1) What is PostgreSQL?
    
B
Bruce Momjian 已提交
113 114
   PostgreSQL is pronounced Post-Gres-Q-L.
   
B
Bruce Momjian 已提交
115 116 117 118 119 120
   PostgreSQL is an enhancement of the POSTGRES database management
   system, a next-generation DBMS research prototype. While PostgreSQL
   retains the powerful data model and rich data types of POSTGRES, it
   replaces the PostQuel query language with an extended subset of SQL.
   PostgreSQL is free and the complete source is available.
   
B
Bruce Momjian 已提交
121 122 123 124 125
   PostgreSQL development is performed by a team of Internet developers
   who all subscribe to the PostgreSQL development mailing list. The
   current coordinator is Marc G. Fournier (scrappy@PostgreSQL.org). (See
   below on how to join). This team is now responsible for all
   development of PostgreSQL.
B
Bruce Momjian 已提交
126 127
   
   The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many
B
Bruce Momjian 已提交
128
   others have contributed to the porting, testing, debugging, and
B
Bruce Momjian 已提交
129 130 131 132 133 134 135 136 137 138
   enhancement of the code. The original Postgres code, from which
   PostgreSQL is derived, was the effort of many graduate students,
   undergraduate students, and staff programmers working under the
   direction of Professor Michael Stonebraker at the University of
   California, Berkeley.
   
   The original name of the software at Berkeley was Postgres. When SQL
   functionality was added in 1995, its name was changed to Postgres95.
   The name was changed at the end of 1996 to PostgreSQL.
   
B
Bruce Momjian 已提交
139
    1.2) What is the copyright on PostgreSQL?
B
Bruce Momjian 已提交
140
    
B
Bruce Momjian 已提交
141
   PostgreSQL is subject to the following COPYRIGHT:
B
Bruce Momjian 已提交
142 143 144
   
   PostgreSQL Data Base Management System
   
B
Bruce Momjian 已提交
145
   Portions copyright (c) 1996-2002, PostgreSQL Global Development Group
B
Bruce Momjian 已提交
146
   Portions Copyright (c) 1994-6 Regents of the University of California
B
Bruce Momjian 已提交
147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
   
   Permission to use, copy, modify, and distribute this software and its
   documentation for any purpose, without fee, and without a written
   agreement is hereby granted, provided that the above copyright notice
   and this paragraph and the following two paragraphs appear in all
   copies.
   
   IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
   FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
   INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND
   ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN
   ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
   
   THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
   INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
   MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
   PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
   CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,
   UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
   
B
Bruce Momjian 已提交
167 168 169 170
   The above is the BSD license, the classic open-source license. It has
   no restrictions on how the source code may be used. We like it and
   have no intention of changing it.
   
B
Bruce Momjian 已提交
171 172
    1.3) What Unix platforms does PostgreSQL run on?
    
B
Bruce Momjian 已提交
173 174 175 176
   In general, a modern Unix-compatible platform should be able to run
   PostgreSQL. The platforms that had received explicit testing at the
   time of release are listed in the installation instructions.
   
B
Bruce Momjian 已提交
177
    1.4) What non-Unix ports are available?
B
Bruce Momjian 已提交
178
    
B
Bruce Momjian 已提交
179 180
   Client
   
B
Bruce Momjian 已提交
181 182 183
   It is possible to compile the libpq C library, psql, and other
   interfaces and binaries to run on MS Windows platforms. In this case,
   the client is running on MS Windows, and communicates via TCP/IP to a
B
Bruce Momjian 已提交
184 185
   server running on one of our supported Unix platforms. A file
   win31.mak is included in the distribution for making a Win32 libpq
B
Bruce Momjian 已提交
186
   library and psql. PostgreSQL also communicates with ODBC clients.
B
Bruce Momjian 已提交
187
   
B
Bruce Momjian 已提交
188 189
   Server
   
B
Bruce Momjian 已提交
190 191 192 193
   The database server can run on Windows NT and Win2k using Cygwin, the
   Cygnus Unix/NT porting library. See pgsql/doc/FAQ_MSWIN in the
   distribution or the MS Windows FAQ on our web site. We have no plan to
   do a native port to any Microsoft platform.
B
Bruce Momjian 已提交
194 195 196 197
   
    1.5) Where can I get PostgreSQL?
    
   The primary anonymous ftp site for PostgreSQL is
B
Bruce Momjian 已提交
198
   ftp://ftp.PostgreSQL.org/pub. For mirror sites, see our main web site.
B
Bruce Momjian 已提交
199
   
B
Bruce Momjian 已提交
200
    1.6) Where can I get support?
B
Bruce Momjian 已提交
201
    
B
Bruce Momjian 已提交
202
   The main mailing list is: pgsql-general@PostgreSQL.org. It is
B
Bruce Momjian 已提交
203
   available for discussion of matters pertaining to PostgreSQL. To
B
Bruce Momjian 已提交
204
   subscribe, send mail with the following lines in the body (not the
B
Bruce Momjian 已提交
205
   subject line):
B
Bruce Momjian 已提交
206 207
    subscribe
    end
B
Bruce Momjian 已提交
208

B
Bruce Momjian 已提交
209
   to pgsql-general-request@PostgreSQL.org.
B
Bruce Momjian 已提交
210 211
   
   There is also a digest list available. To subscribe to this list, send
B
Bruce Momjian 已提交
212
   email to: pgsql-general-digest-request@PostgreSQL.org with a body of:
B
Bruce Momjian 已提交
213 214
    subscribe
    end
B
Bruce Momjian 已提交
215 216 217 218

   Digests are sent out to members of this list whenever the main list
   has received around 30k of messages.
   
B
Bruce Momjian 已提交
219
   The bugs mailing list is available. To subscribe to this list, send
B
Bruce Momjian 已提交
220
   email to pgsql-bugs-request@PostgreSQL.org with a body of:
B
Bruce Momjian 已提交
221 222
    subscribe
    end
B
Bruce Momjian 已提交
223

B
Bruce Momjian 已提交
224
   There is also a developers discussion mailing list available. To
B
Bruce Momjian 已提交
225 226
   subscribe to this list, send email to
   pgsql-hackers-request@PostgreSQL.org with a body of:
B
Bruce Momjian 已提交
227 228
    subscribe
    end
B
Bruce Momjian 已提交
229

B
Bruce Momjian 已提交
230 231
   Additional mailing lists and information about PostgreSQL can be found
   via the PostgreSQL WWW home page at:
B
Bruce Momjian 已提交
232
   
B
Bruce Momjian 已提交
233
     http://www.PostgreSQL.org
B
Bruce Momjian 已提交
234
     
B
Bruce Momjian 已提交
235
   There is also an IRC channel on EFNet, channel #PostgreSQL. I use the
B
Bruce Momjian 已提交
236
   Unix command irc -c '#PostgreSQL' "$USER" irc.phoenix.net.
B
Bruce Momjian 已提交
237
   
B
Bruce Momjian 已提交
238
   A list of commercial support companies is available at
B
Bruce Momjian 已提交
239
   http://www.postgresql.org/users-lounge/commercial-support.html.
240
   
B
Bruce Momjian 已提交
241
    1.7) What is the latest release?
B
Bruce Momjian 已提交
242
    
B
Bruce Momjian 已提交
243
   The latest release of PostgreSQL is version 7.2.1.
B
Bruce Momjian 已提交
244
   
B
Bruce Momjian 已提交
245
   We plan to have major releases every four months.
B
Bruce Momjian 已提交
246
   
B
Bruce Momjian 已提交
247
    1.8) What documentation is available?
B
Bruce Momjian 已提交
248
    
B
Bruce Momjian 已提交
249
   Several manuals, manual pages, and some small test examples are
B
Bruce Momjian 已提交
250
   included in the distribution. See the /doc directory. You can also
B
Bruce Momjian 已提交
251
   browse the manual online at
B
Bruce Momjian 已提交
252
   http://www.PostgreSQL.org/users-lounge/docs/.
B
Bruce Momjian 已提交
253
   
B
Bruce Momjian 已提交
254 255 256
   There are two PostgreSQL books available online at
   http://www.PostgreSQL.org/docs/awbook.html and
   http://www.commandprompt.com/ppbook/. There is a list of PostgreSQL
B
Bruce Momjian 已提交
257 258 259
   books available for purchase at http://www.postgresql.org/books/.
   There is also a collection of PostgreSQL technical articles at
   http://techdocs.postgresql.org/.
B
Bruce Momjian 已提交
260 261 262
   
   psql has some nice \d commands to show information about types,
   operators, functions, aggregates, etc.
B
Bruce Momjian 已提交
263
   
B
Bruce Momjian 已提交
264
   Our web site contains even more documentation.
B
Bruce Momjian 已提交
265 266 267
   
    1.9) How do I find out about known bugs or missing features?
    
B
Bruce Momjian 已提交
268 269
   PostgreSQL supports an extended subset of SQL-92. See our TODO list
   for known bugs, missing features, and future plans.
B
Bruce Momjian 已提交
270 271 272
   
    1.10) How can I learn SQL?
    
B
Bruce Momjian 已提交
273
   The PostgreSQL book at http://www.PostgreSQL.org/docs/awbook.html
B
Bruce Momjian 已提交
274 275 276 277 278
   teaches SQL. There is another PostgreSQL book at
   http://www.commandprompt.com/ppbook. There is a nice tutorial at
   http://www.intermedia.net/support/sql/sqltut.shtm, at
   http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,
   and at http://sqlcourse.com.
B
Bruce Momjian 已提交
279 280
   
   Another one is "Teach Yourself SQL in 21 Days, Second Edition" at
B
Bruce Momjian 已提交
281
   http://members.tripod.com/er4ebus/sql/index.htm
B
Bruce Momjian 已提交
282
   
B
Bruce Momjian 已提交
283
   Many of our users like The Practical SQL Handbook, Bowman, Judith S.,
B
Bruce Momjian 已提交
284
   et al., Addison-Wesley. Others like The Complete Reference SQL, Groff
B
Bruce Momjian 已提交
285
   et al., McGraw-Hill.
B
Bruce Momjian 已提交
286 287 288
   
    1.11) Is PostgreSQL Y2K compliant?
    
B
Bruce Momjian 已提交
289
   Yes, we easily handle dates past the year 2000 AD, and before 2000 BC.
B
Bruce Momjian 已提交
290
   
B
Bruce Momjian 已提交
291 292
    1.12) How do I join the development team?
    
B
Bruce Momjian 已提交
293
   First, download the latest source and read the PostgreSQL Developers
B
Bruce Momjian 已提交
294
   documentation on our web site, or in the distribution. Second,
B
Bruce Momjian 已提交
295
   subscribe to the pgsql-hackers and pgsql-patches mailing lists. Third,
B
Bruce Momjian 已提交
296
   submit high quality patches to pgsql-patches.
B
Bruce Momjian 已提交
297
   
B
Bruce Momjian 已提交
298
   There are about a dozen people who have commit privileges to the
B
Bruce Momjian 已提交
299
   PostgreSQL CVS archive. They each have submitted so many high-quality
B
Bruce Momjian 已提交
300 301 302
   patches that it was impossible for the existing committers to keep up,
   and we had confidence that patches they committed were of high
   quality.
B
Bruce Momjian 已提交
303
   
B
Bruce Momjian 已提交
304 305
    1.13) How do I submit a bug report?
    
B
Bruce Momjian 已提交
306 307
   Please visit the PostgreSQL BugTool page, which gives guidelines and
   directions on how to submit a bug.
B
Bruce Momjian 已提交
308
   
B
Bruce Momjian 已提交
309
   Also check out our ftp site ftp://ftp.PostgreSQL.org/pub to see if
B
Bruce Momjian 已提交
310 311
   there is a more recent PostgreSQL version or patches.
   
B
Bruce Momjian 已提交
312
    1.14) How does PostgreSQL compare to other DBMSs?
B
Bruce Momjian 已提交
313 314 315 316 317
    
   There are several ways of measuring software: features, performance,
   reliability, support, and price.
   
   Features
B
Bruce Momjian 已提交
318 319 320 321
          PostgreSQL has most features present in large commercial DBMSs,
          like transactions, subselects, triggers, views, foreign key
          referential integrity, and sophisticated locking. We have some
          features they do not have, like user-defined types,
B
Bruce Momjian 已提交
322
          inheritance, rules, and multi-version concurrency control to
B
Bruce Momjian 已提交
323
          reduce lock contention.
B
Bruce Momjian 已提交
324 325 326 327
          
   Performance
          PostgreSQL runs in two modes. Normal fsync mode flushes every
          completed transaction to disk, guaranteeing that if the OS
B
Bruce Momjian 已提交
328
          crashes or loses power in the next few seconds, all your data
B
Bruce Momjian 已提交
329 330 331 332 333 334
          is safely stored on disk. In this mode, we are slower than most
          commercial databases, partly because few of them do such
          conservative flushing to disk in their default modes. In
          no-fsync mode, we are usually faster than commercial databases,
          though in this mode, an OS crash could cause data corruption.
          We are working to provide an intermediate mode that suffers
B
Bruce Momjian 已提交
335
          less performance overhead than full fsync mode, and will allow
B
Bruce Momjian 已提交
336
          data integrity within 30 seconds of an OS crash.
B
Bruce Momjian 已提交
337
          In comparison to MySQL or leaner database systems, we are
B
Bruce Momjian 已提交
338
          slower on inserts/updates because we have transaction overhead.
B
Bruce Momjian 已提交
339
          Of course, MySQL does not have any of the features mentioned in
B
Bruce Momjian 已提交
340 341
          the Features section above. We are built for flexibility and
          features, though we continue to improve performance through
B
Bruce Momjian 已提交
342
          profiling and source code analysis. There is an interesting Web
B
Bruce Momjian 已提交
343 344
          page comparing PostgreSQL to MySQL at
          http://openacs.org/why-not-mysql.html
B
Bruce Momjian 已提交
345 346
          We handle each user connection by creating a Unix process.
          Backend processes share data buffers and locking information.
B
Bruce Momjian 已提交
347 348
          With multiple CPUs, multiple backends can easily run on
          different CPUs.
B
Bruce Momjian 已提交
349 350 351 352 353 354 355 356 357 358 359 360
          
   Reliability
          We realize that a DBMS must be reliable, or it is worthless. We
          strive to release well-tested, stable code that has a minimum
          of bugs. Each release has at least one month of beta testing,
          and our release history shows that we can provide stable, solid
          releases that are ready for production use. We believe we
          compare favorably to other database software in this area.
          
   Support
          Our mailing list provides a large group of developers and users
          to help resolve any problems encountered. While we can not
B
Bruce Momjian 已提交
361
          guarantee a fix, commercial DBMSs do not always supply a fix
B
Bruce Momjian 已提交
362 363
          either. Direct access to developers, the user community,
          manuals, and the source code often make PostgreSQL support
B
Bruce Momjian 已提交
364
          superior to other DBMSs. There is commercial per-incident
B
Bruce Momjian 已提交
365 366 367 368 369 370 371
          support available for those who need it. (See support FAQ
          item.)
          
   Price
          We are free for all use, both commercial and non-commercial.
          You can add our code to your product with no limitations,
          except those outlined in our BSD-style license stated above.
B
Bruce Momjian 已提交
372
          
B
Bruce Momjian 已提交
373
    1.15) How can I financially assist PostgreSQL?
B
Bruce Momjian 已提交
374
    
B
Bruce Momjian 已提交
375
   PostgreSQL has had a first-class infrastructure since we started six
B
Bruce Momjian 已提交
376 377 378 379 380 381 382 383 384 385
   years ago. This is all thanks to Marc Fournier, who has created and
   managed this infrastructure over the years.
   
   Quality infrastructure is very important to an open-source project. It
   prevents disruptions that can greatly delay forward movement of the
   project.
   
   Of course, this infrastructure is not cheap. There are a variety of
   monthly and one-time expenses that are required to keep it going. If
   you or your company has money it can donate to help fund this effort,
B
Bruce Momjian 已提交
386 387
   please go to http://www.pgsql.com/pg_goodies and make a donation.
   
B
Bruce Momjian 已提交
388 389 390 391
   Although the web page mentions PostgreSQL, Inc, the "contributions"
   item is solely to support the PostgreSQL project and does not fund any
   specific company. If you prefer, you can also send a check to the
   contact address.
B
Bruce Momjian 已提交
392
     _________________________________________________________________
B
Bruce Momjian 已提交
393
   
B
Bruce Momjian 已提交
394 395 396 397
                           User Client Questions
                                      
    2.1) Are there ODBC drivers for PostgreSQL?
    
B
Bruce Momjian 已提交
398
   There are two ODBC drivers available, PsqlODBC and OpenLink ODBC.
B
Bruce Momjian 已提交
399
   
B
Bruce Momjian 已提交
400
   PsqlODBC is included in the distribution. More information about it
B
Bruce Momjian 已提交
401
   can be gotten from ftp://ftp.PostgreSQL.org/pub/odbc/.
B
Bruce Momjian 已提交
402
   
B
Bruce Momjian 已提交
403 404 405 406 407 408
   OpenLink ODBC can be gotten from http://www.openlinksw.com. It works
   with their standard ODBC client software so you'll have PostgreSQL
   ODBC available on every client platform they support (Win, Mac, Unix,
   VMS).
   
   They will probably be selling this product to people who need
B
Bruce Momjian 已提交
409
   commercial-quality support, but a freeware version will always be
B
Bruce Momjian 已提交
410
   available. Please send questions to postgres95@openlink.co.uk.
B
Bruce Momjian 已提交
411
   
B
Bruce Momjian 已提交
412 413
   See also the ODBC chapter of the Programmer's Guide.
   
B
Bruce Momjian 已提交
414
    2.2) What tools are available for using PostgreSQL with Web pages?
B
Bruce Momjian 已提交
415
    
B
Bruce Momjian 已提交
416
   A nice introduction to Database-backed Web pages can be seen at:
B
Bruce Momjian 已提交
417
   http://www.webreview.com
B
Bruce Momjian 已提交
418
   
B
Bruce Momjian 已提交
419 420
   There is also one at http://www.phone.net/home/mwm/hotlist/.
   
B
Bruce Momjian 已提交
421
   For Web integration, PHP is an excellent interface. It is at
B
Bruce Momjian 已提交
422
   http://www.php.net.
B
Bruce Momjian 已提交
423
   
B
Bruce Momjian 已提交
424
   For complex cases, many use the Perl interface and CGI.pm.
B
Bruce Momjian 已提交
425
   
B
Bruce Momjian 已提交
426 427 428
    2.3) Does PostgreSQL have a graphical user interface? A report generator?
    An embedded query language interface?
    
B
Bruce Momjian 已提交
429
   We have a nice graphical user interface called pgaccess, which is
B
Bruce Momjian 已提交
430
   shipped as part of the distribution. pgaccess also has a report
B
Bruce Momjian 已提交
431
   generator. The Web page is http://www.flex.ro/pgaccess
B
Bruce Momjian 已提交
432
   
B
Bruce Momjian 已提交
433 434
   We also include ecpg, which is an embedded SQL query language
   interface for C.
B
Bruce Momjian 已提交
435
   
B
Bruce Momjian 已提交
436 437
    2.4) What languages are available to communicate with PostgreSQL?
    
B
Bruce Momjian 已提交
438
   We have:
B
Bruce Momjian 已提交
439 440 441 442 443 444 445 446 447
     * C (libpq)
     * C++ (libpq++)
     * Embedded C (ecpg)
     * Java (jdbc)
     * Perl (perl5)
     * ODBC (odbc)
     * Python (PyGreSQL)
     * TCL (libpgtcl)
     * C Easy API (libpgeasy)
B
Bruce Momjian 已提交
448
     * Embedded HTML (PHP from http://www.php.net)
B
Bruce Momjian 已提交
449 450
     _________________________________________________________________
   
B
Bruce Momjian 已提交
451 452
                          Administrative Questions
                                      
B
Bruce Momjian 已提交
453
    3.1) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
B
Bruce Momjian 已提交
454
    
B
Bruce Momjian 已提交
455
   Specify the --prefix option when running configure.
B
Bruce Momjian 已提交
456
   
B
Bruce Momjian 已提交
457
    3.2) When I start postmaster, I get a Bad System Call or core dumped
B
Bruce Momjian 已提交
458 459
    message. Why?
    
B
Bruce Momjian 已提交
460
   It could be a variety of problems, but first check to see that you
B
Bruce Momjian 已提交
461
   have System V extensions installed in your kernel. PostgreSQL requires
B
Bruce Momjian 已提交
462
   kernel support for shared memory and semaphores.
B
Bruce Momjian 已提交
463
   
B
Bruce Momjian 已提交
464
    3.3) When I try to start postmaster, I get IpcMemoryCreate errors. Why?
B
Bruce Momjian 已提交
465
    
B
Bruce Momjian 已提交
466 467 468
   You either do not have shared memory configured properly in your
   kernel or you need to enlarge the shared memory available in the
   kernel. The exact amount you need depends on your architecture and how
B
Bruce Momjian 已提交
469 470 471 472
   many buffers and backend processes you configure for postmaster. For
   most systems, with default numbers of buffers and processes, you need
   a minimum of ~1 MB. See the PostgreSQL Administrator's Guide for more
   detailed information about shared memory and semaphores.
B
Bruce Momjian 已提交
473
   
B
Bruce Momjian 已提交
474
    3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors. Why?
B
Bruce Momjian 已提交
475 476 477 478
    
   If the error message is IpcSemaphoreCreate: semget failed (No space
   left on device) then your kernel is not configured with enough
   semaphores. Postgres needs one semaphore per potential backend
B
Bruce Momjian 已提交
479 480 481 482 483 484 485
   process. A temporary solution is to start postmaster with a smaller
   limit on the number of backend processes. Use -N with a parameter less
   than the default of 32. A more permanent solution is to increase your
   kernel's SEMMNS and SEMMNI parameters.
   
   Inoperative semaphores can also cause crashes during heavy database
   access.
B
Bruce Momjian 已提交
486 487
   
   If the error message is something else, you might not have semaphore
B
Bruce Momjian 已提交
488 489 490
   support configured in your kernel at all. See the PostgreSQL
   Administrator's Guide for more detailed information about shared
   memory and semaphores.
B
Bruce Momjian 已提交
491
   
B
Bruce Momjian 已提交
492
    3.5) How do I control connections from other hosts?
B
Bruce Momjian 已提交
493
    
B
Bruce Momjian 已提交
494
   By default, PostgreSQL only allows connections from the local machine
B
Bruce Momjian 已提交
495
   using Unix domain sockets. Other machines will not be able to connect
B
Bruce Momjian 已提交
496
   unless you add the -i flag to postmaster, and enable host-based
B
Bruce Momjian 已提交
497
   authentication by modifying the file $PGDATA/pg_hba.conf accordingly.
B
Bruce Momjian 已提交
498
   This will allow TCP/IP connections.
B
Bruce Momjian 已提交
499
   
B
Bruce Momjian 已提交
500
    3.6) How do I tune the database engine for better performance?
B
Bruce Momjian 已提交
501
    
B
Bruce Momjian 已提交
502
   Certainly, indexes can speed up queries. The EXPLAIN command allows
B
Bruce Momjian 已提交
503
   you to see how PostgreSQL is interpreting your query, and which
B
Bruce Momjian 已提交
504
   indexes are being used.
B
Bruce Momjian 已提交
505
   
B
Bruce Momjian 已提交
506 507 508 509 510 511 512
   If you are doing many INSERTs, consider doing them in a large batch
   using the COPY command. This is much faster than individual INSERTS.
   Second, statements not in a BEGIN WORK/COMMIT transaction block are
   considered to be in their own transaction. Consider performing several
   statements in a single transaction block. This reduces the transaction
   overhead. Also, consider dropping and recreating indexes when making
   large data changes.
B
Bruce Momjian 已提交
513
   
B
Bruce Momjian 已提交
514
   There are several tuning options. You can disable fsync() by starting
B
Bruce Momjian 已提交
515
   postmaster with a -o -F option. This will prevent fsync()s from
B
Bruce Momjian 已提交
516
   flushing to disk after every transaction.
B
Bruce Momjian 已提交
517 518
   
   You can also use the postmaster -B option to increase the number of
B
Bruce Momjian 已提交
519
   shared memory buffers used by the backend processes. If you make this
B
Bruce Momjian 已提交
520
   parameter too high, the postmaster may not start because you have
B
Bruce Momjian 已提交
521 522
   exceeded your kernel's limit on shared memory space. Each buffer is 8K
   and the default is 64 buffers.
B
Bruce Momjian 已提交
523
   
B
Bruce Momjian 已提交
524
   You can also use the backend -S option to increase the maximum amount
B
Bruce Momjian 已提交
525
   of memory used by the backend process for temporary sorts. The -S
B
Bruce Momjian 已提交
526
   value is measured in kilobytes, and the default is 512 (i.e. 512K).
B
Bruce Momjian 已提交
527
   
B
Bruce Momjian 已提交
528
   You can also use the CLUSTER command to group data in tables to match
B
Bruce Momjian 已提交
529
   an index. See the CLUSTER manual page for more details.
B
Bruce Momjian 已提交
530
   
B
Bruce Momjian 已提交
531
    3.7) What debugging features are available?
B
Bruce Momjian 已提交
532
    
B
Bruce Momjian 已提交
533 534 535
   PostgreSQL has several features that report status information that
   can be valuable for debugging purposes.
   
B
Bruce Momjian 已提交
536
   First, by running configure with the --enable-cassert option, many
B
Bruce Momjian 已提交
537
   assert()s monitor the progress of the backend and halt the program
B
Bruce Momjian 已提交
538 539 540
   when something unexpected occurs.
   
   Both postmaster and postgres have several debug options available.
B
Bruce Momjian 已提交
541 542
   First, whenever you start postmaster, make sure you send the standard
   output and error to a log file, like:
B
Bruce Momjian 已提交
543 544
    cd /usr/local/pgsql
    ./bin/postmaster >server.log 2>&1 &
B
Bruce Momjian 已提交
545 546

   This will put a server.log file in the top-level PostgreSQL directory.
B
Bruce Momjian 已提交
547
   This file contains useful information about problems or errors
B
Bruce Momjian 已提交
548 549
   encountered by the server. Postmaster has a -d option that allows even
   more detailed information to be reported. The -d option takes a number
B
Bruce Momjian 已提交
550
   that specifies the debug level. Be warned that high debug level values
B
Bruce Momjian 已提交
551
   generate large log files.
B
Bruce Momjian 已提交
552
   
B
Bruce Momjian 已提交
553
   If postmaster is not running, you can actually run the postgres
B
Bruce Momjian 已提交
554 555 556 557
   backend from the command line, and type your SQL statement directly.
   This is recommended only for debugging purposes. Note that a newline
   terminates the query, not a semicolon. If you have compiled with
   debugging symbols, you can use a debugger to see what is happening.
B
Bruce Momjian 已提交
558 559 560
   Because the backend was not started from postmaster, it is not running
   in an identical environment and locking/backend interaction problems
   may not be duplicated.
B
Bruce Momjian 已提交
561
   
B
Bruce Momjian 已提交
562 563 564
   If postmaster is running, start psql in one window, then find the PID
   of the postgres process used by psql. Use a debugger to attach to the
   postgres PID. You can set breakpoints in the debugger and issue
B
Bruce Momjian 已提交
565 566
   queries from psql. If you are debugging postgres startup, you can set
   PGOPTIONS="-W n", then start psql. This will cause startup to delay
B
Bruce Momjian 已提交
567 568
   for n seconds so you can attach to the process with the debugger, set
   any breakpoints, and continue through the startup sequence.
B
Bruce Momjian 已提交
569
   
B
Bruce Momjian 已提交
570 571
   The postgres program has -s, -A, and -t options that can be very
   useful for debugging and performance measurements.
B
Bruce Momjian 已提交
572 573 574 575
   
   You can also compile with profiling to see what functions are taking
   execution time. The backend profile files will be deposited in the
   pgsql/data/base/dbname directory. The client profile file will be put
576 577
   in the client's current directory. Linux requires a compile with
   -DLINUX_PROFILE for proper profiling.
B
Bruce Momjian 已提交
578
   
B
Bruce Momjian 已提交
579
    3.8) Why do I get "Sorry, too many clients" when trying to connect?
B
Bruce Momjian 已提交
580
    
B
Bruce Momjian 已提交
581 582
   You need to increase postmaster's limit on how many concurrent backend
   processes it can start.
B
Bruce Momjian 已提交
583
   
B
Bruce Momjian 已提交
584
   The default limit is 32 processes. You can increase it by restarting
B
Bruce Momjian 已提交
585
   postmaster with a suitable -N value or modifying postgresql.conf.
B
Bruce Momjian 已提交
586 587 588 589 590 591
   
   Note that if you make -N larger than 32, you must also increase -B
   beyond its default of 64; -B must be at least twice -N, and probably
   should be more than that for best performance. For large numbers of
   backend processes, you are also likely to find that you need to
   increase various Unix kernel configuration parameters. Things to check
B
Bruce Momjian 已提交
592 593 594
   include the maximum size of shared memory blocks, SHMMAX; the maximum
   number of semaphores, SEMMNS and SEMMNI; the maximum number of
   processes, NPROC; the maximum number of processes per user, MAXUPRC;
B
Bruce Momjian 已提交
595
   and the maximum number of open files, NFILE and NINODE. The reason
B
Bruce Momjian 已提交
596
   that PostgreSQL has a limit on the number of allowed backend processes
B
Bruce Momjian 已提交
597
   is so your system won't run out of resources.
B
Bruce Momjian 已提交
598
   
B
Bruce Momjian 已提交
599 600 601
   In PostgreSQL versions prior to 6.5, the maximum number of backends
   was 64, and changing it required a rebuild after altering the
   MaxBackendId constant in include/storage/sinvaladt.h.
B
Bruce Momjian 已提交
602
   
B
Bruce Momjian 已提交
603
    3.9) What are the pg_sorttempNNN.NN files in my database directory?
B
Bruce Momjian 已提交
604 605
    
   They are temporary files generated by the query executor. For example,
B
Bruce Momjian 已提交
606
   if a sort needs to be done to satisfy an ORDER BY, and the sort
B
Bruce Momjian 已提交
607 608
   requires more space than the backend's -S parameter allows, then
   temporary files are created to hold the extra data.
B
Bruce Momjian 已提交
609
   
B
Bruce Momjian 已提交
610 611 612
   The temporary files should be deleted automatically, but might not if
   a backend crashes during a sort. If you have no backends running at
   the time, it is safe to delete the pg_tempNNN.NN files.
B
Bruce Momjian 已提交
613
     _________________________________________________________________
B
Bruce Momjian 已提交
614
   
B
Bruce Momjian 已提交
615 616
                           Operational Questions
                                      
B
Bruce Momjian 已提交
617
    4.1) What is the difference between binary cursors and normal cursors?
B
Bruce Momjian 已提交
618
    
B
Bruce Momjian 已提交
619
   See the DECLARE manual page for a description.
B
Bruce Momjian 已提交
620
   
B
Bruce Momjian 已提交
621
    4.2) How do I SELECT only the first few rows of a query?
B
Bruce Momjian 已提交
622
    
B
Bruce Momjian 已提交
623
   See the FETCH manual page, or use SELECT ... LIMIT....
B
Bruce Momjian 已提交
624
   
B
Bruce Momjian 已提交
625 626 627 628 629
   The entire query may have to be evaluated, even if you only want the
   first few rows. Consider a query that has an ORDER BY. If there is an
   index that matches the ORDER BY, PostgreSQL may be able to evaluate
   only the first few records requested, or the entire query may have to
   be evaluated until the desired rows have been generated.
B
Bruce Momjian 已提交
630
   
B
Bruce Momjian 已提交
631
    4.3) How do I get a list of tables or other things I can see in psql?
B
Bruce Momjian 已提交
632
    
B
Bruce Momjian 已提交
633
   You can read the source code for psql in file
B
Bruce Momjian 已提交
634 635 636
   pgsql/src/bin/psql/describe.c. It contains SQL commands that generate
   the output for psql's backslash commands. You can also start psql with
   the -E option so it will print out the queries it uses to execute the
B
Bruce Momjian 已提交
637
   commands you give.
B
Bruce Momjian 已提交
638
   
B
Bruce Momjian 已提交
639
    4.4) How do you remove a column from a table?
B
Bruce Momjian 已提交
640
    
B
Bruce Momjian 已提交
641
   We do not support ALTER TABLE DROP COLUMN, but do this:
642 643
    BEGIN;
    LOCK TABLE old_table;
B
Bruce Momjian 已提交
644 645 646 647 648
    SELECT ...  -- select all columns but the one you want to remove
    INTO TABLE new_table
    FROM old_table;
    DROP TABLE old_table;
    ALTER TABLE new_table RENAME TO old_table;
649
    COMMIT;
B
Bruce Momjian 已提交
650

B
Bruce Momjian 已提交
651
    4.5) What is the maximum size for a row, a table, and a database?
B
Bruce Momjian 已提交
652
    
B
Bruce Momjian 已提交
653
   These are the limits:
B
Bruce Momjian 已提交
654
    Maximum size for a database?             unlimited (500 GB databases exist)
B
Bruce Momjian 已提交
655 656
    Maximum size for a table?                16 TB
    Maximum size for a row?                  unlimited in 7.1 and later
B
Bruce Momjian 已提交
657
    Maximum size for a field?                1 GB in 7.1 and later
B
Bruce Momjian 已提交
658 659 660
    Maximum number of rows in a table?       unlimited
    Maximum number of columns in a table?    250-1600 depending on column types
    Maximum number of indexes on a table?    unlimited
B
Bruce Momjian 已提交
661 662

   Of course, these are not actually unlimited, but limited to available
B
Bruce Momjian 已提交
663 664
   disk space and memory/swap space. Performance may suffer when these
   values get unusually large.
B
Bruce Momjian 已提交
665
   
B
Bruce Momjian 已提交
666 667
   The maximum table size of 16 TB does not require large file support
   from the operating system. Large tables are stored as multiple 1 GB
B
Bruce Momjian 已提交
668
   files so file system size limits are not important.
B
Bruce Momjian 已提交
669 670
   
   The maximum table size and maximum number of columns can be increased
B
Bruce Momjian 已提交
671
   if the default block size is increased to 32k.
B
Bruce Momjian 已提交
672
   
B
Bruce Momjian 已提交
673
    4.6) How much database disk space is required to store data from a typical
B
Bruce Momjian 已提交
674
    text file?
B
Bruce Momjian 已提交
675
    
B
Bruce Momjian 已提交
676 677
   A PostgreSQL database may require up to five times the disk space to
   store data from a text file.
B
Bruce Momjian 已提交
678
   
B
Bruce Momjian 已提交
679 680
   As an example, consider a file of 100,000 lines with an integer and
   text description on each line. Suppose the text string avergages
B
Bruce Momjian 已提交
681 682 683
   twenty bytes in length. The flat file would be 2.8 MB. The size of the
   PostgreSQL database file containing this data can be estimated as 6.4
   MB:
B
Bruce Momjian 已提交
684
    36 bytes: each row header (approximate)
B
Bruce Momjian 已提交
685
    24 bytes: one int field and one text filed
B
Bruce Momjian 已提交
686 687
   + 4 bytes: pointer on page to tuple
   ----------------------------------------
B
Bruce Momjian 已提交
688
    64 bytes per row
B
Bruce Momjian 已提交
689 690 691 692

   The data page size in PostgreSQL is 8192 bytes (8 KB), so:

   8192 bytes per page
B
Bruce Momjian 已提交
693 694
   -------------------   =  128 rows per database page (rounded down)
     64 bytes per row
B
Bruce Momjian 已提交
695

B
Bruce Momjian 已提交
696
   100000 data rows
B
Bruce Momjian 已提交
697 698
   --------------------  =  782 database pages (rounded up)
      128 rows per page
B
Bruce Momjian 已提交
699

B
Bruce Momjian 已提交
700
782 database pages * 8192 bytes per page  =  6,406,144 bytes (6.4 MB)
B
Bruce Momjian 已提交
701

B
Bruce Momjian 已提交
702
   Indexes do not require as much overhead, but do contain the data that
B
Bruce Momjian 已提交
703 704
   is being indexed, so they can be large also.
   
705 706
    4.7) How do I find out what tables, indexes, databases, and users are
    defined?
B
Bruce Momjian 已提交
707 708
    
   psql has a variety of backslash commands to show such information. Use
709 710
   \? to see them. There are also system tables beginning with pg_ that
   describe these too. Also, psql -l will list all databases.
B
Bruce Momjian 已提交
711 712
   
   Also try the file pgsql/src/tutorial/syscat.source. It illustrates
B
Bruce Momjian 已提交
713
   many of the SELECTs needed to get information from the database system
B
Bruce Momjian 已提交
714
   tables.
B
Bruce Momjian 已提交
715
   
B
Bruce Momjian 已提交
716
    4.8) My queries are slow or don't make use of the indexes. Why?
B
Bruce Momjian 已提交
717
    
B
Bruce Momjian 已提交
718
   Indexes are not automatically used by every query. Indexes are only
B
Bruce Momjian 已提交
719
   used if the table is larger than a minimum size, and the query selects
B
Bruce Momjian 已提交
720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736
   only a small percentage of the rows in the table. This is because the
   random disk access caused by an index scan is sometimes slower than a
   straight read through the table, or sequential scan.
   
   To determine if an index should be used, PostgreSQL must have
   statistics about the table. These statistics are collected using
   VACUUM ANALYZE, or simply ANALYZE. Using statistics, the optimizer
   knows how many rows are in the table, and can better determine if
   indexes should be used. Statistics are also valuable in determining
   optimal join order and join methods. Statistics collection should be
   performed periodically as the contents of the table change.
   
   Indexes are normally not used for ORDER BY or to perform joins. A
   sequential scan followed by an explicit sort is usually faster than an
   index scan of a large table.
   However, LIMIT combined with ORDER BY often will use an index because
   only a small portion of the table is returned.
B
Bruce Momjian 已提交
737
   
B
Bruce Momjian 已提交
738
   When using wild-card operators such as LIKE or ~, indexes can only be
B
Bruce Momjian 已提交
739
   used if the beginning of the search is anchored to the start of the
B
Bruce Momjian 已提交
740 741
   string. Therefore, to use indexes, LIKE patterns must not start with
   %, and ~(regular expression) patterns must start with ^.
B
Bruce Momjian 已提交
742
   
B
Bruce Momjian 已提交
743
    4.9) How do I see how the query optimizer is evaluating my query?
B
Bruce Momjian 已提交
744
    
B
Bruce Momjian 已提交
745
   See the EXPLAIN manual page.
B
Bruce Momjian 已提交
746
   
B
Bruce Momjian 已提交
747
    4.10) What is an R-tree index?
B
Bruce Momjian 已提交
748
    
B
Bruce Momjian 已提交
749
   An R-tree index is used for indexing spatial data. A hash index can't
B
Bruce Momjian 已提交
750
   handle range searches. A B-tree index only handles range searches in a
B
Bruce Momjian 已提交
751
   single dimension. R-trees can handle multi-dimensional data. For
B
Bruce Momjian 已提交
752
   example, if an R-tree index can be built on an attribute of type
B
Bruce Momjian 已提交
753 754
   point, the system can more efficiently answer queries such as "select
   all points within a bounding rectangle."
B
Bruce Momjian 已提交
755
   
B
Bruce Momjian 已提交
756
   The canonical paper that describes the original R-tree design is:
B
Bruce Momjian 已提交
757
   
B
Bruce Momjian 已提交
758
   Guttman, A. "R-trees: A Dynamic Index Structure for Spatial
B
Bruce Momjian 已提交
759 760
   Searching." Proceedings of the 1984 ACM SIGMOD Int'l Conf on Mgmt of
   Data, 45-57.
B
Bruce Momjian 已提交
761 762
   
   You can also find this paper in Stonebraker's "Readings in Database
B
Bruce Momjian 已提交
763
   Systems".
B
Bruce Momjian 已提交
764
   
B
Bruce Momjian 已提交
765
   Built-in R-trees can handle polygons and boxes. In theory, R-trees can
B
Bruce Momjian 已提交
766
   be extended to handle higher number of dimensions. In practice,
B
Bruce Momjian 已提交
767
   extending R-trees requires a bit of work and we don't currently have
B
Bruce Momjian 已提交
768 769
   any documentation on how to do it.
   
B
Bruce Momjian 已提交
770
    4.11) What is the Genetic Query Optimizer?
B
Bruce Momjian 已提交
771
    
B
Bruce Momjian 已提交
772 773
   The GEQO module speeds query optimization when joining many tables by
   means of a Genetic Algorithm (GA). It allows the handling of large
B
Bruce Momjian 已提交
774
   join queries through nonexhaustive search.
B
Bruce Momjian 已提交
775
   
B
Bruce Momjian 已提交
776
    4.12) How do I perform regular expression searches and case-insensitive
B
Bruce Momjian 已提交
777 778
    regular expression searches? How do I use an index for case-insensitive
    searches?
B
Bruce Momjian 已提交
779
    
B
Bruce Momjian 已提交
780
   The ~ operator does regular expression matching, and ~* does
B
Bruce Momjian 已提交
781
   case-insensitive regular expression matching. The case-insensitive
B
Bruce Momjian 已提交
782
   variant of LIKE is called ILIKE in PostgreSQL 7.1 and later.
B
Bruce Momjian 已提交
783
   
B
Bruce Momjian 已提交
784 785 786 787 788 789 790 791 792
   Case-insensitive equality comparisons are normally expressed as:
    SELECT *
    FROM tab
    WHERE lower(col) = 'abc'

   This will not use an standard index. However, if you create a
   functional index, it will be used:
    CREATE INDEX tabindex on tab (lower(col));

B
Bruce Momjian 已提交
793
    4.13) In a query, how do I detect if a field is NULL?
B
Bruce Momjian 已提交
794
    
B
Bruce Momjian 已提交
795
   You test the column with IS NULL and IS NOT NULL.
B
Bruce Momjian 已提交
796
   
B
Bruce Momjian 已提交
797
    4.14) What is the difference between the various character types?
B
Bruce Momjian 已提交
798
    
B
Bruce Momjian 已提交
799
Type            Internal Name   Notes
M
Marc G. Fournier 已提交
800
--------------------------------------------------
B
Bruce Momjian 已提交
801
"char"          char            1 character
B
Bruce Momjian 已提交
802 803
CHAR(#)         bpchar          blank padded to the specified fixed length
VARCHAR(#)      varchar         size specifies maximum length, no padding
804
TEXT            text            no specific upper limit on length
B
Bruce Momjian 已提交
805
BYTEA           bytea           variable-length byte array (null-byte safe)
B
Bruce Momjian 已提交
806

B
Bruce Momjian 已提交
807 808
   You will see the internal name when examining system catalogs and in
   some error messages.
B
Bruce Momjian 已提交
809
   
B
Bruce Momjian 已提交
810
   The last four types above are "varlena" types (i.e., the first four
B
Bruce Momjian 已提交
811 812
   bytes on disk are the length, followed by the data). Thus the actual
   space used is slightly greater than the declared size. However, these
813 814
   data types are also subject to compression or being stored out-of-line
   by TOAST, so the space on disk might also be less than expected.
B
Bruce Momjian 已提交
815
   
B
Bruce Momjian 已提交
816
   CHAR() is best when storing strings that are usually the same length.
B
Bruce Momjian 已提交
817 818 819 820
   VARCHAR() is best when storing variable-length strings but it limits
   how long a string can be. TEXT is for strings of unlimited length,
   maximum 1 gigabyte. BYTEA is for storing binary data, particularly
   values that include NULL bytes.
B
Bruce Momjian 已提交
821
   
B
Bruce Momjian 已提交
822
    4.15.1) How do I create a serial/auto-incrementing field?
B
Bruce Momjian 已提交
823
    
B
Bruce Momjian 已提交
824
   PostgreSQL supports a SERIAL data type. It auto-creates a sequence and
B
Bruce Momjian 已提交
825
   index on the column. For example, this:
B
Bruce Momjian 已提交
826 827 828 829
    CREATE TABLE person (
        id   SERIAL,
        name TEXT
    );
B
Bruce Momjian 已提交
830

B
Bruce Momjian 已提交
831
   is automatically translated into this:
B
Bruce Momjian 已提交
832 833 834 835 836 837
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person (
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT
    );
    CREATE UNIQUE INDEX person_id_key ON person ( id );
B
Bruce Momjian 已提交
838 839

   See the create_sequence manual page for more information about
B
Bruce Momjian 已提交
840
   sequences. You can also use each row's OID field as a unique value.
B
Bruce Momjian 已提交
841
   However, if you need to dump and reload the database, you need to use
B
Bruce Momjian 已提交
842
   pg_dump's -o option or COPY WITH OIDS option to preserve the OIDs.
B
Bruce Momjian 已提交
843
   
B
Bruce Momjian 已提交
844
    4.15.2) How do I get the value of a SERIAL insert?
B
Bruce Momjian 已提交
845
    
B
Bruce Momjian 已提交
846
   One approach is to retrieve the next SERIAL value from the sequence
B
Bruce Momjian 已提交
847
   object with the nextval() function before inserting and then insert it
B
Bruce Momjian 已提交
848
   explicitly. Using the example table in 4.15.1, that might look like
B
Bruce Momjian 已提交
849
   this in Perl:
B
Bruce Momjian 已提交
850 851
    new_id = output of "SELECT nextval('person_id_seq')"
    INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');
B
Bruce Momjian 已提交
852

B
Bruce Momjian 已提交
853 854 855 856 857
   You would then also have the new value stored in new_id for use in
   other queries (e.g., as a foreign key to the person table). Note that
   the name of the automatically created SEQUENCE object will be named
   <table>_<serialcolumn>_seq, where table and serialcolumn are the names
   of your table and your SERIAL column, respectively.
B
Bruce Momjian 已提交
858
   
B
Bruce Momjian 已提交
859 860
   Alternatively, you could retrieve the assigned SERIAL value with the
   currval() function after it was inserted by default, e.g.,
B
Bruce Momjian 已提交
861
    INSERT INTO person (name) VALUES ('Blaise Pascal');
B
Bruce Momjian 已提交
862
    new_id = output of "SELECT currval('person_id_seq')";
B
Bruce Momjian 已提交
863

B
Bruce Momjian 已提交
864
   Finally, you could use the OID returned from the INSERT statement to
B
Bruce Momjian 已提交
865
   look up the default value, though this is probably the least portable
B
Bruce Momjian 已提交
866
   approach. In Perl, using DBI with Edmund Mergl's DBD::Pg module, the
B
Bruce Momjian 已提交
867 868 869
   oid value is made available via $sth->{pg_oid_status} after
   $sth->execute().
   
B
Bruce Momjian 已提交
870
    4.15.3) Don't currval() and nextval() lead to a race condition with other
B
Bruce Momjian 已提交
871
    users?
B
Bruce Momjian 已提交
872
    
B
Bruce Momjian 已提交
873 874
   No. Currval() returns the current value assigned by your backend, not
   by all users.
B
Bruce Momjian 已提交
875
   
B
Bruce Momjian 已提交
876
    4.15.4) Why aren't my sequence numbers reused on transaction abort? Why are
B
Bruce Momjian 已提交
877
    there gaps in the numbering of my sequence/SERIAL column?
B
Bruce Momjian 已提交
878 879
    
   To improve concurrency, sequence values are given out to running
B
Bruce Momjian 已提交
880
   transactions as needed and are not locked until the transaction
B
Bruce Momjian 已提交
881 882
   completes. This causes gaps in numbering from aborted transactions.
   
B
Bruce Momjian 已提交
883
    4.16) What is an OID? What is a TID?
B
Bruce Momjian 已提交
884
    
B
Bruce Momjian 已提交
885
   OIDs are PostgreSQL's answer to unique row ids. Every row that is
B
Bruce Momjian 已提交
886
   created in PostgreSQL gets a unique OID. All OIDs generated during
B
Bruce Momjian 已提交
887
   initdb are less than 16384 (from backend/access/transam.h). All
B
Bruce Momjian 已提交
888 889
   user-created OIDs are equal to or greater than this. By default, all
   these OIDs are unique not only within a table or database, but unique
B
Bruce Momjian 已提交
890
   within the entire PostgreSQL installation.
B
Bruce Momjian 已提交
891
   
B
Bruce Momjian 已提交
892 893 894 895
   PostgreSQL uses OIDs in its internal system tables to link rows
   between tables. These OIDs can be used to identify specific user rows
   and used in joins. It is recommended you use column type OID to store
   OID values. You can create an index on the OID field for faster
B
Bruce Momjian 已提交
896
   access.
B
Bruce Momjian 已提交
897
   
B
Bruce Momjian 已提交
898
   OIDs are assigned to all new rows from a central area that is used by
B
Bruce Momjian 已提交
899
   all databases. If you want to change the OID to something else, or if
B
Bruce Momjian 已提交
900 901
   you want to make a copy of the table, with the original OIDs, there is
   no reason you can't do it:
B
Bruce Momjian 已提交
902
        CREATE TABLE new_table(old_oid oid, mycol int);
B
Bruce Momjian 已提交
903
        SELECT old_oid, mycol INTO new FROM old;
B
Bruce Momjian 已提交
904 905 906
        COPY new TO '/tmp/pgtable';
        DELETE FROM new;
        COPY new WITH OIDS FROM '/tmp/pgtable';
B
Bruce Momjian 已提交
907

B
Bruce Momjian 已提交
908
   OIDs are stored as 4-byte integers, and will overflow at 4 billion. No
B
Bruce Momjian 已提交
909
   one has reported this ever happening, and we plan to have the limit
B
Bruce Momjian 已提交
910 911
   removed before anyone does.
   
B
Bruce Momjian 已提交
912
   TIDs are used to identify specific physical rows with block and offset
B
Bruce Momjian 已提交
913
   values. TIDs change after rows are modified or reloaded. They are used
B
Bruce Momjian 已提交
914
   by index entries to point to physical rows.
B
Bruce Momjian 已提交
915
   
B
Bruce Momjian 已提交
916
    4.17) What is the meaning of some of the terms used in PostgreSQL?
B
Bruce Momjian 已提交
917
    
B
Bruce Momjian 已提交
918 919
   Some of the source code and older documentation use terms that have
   more common usage. Here are some:
B
Bruce Momjian 已提交
920
     * table, relation, class
B
Bruce Momjian 已提交
921
     * row, record, tuple
B
Bruce Momjian 已提交
922
     * column, field, attribute
B
Bruce Momjian 已提交
923 924 925
     * retrieve, select
     * replace, update
     * append, insert
B
Bruce Momjian 已提交
926
     * OID, serial value
B
Bruce Momjian 已提交
927 928 929
     * portal, cursor
     * range variable, table name, table alias
       
B
Bruce Momjian 已提交
930 931 932
   A list of general database terms can be found at:
   http://www.comptechnews.com/~reaster/dbdesign.html
   
B
Bruce Momjian 已提交
933
    4.18) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
B
Bruce Momjian 已提交
934
    
B
Bruce Momjian 已提交
935 936 937
   If you are running a version older than 7.1, an upgrade may fix the
   problem. Also it is possible you have run out of virtual memory on
   your system, or your kernel has a low limit for certain resources. Try
B
Bruce Momjian 已提交
938
   this before starting postmaster:
B
Bruce Momjian 已提交
939 940 941 942 943 944 945 946 947 948
    ulimit -d 262144
    limit datasize 256m

   Depending on your shell, only one of these may succeed, but it will
   set your process data segment limit much higher and perhaps allow the
   query to complete. This command applies to the current process, and
   all subprocesses created after the command is run. If you are having a
   problem with the SQL client because the backend is returning too much
   data, try it before starting the client.
   
B
Bruce Momjian 已提交
949
    4.19) How do I tell what PostgreSQL version I am running?
B
Bruce Momjian 已提交
950
    
B
Bruce Momjian 已提交
951
   From psql, type select version();
B
Bruce Momjian 已提交
952
   
B
Bruce Momjian 已提交
953 954
    4.20) Why does my large-object operations get "invalid large obj
    descriptor"?
B
Bruce Momjian 已提交
955 956 957 958
    
   You need to put BEGIN WORK and COMMIT around any use of a large object
   handle, that is, surrounding lo_open ... lo_close.
   
B
Bruce Momjian 已提交
959 960 961 962 963
   Currently PostgreSQL enforces the rule by closing large object handles
   at transaction commit. So the first attempt to do anything with the
   handle will draw invalid large obj descriptor. So code that used to
   work (at least most of the time) will now generate that error message
   if you fail to use a transaction.
B
Bruce Momjian 已提交
964 965 966 967
   
   If you are using a client interface like ODBC you may need to set
   auto-commit off.
   
B
Bruce Momjian 已提交
968
    4.21) How do I create a column that will default to the current time?
B
Bruce Momjian 已提交
969
    
B
Bruce Momjian 已提交
970 971
   Use CURRENT_TIMESTAMP:
CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
B
Bruce Momjian 已提交
972

B
Bruce Momjian 已提交
973
    4.22) Why are my subqueries using IN so slow?
B
Bruce Momjian 已提交
974
    
B
Bruce Momjian 已提交
975 976
   Currently, we join subqueries to outer queries by sequentially
   scanning the result of the subquery for each row of the outer query. A
B
Bruce Momjian 已提交
977
   workaround is to replace IN with EXISTS:
B
Bruce Momjian 已提交
978 979 980
SELECT *
    FROM tab
    WHERE col1 IN (SELECT col2 FROM TAB2)
B
Bruce Momjian 已提交
981 982

   to:
B
Bruce Momjian 已提交
983 984 985
SELECT *
    FROM tab
    WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
B
Bruce Momjian 已提交
986

B
Bruce Momjian 已提交
987
   We hope to fix this limitation in a future release.
B
Bruce Momjian 已提交
988
   
B
Bruce Momjian 已提交
989
    4.23) How do I perform an outer join?
B
Bruce Momjian 已提交
990
    
B
Bruce Momjian 已提交
991 992
   PostgreSQL 7.1 and later supports outer joins using the SQL standard
   syntax. Here are two examples:
B
Bruce Momjian 已提交
993 994 995 996
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

   or
B
Bruce Momjian 已提交
997 998 999
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);

B
Bruce Momjian 已提交
1000 1001 1002 1003 1004 1005
   These identical queries join t1.col to t2.col, and also return any
   unjoined rows in t1 (those with no match in t2). A RIGHT join would
   add unjoined rows of t2. A FULL join would return the matched rows
   plus all unjoined rows from t1 and t2. The word OUTER is optional and
   is assumed in LEFT, RIGHT, and FULL joins. Ordinary joins are called
   INNER joins.
B
Bruce Momjian 已提交
1006
   
B
Bruce Momjian 已提交
1007 1008 1009
   In previous releases, outer joins can be simulated using UNION and NOT
   IN. For example, when joining tab1 and tab2, the following query does
   an outer join of the two tables:
B
Bruce Momjian 已提交
1010 1011 1012 1013 1014 1015 1016
    SELECT tab1.col1, tab2.col2
    FROM tab1, tab2
    WHERE tab1.col1 = tab2.col1
    UNION ALL
    SELECT tab1.col1, NULL
    FROM tab1
    WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
B
Bruce Momjian 已提交
1017
    ORDER BY col1
B
Bruce Momjian 已提交
1018

B
Bruce Momjian 已提交
1019
    4.24) How do I perform queries using multiple databases?
B
Bruce Momjian 已提交
1020 1021 1022 1023 1024 1025 1026
    
   There is no way to query any database except the current one. Because
   PostgreSQL loads database-specific system catalogs, it is uncertain
   how a cross-database query should even behave.
   
   Of course, a client can make simultaneous connections to different
   databases and merge the information that way.
B
Bruce Momjian 已提交
1027
   
B
Bruce Momjian 已提交
1028 1029 1030 1031
    4.25) How do I return multiple rows or columns from a function?
    
   You can return result sets from PL/pgSQL functions using refcursors.
   See
B
Bruce Momjian 已提交
1032
   http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,
B
Bruce Momjian 已提交
1033 1034
   section 23.7.3.3.
   
1035 1036 1037 1038 1039 1040 1041 1042 1043 1044
    4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
    functions?
    
   PL/PgSQL caches function contents, and an unfortunate side effect is
   that if a PL/PgSQL function accesses a temporary table, and that table
   is later dropped and recreated, and the function called again, the
   function will fail because the cached function contents still point to
   the old temporary table. The solution is to use EXECUTE for temporary
   table access in PL/PgSQL. This will cause the query to be reparsed
   every time.
B
Bruce Momjian 已提交
1045
     _________________________________________________________________
1046
   
B
Bruce Momjian 已提交
1047 1048 1049 1050 1051
                            Extending PostgreSQL
                                      
    5.1) I wrote a user-defined function. When I run it in psql, why does it
    dump core?
    
B
Bruce Momjian 已提交
1052
   The problem could be a number of things. Try testing your user-defined
B
Bruce Momjian 已提交
1053
   function in a stand-alone test program first.
B
Bruce Momjian 已提交
1054
   
B
Bruce Momjian 已提交
1055
    5.2) How can I contribute some nifty new types and functions to PostgreSQL?
B
Bruce Momjian 已提交
1056 1057 1058
    
   Send your extensions to the pgsql-hackers mailing list, and they will
   eventually end up in the contrib/ subdirectory.
B
Bruce Momjian 已提交
1059
   
B
Bruce Momjian 已提交
1060
    5.3) How do I write a C function to return a tuple?
B
Bruce Momjian 已提交
1061 1062 1063
    
   This requires wizardry so extreme that the authors have never tried
   it, though in principle it can be done.
B
Bruce Momjian 已提交
1064
   
B
Bruce Momjian 已提交
1065
    5.4) I have changed a source file. Why does the recompile not see the
B
Bruce Momjian 已提交
1066 1067 1068
    change?
    
   The Makefiles do not have the proper dependencies for include files.
B
Bruce Momjian 已提交
1069 1070 1071
   You have to do a make clean and then another make. If you are using
   GCC you can use the --enable-depend option of configure to have the
   compiler compute the dependencies automatically.