Part II. The SQL Language

Top  Previous  Next

Part II. The SQL Language

This part describes the use of the SQL language in PostgreSQL. We start with describing the general syntax of SQL, then explain how to create the structures to hold data, how to populate the database, and how to query it. The middle part lists the available data types and functions for use in SQL commands. The rest treats several aspects that are important for tuning a database for optimal performance.

The information in this part is arranged so that a novice user can follow it start to end to gain a full understanding of the topics without having to refer forward too many times. The chapters are intended to be self-contained, so that advanced users can read the chapters individually as they choose. The information in this part is presented in a narrative fashion in topical units. Readers looking for a complete description of a particular command should look into Part VI, “Reference”.

Readers of this part should know how to connect to a PostgreSQL database and issue SQL commands. Readers that are unfamiliar with these issues are encouraged to read Part I, “Tutorial” first. SQL commands are typically entered using the PostgreSQL interactive terminal psql, but other programs that have similar functionality can be used as well.

Table of Contents

4. SQL Syntax

4.1. Lexical Structure

4.1.1. Identifiers and Key Words

4.1.2. Constants

4.1.3. Operators

4.1.4. Special Characters

4.1.5. Comments

4.1.6. Lexical Precedence

4.2. Value Expressions

4.2.1. Column References

4.2.2. Positional Parameters

4.2.3. Subscripts

4.2.4. Field Selection

4.2.5. Operator Invocations

4.2.6. Function Calls

4.2.7. Aggregate Expressions

4.2.8. Type Casts

4.2.9. Scalar Subqueries

4.2.10. Array Constructors

4.2.11. Row Constructors

4.2.12. Expression Evaluation Rules

5. Data Definition

5.1. Table Basics

5.2. Default Values

5.3. Constraints

5.3.1. Check Constraints

5.3.2. Not-Null Constraints

5.3.3. Unique Constraints

5.3.4. Primary Keys

5.3.5. Foreign Keys

5.4. System Columns

5.5. Modifying Tables

5.5.1. Adding a Column

5.5.2. Removing a Column

5.5.3. Adding a Constraint

5.5.4. Removing a Constraint

5.5.5. Changing a Column's Default Value

5.5.6. Changing a Column's Data Type

5.5.7. Renaming a Column

5.5.8. Renaming a Table

5.6. Privileges

5.7. Schemas

5.7.1. Creating a Schema

5.7.2. The Public Schema

5.7.3. The Schema Search Path

5.7.4. Schemas and Privileges

5.7.5. The System Catalog Schema

5.7.6. Usage Patterns

5.7.7. Portability

5.8. Inheritance

5.8.1. Caveats

5.9. Partitioning

5.9.1. Overview

5.9.2. Implementing Partitioning

5.9.3. Partitioning and Constraint Exclusion

5.10. Other Database Objects

5.11. Dependency Tracking

6. Data Manipulation

6.1. Inserting Data

6.2. Updating Data

6.3. Deleting Data

7. Queries

7.1. Overview

7.2. Table Expressions

7.2.1. The FROM Clause

7.2.2. The WHERE Clause

7.2.3. The GROUP BY and HAVING Clauses

7.3. Select Lists

7.3.1. Select-List Items

7.3.2. Column Labels

7.3.3. DISTINCT

7.4. Combining Queries

7.5. Sorting Rows

7.6. LIMIT and OFFSET

8. Data Types

8.1. Numeric Types

8.1.1. Integer Types

8.1.2. Arbitrary Precision Numbers

8.1.3. Floating-Point Types

8.1.4. Serial Types

8.2. Monetary Types

8.3. Character Types

8.4. Binary Data Types

8.5. Date/Time Types

8.5.1. Date/Time Input

8.5.2. Date/Time Output

8.5.3. Time Zones

8.5.4. Internals

8.6. Boolean Type

8.7. Geometric Types

8.7.1. Points

8.7.2. Line Segments

8.7.3. Boxes

8.7.4. Paths

8.7.5. Polygons

8.7.6. Circles

8.8. Network Address Types

8.8.1. inet

8.8.2. cidr

8.8.3. inet vs. cidr

8.8.4. macaddr

8.9. Bit String Types

8.10. Arrays

8.10.1. Declaration of Array Types

8.10.2. Array Value Input

8.10.3. Accessing Arrays

8.10.4. Modifying Arrays

8.10.5. Searching in Arrays

8.10.6. Array Input and Output Syntax

8.11. Composite Types

8.11.1. Declaration of Composite Types

8.11.2. Composite Value Input

8.11.3. Accessing Composite Types

8.11.4. Modifying Composite Types

8.11.5. Composite Type Input and Output Syntax

8.12. Object Identifier Types

8.13. Pseudo-Types

9. Functions and Operators

9.1. Logical Operators

9.2. Comparison Operators

9.3. Mathematical Functions and Operators

9.4. String Functions and Operators

9.5. Binary String Functions and Operators

9.6. Bit String Functions and Operators

9.7. Pattern Matching

9.7.1. LIKE

9.7.2. SIMILAR TO Regular Expressions

9.7.3. POSIX Regular Expressions

9.8. Data Type Formatting Functions

9.9. Date/Time Functions and Operators

9.9.1. EXTRACT, date_part

9.9.2. date_trunc

9.9.3. AT TIME ZONE

9.9.4. Current Date/Time

9.10. Geometric Functions and Operators

9.11. Network Address Functions and Operators

9.12. Sequence Manipulation Functions

9.13. Conditional Expressions

9.13.1. CASE

9.13.2. COALESCE

9.13.3. NULLIF

9.13.4. GREATEST and LEAST

9.14. Array Functions and Operators

9.15. Aggregate Functions

9.16. Subquery Expressions

9.16.1. EXISTS

9.16.2. IN

9.16.3. NOT IN

9.16.4. ANY/SOME

9.16.5. ALL

9.16.6. Row-wise Comparison

9.17. Row and Array Comparisons

9.17.1. IN

9.17.2. NOT IN

9.17.3. ANY/SOME (array)

9.17.4. ALL (array)

9.17.5. Row-wise Comparison

9.18. Set Returning Functions

9.19. System Information Functions

9.20. System Administration Functions

10. Type Conversion

10.1. Overview

10.2. Operators

10.3. Functions

10.4. Value Storage

10.5. UNION, CASE, and Related Constructs

11. Indexes

11.1. Introduction

11.2. Index Types

11.3. Multicolumn Indexes

11.4. Combining Multiple Indexes

11.5. Unique Indexes

11.6. Indexes on Expressions

11.7. Partial Indexes

11.8. Operator Classes

11.9. Examining Index Usage

12. Concurrency Control

12.1. Introduction

12.2. Transaction Isolation

12.2.1. Read Committed Isolation Level

12.2.2. Serializable Isolation Level

12.3. Explicit Locking

12.3.1. Table-Level Locks

12.3.2. Row-Level Locks

12.3.3. Deadlocks

12.4. Data Consistency Checks at the Application Level

12.5. Locking and Indexes

13. Performance Tips

13.1. Using EXPLAIN

13.2. Statistics Used by the Planner

13.3. Controlling the Planner with Explicit JOIN Clauses

13.4. Populating a Database

13.4.1. Disable Autocommit

13.4.2. Use COPY

13.4.3. Remove Indexes

13.4.4. Remove Foreign Key Constraints

13.4.5. Increase maintenance_work_mem

13.4.6. Increase checkpoint_segments

13.4.7. Run ANALYZE Afterwards

13.4.8. Some Notes About pg_dump