79 captures

30 Dec 2004 - 12 Nov 2025

Apr	MAY	Jun
	28
2009	2010	2011

success

fail

About this capture

TIMESTAMPS

PostgreSQL: Introduction and Concepts
Bruce Momjian

• Copyright
• Dedication
• Contents
• List of Figures
• List of Tables
• Foreword
• Preface
• Acknowledgments
  • Steering
  • Major Developers
  • Non-code Contributors
• History of POSTGRESQL
  • Introduction
  • University of California at Berkeley
  • Development Leaves Berkeley
  • POSTGRESQL Global Development Team
  • Open Source Software
  • Summary
• Issuing Database Commands
  • Starting a Database Session
    • Choosing an Interface
    • Choosing a Database
    • Starting a Session
  • Controlling a Session
    • Typing in the Query Buffer
    • Displaying the Query Buffer
    • Erasing the Query Buffer
  • Getting Help
  • Exiting a Session
  • Summary
• Basic SQL Commands
  • Relational Databases
  • Creating Tables
  • Adding Data with INSERT
  • Viewing Data with SELECT
  • Selecting Specific Rows with WHERE
  • Removing Data with DELETE
  • Modifying Data with UPDATE
  • Sorting Data with ORDER BY
  • Destroying Tables
  • Summary
• Customizing Queries
  • Data Types
  • Quotes Inside Text
  • Using NULL Values
  • Controlling DEFAULT Values
  • Column Labels
  • Comments
  • AND/OR Usage
  • Range of Values
  • LIKE Comparison
  • Regular Expressions
  • CASE Clause
  • Distinct Rows
  • Functions and Operators
  • SET, SHOW, and RESET
  • Summary
• SQL Aggregates
  • Aggregates
  • Using GROUP BY
  • Using HAVING
  • Query Tips
  • Summary
• Joining Tables
  • Table and Column References
  • Joined Tables
  • Creating Joined Tables
  • Performing Joins
  • Three- and Four-Table Joins
  • Additional Join Possibilities
  • Choosing a Join Key
  • One-to-Many Joins
  • Unjoined Tables
  • Table Aliases and Self-joins
  • Non-equijoins
  • Ordering Multiple Parts
  • Primary and Foreign Keys
  • Summary
• Numbering Rows
  • Object Identification Numbers (OIDs)
  • Object Identification Number Limitations
    • Nonsequential Numbering
    • Nonmodifiable
    • Not Backed Up by Default
  • Sequences
  • Creating Sequences
  • Using Sequences to Number Rows
  • Serial Column Type
  • Manually Numbering Rows
  • Summary
• Combining SELECTs
  • UNION, EXCEPT, and INTERSECT Clauses
  • Subqueries
    • Subqueries as Constants
    • Subqueries as Correlated Values
    • Subqueries as Lists of Values
    • NOT IN and Subqueries with NULL Values
    • Subqueries Returning Multiple Columns
    • ANY, ALL, and EXISTS Clauses
    • Summary
  • Outer Joins
  • Subqueries in Non-SELECT Queries
  • UPDATE with FROM
  • Inserting Data Using SELECT
  • Creating Tables Using SELECT
  • Summary
• Data Types
  • Purpose of Data Types
  • Installed Types
    • Character String
    • Number
    • Temporal
    • Logical
    • Geometric
    • Network
    • Internal
  • Type Conversion Using CAST
  • Support Functions
  • Support Operators
  • Support Variables
  • Arrays
  • Large Objects (BLOBs)
  • Summary
• Transactions and Locks
  • Transactions
  • Multistatement Transactions
  • Visibility of Committed Transactions
  • Read Committed and Serializable Isolation Levels
  • Locking
  • Deadlocks
  • Summary
• Performance
  • Indexes
  • Unique Indexes
  • CLUSTER
  • VACUUM
  • VACUUM ANALYZE
  • EXPLAIN
  • Summary
• Controlling Results
  • LIMIT
  • Cursors
  • Summary
• Table Management
  • Temporary Tables
  • ALTER TABLE
  • GRANT and REVOKE
  • Inheritance
  • Views
  • Rules
  • LISTEN and NOTIFY
  • Summary
