Database HOWTO (PostgreSQL Relational Database System)

Al Dev (Alavoor Vasudevan) deepav@pol.net

1. Introduction

2. What is PostgreSQL ?

3. Where to get it?

4. Regression Test Package to validate PostgreSQL against ISO/ANSI SQL Standards

5. GUI FrontEnd Tool for PostgreSQL (Graphical User Interface)

6. ODBC Drivers for PostgreSQL

7. UDBC Drivers for PostgreSQL

8. JDBC Drivers for PostgreSQL

9. Kanchenjunga - Java RAD Tool for PostgreSQL

10. Java Classes for PostgreSQL

11. Perl 5 interface for PostgreSQL

12. Windows Interactive Query Tool for PostgreSQL (WISQL)

13. Interactive Query Tool - ISQL for PostgreSQL

14. Problem/Project Tracking System Application Tool for PostgreSQL

15. PostgreSQL 4GL for web database applications - AppGEN Development System

16. Web Database Design/Implementation tool for PostgreSQL - EARP

• 16.1 What is EARP?
• 16.2 Implementation
• 16.3 What you need to run EARP
• 16.4 News Flash
• 16.5 How does it work?
• 16.6 Some online examples
• 16.7 Where do I get it?
• 16.8 Available Documentation
• 16.9 A History of EARP

17. WWW Web interface for PostgresSQL - dbengine

18. Apache Webserver Module for PostgreSQL - NeoSoft NeoWebScript

19. PHP/FI Server-side html-embedded scripting language for PostgreSQL

• 19.1 Major Features
• 19.2 Credits
• 19.3 Brief History
• 19.4 So, what can I do with PHP/FI?
• 19.5 A simple example
• 19.6 CGI Redirection
• 19.7 Running PHP/FI from the command line

20. Python Interface for PostgreSQL

• 20.1 Where to get ... ?
• 20.2 Information and support

21. Gateway between PostgreSQL and the WWW - WDB-P95

• 21.1 Does the Postgres95 server,pgperl, and httpd have to be on the same host?
• 21.2 New Version

22. "C" language Interface for PostgreSQL

23. "C++" language Interface for PostgreSQL

24. ESQL/C for PostgreSQL

25. Japanese Kanji Code for PostgreSQL

26. PostgreSQL Port to Windows 95/Windows NT

27. Mailing Lists

28. Documentations and Books

29. Technical support for PostgreSQL

30. Economic and Business Aspects

31. Conclusion

32. FAQ - Questions on PostgreSQL

• 32.1 Frequently Asked Questions (FAQ) for PostgreSQL
• 32.2 What is PostgreSQL?
• 32.3 What does PostgreSQL run on?
• 32.4 Where can I get PostgreSQL?
• 32.5 What's the copyright on PostgreSQL?
• 32.6 Support for PostgreSQL
• 32.7 Latest release of PostgreSQL
• 32.8 Is there a commercial version of PostgreSQL?
• 32.9 What documentation is available for PostgreSQL?
• 32.10 What version of SQL does PostgreSQL use?
• 32.11 Does PostgreSQL work with databases from earlier versions of postgres?
• 32.12 How many people use PostgreSQL?

33. FAQ - Installation Questions

• 33.1 Initdb doesn't run
• 33.2 When I start up the postmaster, I get "FindBackend- could not find a backend to execute..." "postmaster- could not find backend to execute..."
• 33.3 The system seems to be confused about commas, decimal points, and date formats.
• 33.4 How do I install PostgreSQL somewhere other than /usr/local/pgsql?
• 33.5 When I run postmaster, I get a Bad System Call core dumped message.
• 33.6 When I try to start the postmaster, I get IpcMemoryCreate errors.
• 33.7 I have changed a source file, but a recompile does not see the change?

34. FAQ - PostgreSQL Features

