Part II. The SQL Language

This part describes the use of the SQL language in ProtonBase. 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 see Part IV.

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

Table of Contents

SQL Syntax

 Lexical Structure

 Value Expressions

 Calling Functions

Data Definition

 Table Basics

 Default Values

 Generated Columns

 Constraints

 System Columns

 Modifying Tables

 Schemas

 Other Database Objects

 Dependency Tracking

Data Manipulation

 Inserting Data

 Updating Data

 Deleting Data

 Returning Data from Modified Rows

Queries

 Overview

 Table Expressions

 Select Lists

 Combining Queries (UNION, INTERSECT, EXCEPT)

 Sorting Rows (ORDER BY)

 LIMIT and OFFSET

 VALUES Lists

 WITH Queries (Common Table Expressions)

Data Types

 Numeric Types

 Character Types

 Binary Data Types

 Date/Time Types

 Boolean Type

 Enumerated Types

 Network Address Types

 Bit String Types

 UUID Type

 JSON Types

 Arrays

 Object Identifier Types

Functions and Operators

 Logical Operators

 Comparison Functions and Operators

 Mathematical Functions and Operators

 String Functions and Operators

 Binary String Functions and Operators

 Bit String Functions and Operators

 Pattern Matching

 Data Type Formatting Functions

 Date/Time Functions and Operators

 Enum Support Functions

 Network Address Functions and Operators

 UUID Functions

 JSON Functions and Operators

 Sequence Manipulation Functions

 Conditional Expressions

 Array Functions and Operators

 Aggregate Functions

 Window Functions

 Subquery Expressions

 Row and Array Comparisons

 Set Returning Functions

 System Information Functions and Operators

 System Administration Functions

Type Conversion

 Overview

 Operators

 Functions

 Value Storage

 UNION, CASE, and Related Constructs

 SELECT Output Columns