• Constraints
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • Foreign Key/REFERENCES
    • Modification of Primary Key Row
    • Multicolumn Primary Keys
    • Handling NULL Values in the Foreign Key
    • Frequency of Foreign Key Checking
  • CHECK
  • Summary
• Importing and Exporting Data
  • Using COPY
  • COPY File Format
  • DELIMITERS
  • COPY Without Files
  • Backslashes and NULL Values
  • COPY Tips
  • Summary
• Database Query Tools
  • Psql
    • Query Buffer Commands
    • General Commands
    • Output Format Options
    • Output Format Shortcuts
    • Variables
    • Listing Commands
    • Large Object Commands
    • Psql Command-line Arguments and Start-up File
  • Pgaccess
  • Summary
• Programming Interfaces
  • C Language Interface (LIBPQ)
  • Pgeasy (LIBPGEASY)
  • Embedded C (ECPG)
  • C++ (LIBPQ++)
  • Compiling Programs
  • Assignment to Program Variables
  • ODBC
  • Java (JDBC)
  • Scripting Languages
  • Perl
  • TCL/TK (PGTCLSH/PGTKSH)
  • Python
  • PHP
  • Installing Scripting Languages
  • Summary
• Functions and Triggers
  • Functions
  • SQL Functions
  • PL/PGSQL Functions
  • Triggers
  • Summary
• Extending POSTGRESQL Using C
  • Write the C Code
  • Compile the C Code
  • Register the New Functions
  • Create Operators, Types, and Aggregates
  • Summary
• Administration
  • Files
  • Creating Users
  • Creating Databases
  • Access Configuration
    • Local
    • Host and Hostssl
    • User Mappings
    • Examples
  • Backup and Restore
  • Server Start-up and Shutdown
  • Monitoring
  • Performance
  • System Tables
  • Internationalization
  • Upgrading
  • Summary
• Additional Resources
  • Mailing List Support
  • Supplied Documentation
  • Commercial Support
  • Modifying the Source Code
  • Frequently Asked Questions (FAQs)
    • General Questions
    • User Client Questions
    • Administrative Questions
    • Operational Questions
    • Extending POSTGRESQL
  • General Questions
    • 1.1) What is POSTGRESQL?
    • 1.2) What's the copyright on POSTGRESQL?
    • 1.3) What Unix platforms does POSTGRESQL run on?
    • 1.4) What non-Unix ports are available?
    • 1.5) Where can I get POSTGRESQL?
    • 1.6) Where can I get support?
    • 1.7) What is the latest release?
    • 1.8) What documentation is available?
    • 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?
    • 1.14) How does POSTGRESQL compare to other DBMS's?
      • Features
      • Performance
      • Reliability
      • Support
      • Price
  • User Client Questions
    • 2.1) Are there ODBC drivers for POSTGRESQL?
    • 2.2) What tools are available for hooking POSTGRESQL to Web pages?
    • 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
    • 3.1) Why does initdb fail?
    • 3.2) How do I install POSTGRESQL somewhere other than /usr/local/pgsql?
    • 3.3)When I start the postmaster, I get a Bad System Call or core dumped message. Why?
    • 3.4) When I try to start the postmaster, I get IpcMemoryCreate errors. Why?
    • 3.5) When I try to start the postmaster, I get IpcSemaphoreCreate errors. Why?
    • 3.6) How do I prevent other hosts from accessing my POSTGRESQL database?
    • 3.7) Why can't I connect to my database from another machine?
    • 3.8) Why can't I access the database as the root user?
    • 3.9) All my servers crash under concurrent table access. Why?
    • 3.10) How do I tune the database engine for better performance?
    • 3.11) What debugging features are available?
    • 3.12) I get ``Sorry, too many clients'' when trying to connect. Why?
    • 3.13) What are the pg_sorttempNNN.NN files in my database directory?
  • Operational Questions
    • 4.1) Why is the system confused about commas, decimal points, and date formats?
    • 4.2) What is the exact difference between binary cursors and normal cursors?
    • 4.3) How do I SELECT only the first few rows of a query?
    • 4.4) How do I get a list of tables or other information I see in psql?
    • 4.5) How do you remove a column from a table?
    • 4.6) What is the maximum size for a row, table, database?
    • 4.7) How much database disk space is required to store data from a typical text file?
    • 4.8) How do I find out what indices or operations are defined in the database?
    • 4.9) My queries are slow or don't make use of the indexes. Why?
    • 4.10) How do I see how the query optimizer is evaluating my query?
    • 4.11) What is an R-tree index?
    • 4.12) What is Genetic Query Optimization?
    • 4.13) How do I do regular expression searches and case-insensitive regular expression searches?
    • 4.14) In a query, how do I detect if a field is NULL?
    • 4.15) What is the difference between the various character types?
    • 4.16.1) How do I create a serial/auto-incrementing field?
    • 4.16.2) How do I get the value of a SERIAL insert?
    • 4.16.3) Don't currval() and nextval() lead to a race condition with other users?
    • 4.17) What is an OID? What is a TID?
    • 4.18) What is the meaning of some of the terms used in POSTGRESQL?
    • 4.19) Why do I get the error ``FATAL: palloc failure: memory exhausted?''
    • 4.20) How do I tell what POSTGRESQL version I am running?
    • 4.21) My large-object operations get invalid large obj descriptor. Why?
    • 4.22) How do I create a column that will default to the current time?
    • 4.23) Why are my subqueries using IN so slow?
    • 4.24) How do I do an outer join?
  • Extending POSTGRESQL
    • 5.1) I wrote a user-defined function. When I run it in psql, why does it dump core?
    • 5.2) What does the message ``NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set!'' mean?
    • 5.3) How can I contribute some nifty new types and functions to POSTGRESQL?
    • 5.4) How do I write a C function to return a tuple?
    • 5.5) I have changed a source file. Why does the recompile not see the change?
