Part III. The SQL Language

This part describes the use of the SQL language in QHB. 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 Chapter SQL Commands.

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

• The SQL Language. Tutorial on the basics

  • Introduction
  • Concepts
  • Creating a New Table
  • Populating a Table With Rows
  • Querying a Table
  • Joins Between Tables
  • Aggregate Functions
  • Updates
  • Deletions

• Advanced Features

  • Introduction
  • Views
  • Foreign Keys
  • Transactions
  • Window Functions
  • Inheritance
  • Advanced SQL

• SQL Syntax

  • Lexical Structure
  • Value Expressions
  • Calling Functions

• Data Definition

  • Table Basics
  • Default Values
  • Generated Columns
  • Constraints
  • System Columns
  • Modifying Tables
  • Privileges
  • Row Security Policies
  • Schemas
  • Inheritance
  • Table Partitioning
  • Foreign Data
  • 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
  • Monetary Types
  • Character Types
  • Binary Data Types
  • Date/Time Types
  • Boolean Type
  • Enumerated Types
  • Geometric Types
  • Network Address Types
  • Bit String Types
  • Text Search Types
  • UUID Type
  • XML Type
  • JSON Types
  • Arrays
  • Composite Types
  • Range Types
  • Domain Types
  • Object Identifier Types
  • pg_lsn Type
  • Pseudo-Types
  • UREF Type

• 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
  • Geometric Functions and Operators
  • Network Address Functions and Operators
  • Text Search Functions and Operators
  • UUID Functions
  • XML Functions
  • JSON Functions and Operators
  • Sequence Manipulation Functions
  • Conditional Expressions
  • Array Functions and Operators
  • Range/Multirange Functions and Operators
  • Aggregate Functions
  • Window Functions
  • Subquery Expressions
  • Row and Array Comparisons
  • Set Returning Functions
  • System Information Functions and Operators
  • System Administration Functions
  • Trigger Functions
  • Event Trigger Functions
  • Statistics Information Functions

• Type Conversion

  • Overview
  • Operators
  • Functions
  • Value Storage
  • UNION, CASE, and Related Constructs
  • SELECT Output Columns

• Parallel Query

  • How Parallel Query Works
  • When Can Parallel Query Be Used
  • Parallel Plans
  • Parallel Safety

• PL/pgSQL — SQL Procedural Language

  • Overview
  • Structure of PL/pgSQL
  • Declarations
  • Expressions
  • Basic Statements
  • Control Structures
  • Cursors
  • Transaction Management
  • Errors and Messages
  • Trigger Functions
  • PL/pgSQL under the Hood
  • Tips for Developing in PL/pgSQL
  • Porting from Oracle PL/SQL

• Autonomous Transactions - PL/pgSQL Language Extension

  • Overview
  • Rationale
  • Usage
  • Optimization
  • Configuration
  • Limitations
  • Examples