• 34.1 How do I specify a KEY or other constraints on a column?
• 34.2 Does PostgreSQL support nested subqueries?
• 34.3 How do I define a unique indices?
• 34.4 I've having a lot of problems using rules.
• 34.5 I can't seem to write into the middle of large objects reliably.
• 34.6 Does PostgreSQL have a graphical user interface? A report generator? A embedded query language interface?
• 34.7 How can I write client applications to PostgreSQL?
• 34.8 How do I prevent other hosts from accessing my PostgreSQL backend?
• 34.9 How do I set up a pg_group?
• 34.10 What is the exact difference between binary cursors and normal cursors?
• 34.11 Why doesn't the != operator work?
• 34.12 What is a R-tree index and what is it used for?
• 34.13 What is the maximum size for a tuple?
• 34.14 I defined indices but my queries don't seem to make use of them. Why?
• 34.15 Are there ODBC drivers for PostgreSQL?
• 34.16 How do I use postgres for multi-dimensional indexing (> 2 dimensions)?
• 34.17 How do I do regular expression searches? case-insensitive regexp searching?
• 34.18 I can't access the database as the 'root' user.
• 34.19 I experienced a server crash during a vacuum. How do I remove the lock file?
• 34.20 What is the difference between the various character types?
• 34.21 In a query, how do I detect if a field is NULL?
• 34.22 How do I see how the query optimizer is evaluating my query?
• 34.23 How do I create a serial field?
• 34.24 How do I create a multi-column index?
• 34.25 What are the temp_XXX files in my database directory?
• 34.26 Why are my table files not getting any smaller after a delete?
• 34.27 Why can't I connect to my database from another machine?
• 34.28 I get the error 'default index class unsupported' when creating an index. How do I do it?
• 34.29 Why does creating an index crash the backend server?
• 34.30 How do I find out what indexes or operations are defined in the database?
• 34.31 Why do statements require an extra character at the end? Why does 'createuser' return 'unexpected last match in input()'? Why does pg_dump fail?
• 34.32 All my servers crash under concurrent table access. Why?
• 34.33 What tools are available for hooking postgres to Web pages?
• 34.34 What is the time-warp feature and how does it relate to vacuum?
• 34.35 How do I tune the database engine for better performance?
• 34.36 What debugging features are available in PostgreSQL?
• 34.37 What is an oid? What is a tid?
• 34.38 What is the meaning of some of the terms used in Postgres?
• 34.39 What is Genetic Query Optimization?
• 34.40 I am running Solaris and my dates display wrong. Why?
• 34.41 How do I enable more than 32 concurrent backends?

35. FAQ - Extending PostgreSQL

• 35.1 I wrote a user-defined function and when I run it in psql, it dumps
• 35.2 I get messages of the type NOTICE-PortalHeapMemoryFree- 0x402251d0 not in alloc set!
• 35.3 I've written some nifty new types and functions for PostgreSQL.
• 35.4 How do I write a C function to return a tuple?

36. FAQ - Bugs

• 36.1 How do I make a bug report?

37. FAQ - Compiling PostgreSQL

• 37.1 What changes do I need to make to src/Makefile.global or src/Makefile.custom?
• 37.2 Why do I get problems with missing libreadline?
• 37.3 REDHAT Why do I get problems with missing libdl and dlfcn.h?
• 37.4 SLACKWARE 3.1 Why do I get problems with missing libdl and dlfcn.h?
• 37.5 My compile of the backend dies complaining about the include file dlfcn.h missing
• 37.6 GCC complains about an ignored option -fpic
• 37.7 I get warnings of the form warning- cast from pointer to integer of different size
• 37.8 SuSE-Linux 4.2 Where is curses and termcap?
• 37.9 Why do I get problems with ld.so?
• 37.10 Why do I get `yy_flush_buffer undefined' errors?
• 37.11 How do I compile PostgreSQL on an a.out system?
• 37.12 Why does make fail with -
• 37.13 What are the references in X11_LIB to libsocket and libnsl in src/Makefile.global?
• 37.14 DEBIAN Where is libtermcap?

38. FAQ - Compiling accessory programs

• 38.1 The linker fails to find libX11 when compiling pgtclsh

39. FAQ - Runtime Problems

• 39.1 I get an error reporting _fUnKy_POSTPORT_sTuFf_ undefined when running scripts like createuser
• 39.2 I run postmaster and after that system says 'Bad system call(Core dumped)'
• 39.3 When I try to start the Postmaster, why do I get an error of the form
• 39.4 Why doesn't createuser work?
• 39.5 Why do I get an error like-
• 39.6 Why does psql fail with-

40. FAQ IRIX-PostgreSQL - Compiling PostgreSQL

41. FAQ-IRIX Installing Postgres95

• 41.1 What extra items do I need to install Postgres95 under Irix?
• 41.2 What changes do I need to make to src/Makefile.global or
• 41.3 What are the references in X11_LIB to libsocket and libnsl in
• 41.4 Are there any other changes I should make?
• 41.5 Can I install PostgreSQL under Irix 6.4?

42. FAQ-IRIX Deinstalling Postgres95

• 42.1 Why can't I move the executable files?

43. FAQ-IRIX Extending Postgres95

• 43.1 How do I compile a C program to create a function for extending

44. FAQ-IRIX PostgreSQL AUTHOR contacts

45. Copyright Notice