• Installation
  • Getting POSTGRESQL
  • Creating the POSTGRESQL User
  • Configuration
  • Compilation
  • Installation
  • Initialization
  • Starting the Server
  • Creating a Database
• PostgreSQL Nonstandard Features by Chapter
• Reference Manual
  • ABORT
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • ALTER GROUP
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • ALTER TABLE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • ALTER USER
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • BEGIN
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CLOSE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CLUSTER
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • COMMENT
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • COMMIT
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • COPY
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • File Formats
      • Text Format
      • Binary Format
      • Alignment of Binary Data
    • Usage
    • Compatibility
      • SQL92
  • CREATE AGGREGATE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE CONSTRAINT TRIGGER
    • Name
      • Inputs
      • Outputs
    • Description
  • CREATE DATABASE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE FUNCTION
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
      • SQL/PSM
  • CREATE GROUP
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • CREATE INDEX
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE LANGUAGE
    • Name
      • Inputs
      • Outputs
    • Description
      • Writing PL handlers
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE OPERATOR
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE RULE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
      • Example of a circular rewrite rule combination.
    • Usage
    • Compatibility
      • SQL92
  • CREATE SEQUENCE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE TABLE
    • Name
      • Inputs
      • Outputs
    • Description
    • DEFAULT Clause
      • Inputs
      • Outputs
      • Description
      • Usage
    • Column CONSTRAINT Clause
      • Inputs
      • Description
      • NOT NULL Constraint
      • Outputs
    • Description
      • Usage
      • UNIQUE Constraint
      • Inputs
      • Outputs
      • Description
      • Usage
      • The CHECK Constraint
      • Inputs
      • Outputs
      • Description
      • PRIMARY KEY Constraint
      • Inputs
      • Outputs
      • Description
      • Notes
      • REFERENCES Constraint
      • Inputs
      • Description
      • Notes
    • Table CONSTRAINT Clause
      • Inputs
      • Outputs
      • Description
      • UNIQUE Constraint
      • Inputs
      • Outputs
      • Description
      • Usage
      • PRIMARY KEY Constraint
      • Inputs
      • Outputs
      • Description
      • REFERENCES Constraint
      • Inputs
      • Outputs
      • Description
    • Usage
      • Notes
    • Compatibility
      • SQL92
  • CREATE TABLE AS
    • Name
      • Inputs
      • Outputs
    • Description
  • CREATE TRIGGER
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • CREATE TYPE
    • Name
      • Inputs
      • Outputs
    • Description
      • Array Types
      • Large Object Types
    • Examples
      • Notes
    • Compatibility
      • SQL3
  • CREATE USER
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • CREATE VIEW
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • createdb
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
  • createlang
    • Name
      • Inputs
      • Outputs
    • Description
    • Notes
    • Usage
  • createuser
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
  • DECLARE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DELETE
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • DROP AGGREGATE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP DATABASE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Compatibility
      • SQL92
  • DROP FUNCTION
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
      • SQL/PSM
  • DROP GROUP
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • DROP INDEX
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP LANGUAGE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP OPERATOR
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP RULE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP SEQUENCE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP TABLE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP TRIGGER
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • DROP TYPE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL3
  • DROP USER
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • DROP VIEW
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
      • Inputs
      • Notes
  • dropdb
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
  • droplang
    • Name
      • Inputs
      • Outputs
    • Description
    • Notes
    • Usage
  • dropuser
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
  • ecpg
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
      • Preprocessing for Compilation
      • Compiling and Linking
    • Grammar
      • Libraries
      • Variable Declaration
      • Error Handling
      • Connecting to the Database Server
      • Queries
    • Notes
  • END
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • EXPLAIN
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • FETCH
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • GRANT
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • initdb
    • Name
      • Inputs
      • Outputs
    • Description
  • initlocation
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
  • INSERT
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • ipcclean
    • Name
      • Inputs
      • Outputs
    • Description
  • LISTEN
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • LOAD
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • LOCK
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • MOVE
    • Name
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • NOTIFY
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • pg_ctl
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
      • Starting postmaster
      • Stopping postmaster
      • Restarting postmaster
      • postmaster status
  • pg_dump
    • Name
      • Inputs
      • Outputs
    • Description
    • Notes
    • Usage
  • pg_dumpall
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
  • pg_passwd
    • Name
    • Description
  • pg_upgrade
    • Name
    • Description
      • Upgrading Postgres with pg_upgrade
  • pgaccess
    • Name
      • Inputs
      • Outputs
    • Description
  • pgtclsh
    • Name
      • Inputs
      • Outputs
    • Description
  • pgtksh
    • Name
      • Inputs
      • Outputs
    • Description
  • postgres
    • Name
      • Inputs
      • Outputs
    • Description
    • Notes
  • postmaster
    • Name
      • Inputs
      • Outputs
    • Description
    • Notes
    • Usage
  • psql
    • Name
      • Summary
    • Description
      • Connecting To A Database
      • Entering Queries
    • psql Meta-Commands
    • Command-line Options
    • Advanced features
      • Variables
      • SQL Interpolation
      • Prompting
      • Miscellaneous
      • GNU readline
    • Examples
    • Appendix
      • Bugs and Issues
  • REINDEX
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • RESET
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • REVOKE
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • ROLLBACK
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • SELECT
    • Name
      • Inputs
      • Outputs
    • Description
      • WHERE Clause
      • GROUP BY Clause
      • HAVING Clause
      • ORDER BY Clause
      • UNION Clause
      • INTERSECT Clause
      • EXCEPT Clause
      • LIMIT Clause
    • Usage
    • Compatibility
      • Extensions
      • SQL92
  • SELECT INTO
    • Name
      • Inputs
      • Outputs
    • Description
  • SET
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • SHOW
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • TRUNCATE
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • UNLISTEN
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • UPDATE
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
    • Compatibility
      • SQL92
  • VACUUM
    • Name
      • Inputs
      • Outputs
    • Description
      • Notes
    • Usage
    • Compatibility
      • SQL92
  • vacuumdb
    • Name
      • Inputs
      • Outputs
    • Description
    • Usage
• Bibliography
• Index