You are previewing PostgreSQL: Up and Running.

PostgreSQL: Up and Running

Cover of PostgreSQL: Up and Running by Regina Obe... Published by O'Reilly Media, Inc.
  1. PostgreSQL: Up and Running
  2. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  3. Preface
    1. What Makes PostgreSQL Special and Why Use It?
    2. Why Not PostgreSQL?
    3. For More Information on PostgreSQL
    4. Conventions Used in This Book
    5. Using Code Examples
    6. Safari® Books Online
    7. How to Contact Us
  4. 1. The Basics
    1. Where to Get PostgreSQL
      1. Notable PostgreSQL Forks
    2. Administration Tools
    3. What’s New in Latest Versions of PostgreSQL?
      1. Why Upgrade?
      2. What to Look for in PostgreSQL 9.2
      3. PostgreSQL 9.1 Improvements
    4. Database Drivers
    5. Server and Database Objects
    6. Where to Get Help
  5. 2. Database Administration
    1. Configuration Files
      1. The postgresql.conf File
      2. The pg_hba.conf File
      3. Reload the Configuration Files
    2. Setting Up Groups and Login Roles (Users)
      1. Creating an Account That Can Log In
      2. Creating Group Roles
      3. Roles Inheriting Rights
    3. Databases and Management
      1. Creating and Using a Template Database
      2. Organizing Your Database Using Schemas
      3. Permissions
    4. Extensions and Contribs
      1. Installing Extensions
      2. Common Extensions
    5. Backup
      1. Selective Backup Using pg_dump
      2. Systemwide Backup Using pg_dumpall
    6. Restore
      1. Terminating Connections
      2. Using psql to Restore Plain Text SQL backups
      3. Using pg_restore
    7. Managing Disk Space with Tablespaces
      1. Creating Tablespaces
      2. Moving Objects Between Tablespaces
    8. Verboten
      1. Delete PostgreSQL Core System Files and Binaries
      2. Giving Full Administrative Rights to the Postgres System (Daemon) Account
      3. Setting shared_buffers Too High
      4. Trying to Start PostgreSQL on a Port Already in Use
  6. 3. psql
    1. Interactive psql
    2. Non-Interactive psql
    3. Session Configurations
      1. Changing Prompts
      2. Timing Details
      3. AUTOCOMMIT
      4. Shortcuts
      5. Retrieving Prior Commands
    4. psql Gems
      1. Executing Shell Commands
      2. Lists and Structures
    5. Importing and Exporting Data
    6. Basic Reporting
  7. 4. Using pgAdmin
    1. Getting Started
      1. Overview of Features
      2. Connecting to a PostgreSQL server
      3. Navigating pgAdmin
    2. pgAdmin Features
      1. Accessing psql from pgAdmin
      2. Editing postgresql.conf and pg_hba.conf from pgAdmin
      3. Creating Databases and Setting Permissions
      4. Backup and Restore
    3. pgScript
    4. Graphical Explain
    5. Job Scheduling with pgAgent
      1. Installing pgAgent
      2. Scheduling Jobs
      3. Helpful Queries
  8. 5. Data Types
    1. Numeric Data Types
      1. Serial
      2. Generate Series Function
    2. Arrays
      1. Array Constructors
      2. Referencing Elements in An Array
      3. Array Slicing and Splicing
    3. Character Types
      1. String Functions
      2. Splitting Strings into Arrays, Tables, or Substrings
      3. Regular Expressions and Pattern Matching
    4. Temporal Data Types
      1. Time Zones: What It Is and What It Isn’t
      2. Operators and Functions for Date and Time Data Types
    5. XML
      1. Loading XML Data
      2. Querying XML Data
    6. Custom and Composite Data Types
      1. All Tables Are Custom
      2. Building Your Own Custom Type
  9. 6. Of Tables, Constraints, and Indexes
    1. Tables
      1. Table Creation
      2. Multi-Row Insert
      3. An Elaborate Insert
    2. Constraints
      1. Foreign Key Constraints
      2. Unique Constraints
      3. Check Constraints
      4. Exclusion Constraints
    3. Indexes
      1. PostgreSQL Stock Indexes
      2. Operator Class
      3. Functional Indexes
      4. Partial Indexes
      5. Multicolumn Indexes
  10. 7. SQL: The PostgreSQL Way
    1. SQL Views
    2. Window Functions
      1. Partition By
      2. Order By
    3. Common Table Expressions
      1. Standard CTE
      2. Writeable CTEs
      3. Recursive CTE
    4. Constructions Unique to PostgreSQL
      1. DISTINCT ON
      2. LIMIT and OFFSET
      3. Shorthand Casting
      4. ILIKE for Case Insensitive Search
      5. Set Returning Functions in SELECT
      6. Selective DELETE, UPDATE, and SELECT from Inherited Tables
      7. RETURNING Changed Records
      8. Composite Types in Queries
  11. 8. Writing Functions
    1. Anatomy of PostgreSQL Functions
      1. Function Basics
      2. Trusted and Untrusted Languages
    2. Writing Functions with SQL
    3. Writing PL/pgSQL Functions
    4. Writing PL/Python Functions
      1. Basic Python Function
    5. Trigger Functions
    6. Aggregates
  12. 9. Query Performance Tuning
    1. EXPLAIN and EXPLAIN ANALYZE
    2. Writing Better Queries
      1. Overusing Subqueries in SELECT
      2. Avoid SELECT *
      3. Make Good Use of CASE
    3. Guiding the Query Planner
      1. Strategy Settings
      2. How Useful Is Your Index?
      3. Table Stats
      4. Random Page Cost and Quality of Drives
    4. Caching
  13. 10. Replication and External Data
    1. Replication Overview
      1. Replication Lingo
      2. PostgreSQL Built-in Replication Advancements
      3. Third-Party Replication Options
    2. Setting Up Replication
      1. Configuring the Master
      2. Configuring the Slaves
      3. Initiate the Replication Process
    3. Foreign Data Wrappers (FDW)
      1. Querying Simple Flat File Data Sources
      2. Querying More Complex Data Sources
  14. A. Install, Hosting, and Command-Line Guides
    1. Installation Guides and Distributions
      1. Windows, Mac OS X, Linux Desktops
      2. Other Linux, Unix, Mac Distributions
    2. Where to Host PostgreSQL
      1. Virtual Private Server (VPS)/Virtual Dedicated Server
      2. Cloud Server Hosters
      3. PostgreSQL Database as a Service
    3. PostgreSQL Packaged Command-Line Tools
      1. Database Backup: pg_dump
      2. Server Backup: pg_dumpall
      3. Database Backup: pg_restore
      4. psql: Interactive and Scriptable
  15. About the Authors
  16. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  17. Copyright
O'Reilly logo

Chapter 6. Of Tables, Constraints, and Indexes

Tables

In addition to the run-of-the-mill data table, PostgreSQL offers several kinds of tables that are rather unique: temporary, unlogged (demonstrated in Example 6-3), inherited (demonstrated in Example 6-2), and typed tables (demonstrated in Example 6-4).

Table Creation

In this section, we’ll demonstrate some common table creation examples. Most are similar to or exactly what you’ll find in other databases.

Example 6-1. Basic table creation

CREATE TABLE logs(
 log_id serial PRIMARY KEY 1, user_name varchar(50) 2
 , description text 3
 , log_ts timestamp with time zone NOT NULL DEFAULT current_timestamp); 4
CREATE INDEX idx_logs_log_ts ON logs USING btree(log_ts);
1

serial type is the data type you use when you want an incrementing auto number. It creates a companion sequence object and defines the new column as an integer with the default value set to the next value of the sequence object. It is often as a primary key.

varchar is a variable length string ...

The best content for your career. Discover unlimited learning on demand for around $1/day.