• SQL Commands

  • ABORT
  • ALTER AGGREGATE
  • ALTER COLLATION
  • ALTER CONVERSION
  • ALTER DATABASE
  • ALTER DEFAULT PRIVILEGES
  • ALTER DOMAIN
  • ALTER EVENT TRIGGER
  • ALTER EXTENSION
  • ALTER FOREIGN DATA WRAPPER
  • ALTER FOREIGN TABLE
  • ALTER FUNCTION
  • ALTER GROUP
  • ALTER INDEX
  • ALTER LANGUAGE
  • ALTER LARGE OBJECT
  • ALTER MATERIALIZED VIEW
  • ALTER OPERATOR
  • ALTER OPERATOR CLASS
  • ALTER OPERATOR FAMILY
  • ALTER POLICY
  • ALTER PROCEDURE
  • ALTER PROFILE
  • ALTER PUBLICATION
  • ALTER ROLE
  • ALTER ROUTINE
  • ALTER RULE
  • ALTER SCHEMA
  • ALTER SEQUENCE
  • ALTER SERVER
  • ALTER STATISTICS
  • ALTER SUBSCRIPTION
  • ALTER SYSTEM
  • ALTER TABLE
  • ALTER TABLESPACE
  • ALTER TEXT SEARCH CONFIGURATION
  • ALTER TEXT SEARCH DICTIONARY
  • ALTER TEXT SEARCH PARSER
  • ALTER TEXT SEARCH TEMPLATE
  • ALTER TRIGGER
  • ALTER TYPE
  • ALTER USER
  • ALTER USER MAPPING
  • ALTER VIEW
  • ANALYZE
  • BEGIN
  • CALL
  • CHECKPOINT
  • CLOSE
  • CLUSTER
  • COMMENT
  • COMMIT
  • COMMIT PREPARED
  • COPY
  • CREATE ACCESS METHOD
  • CREATE AGGREGATE
  • CREATE CAST
  • CREATE COLLATION
  • CREATE CONVERSION
  • CREATE DATABASE
  • CREATE DOMAIN
  • CREATE EVENT TRIGGER
  • CREATE EXTENSION
  • CREATE FOREIGN DATA WRAPPER
  • CREATE FOREIGN TABLE
  • CREATE FUNCTION
  • CREATE GROUP
  • CREATE INDEX
  • CREATE LANGUAGE
  • CREATE MATERIALIZED VIEW
  • CREATE OPERATOR
  • CREATE OPERATOR CLASS
  • CREATE OPERATOR FAMILY
  • CREATE POLICY
  • CREATE PROCEDURE
  • CREATE PROFILE
  • CREATE PUBLICATION
  • CREATE QHBSECUREROLE
  • CREATE ROLE
  • CREATE RULE
  • CREATE SCHEMA
  • CREATE SEQUENCE
  • CREATE SERVER
  • CREATE STATISTICS
  • CREATE SUBSCRIPTION
  • CREATE TABLE
  • CREATE TABLE AS
  • CREATE TABLESPACE
  • CREATE TEXT SEARCH CONFIGURATION
  • CREATE TEXT SEARCH DICTIONARY
  • CREATE TEXT SEARCH PARSER
  • CREATE TEXT SEARCH TEMPLATE
  • CREATE TRANSFORM
  • CREATE TRIGGER
  • CREATE TYPE
  • CREATE USER
  • CREATE USER MAPPING
  • CREATE VIEW
  • DEALLOCATE
  • DECLARE
  • DELETE
  • DISCARD
  • DO
  • DROP ACCESS METHOD
  • DROP AGGREGATE
  • DROP CAST
  • DROP COLLATION
  • DROP CONVERSION
  • DROP DATABASE
  • DROP DOMAIN
  • DROP EVENT TRIGGER
  • DROP EXTENSION
  • DROP FOREIGN DATA WRAPPER
  • DROP FOREIGN TABLE
  • DROP FUNCTION
  • DROP GROUP
  • DROP INDEX
  • DROP LANGUAGE
  • DROP MATERIALIZED VIEW
  • DROP OPERATOR
  • DROP OPERATOR CLASS
  • DROP OPERATOR FAMILY
  • DROP OWNED
  • DROP POLICY
  • DROP PROCEDURE
  • DROP PROFILE
  • DROP PUBLICATION
  • DROP QHBSECUREROLE
  • DROP ROLE
  • DROP ROUTINE
  • DROP RULE
  • DROP SCHEMA
  • DROP SEQUENCE
  • DROP SERVER
  • DROP STATISTICS
  • DROP SUBSCRIPTION
  • DROP TABLE
  • DROP TABLESPACE
  • DROP TEXT SEARCH CONFIGURATION
  • DROP TEXT SEARCH DICTIONARY
  • DROP TEXT SEARCH PARSER
  • DROP TEXT SEARCH TEMPLATE
  • DROP TRANSFORM
  • DROP TRIGGER
  • DROP TYPE
  • DROP USER
  • DROP USER MAPPING
  • DROP VIEW
  • END
  • EXECUTE
  • EXPLAIN
  • FETCH
  • GRANT
  • IMPORT FOREIGN SCHEMA
  • INSERT
  • LISTEN
  • LOAD
  • LOCK
  • MERGE
  • MOVE
  • NOTIFY
  • PREPARE
  • PREPARE TRANSACTION
  • REASSIGN OWNED
  • REFRESH MATERIALIZED VIEW
  • REINDEX
  • RELEASE SAVEPOINT
  • RESET
  • REVOKE
  • ROLLBACK
  • ROLLBACK PREPARED
  • ROLLBACK TO SAVEPOINT
  • SAVEPOINT
  • SECURITY LABEL
  • SELECT
  • SELECT INTO
  • SET
  • SET CONSTRAINTS
  • SET ROLE
  • SET SESSION AUTHORIZATION
  • SET TRANSACTION
  • SHOW
  • START TRANSACTION
  • TRUNCATE
  • UNLISTEN
  • UPDATE
  • VACUUM
  • VALUES

• SQL Standard Support

  • SQL Key Words
  • QHB Error Codes
  • Date/Time Support
  • SQL Conformance