Firebird 2.5 Language Reference

1 Firebird Language ReferenceRelease FilippovAlexander KarpeykinAlexey KovyazinDmitry KuzmenkoDenis SimonovPaul VinkenoogDmitry Yemanov11 August 2017, document version Language ReferenceRelease August 2017, document version Dmitry Filippov, Alexander Karpeykin, Alexey Kovyazin, Dmitry Kuzmenko, Denis Simonov, Paul Vinkenoog, andDmitry YemanovThe source of much copied Reference material: Paul VinkenoogCopyright 2017 Firebird Project and all contributing authors, under the Public Documentation License Version refer to the License Notice in the volume represents a compilation of topics concerning Firebird 's SQL Language written by members of the Rus-sian-speaking community of Firebird developers and users.

2 In 2014, it culminated in a Language Reference manual, in Rus-sian. At the instigation of Alexey Kovyazin, a campaign was launched amongst Firebird users world-wide to raise fundsto pay for a professional translation into English, from which translations into other languages would proceed under theauspices of the Firebird Documentation of Contents1. About the Firebird SQL Language Reference .. 1 Subject Matter .. 1 Authorship .. 1 Language Reference Updates .. 1 Gestation of the Big Book .. 2 Contributors .. 2 Acknowledgments.

3 32. SQL Language Structure .. 5 Background to Firebird 's SQL Language .. 5 SQL Flavours .. 5 SQL Dialects .. 6 Error Conditions .. 7 Basic Elements: Statements, Clauses, Keywords .. 7 Identifiers .. 8 Literals .. 9 Operators and Special Characters .. 9 Comments .. 103. Data Types and Subtypes .. 12 Integer Data Types .. 14 SMALLINT .. 14 INTEGER .. 14 BIGINT .. 14 Hexadecimal Format for Integer Numbers .. 15 Floating-Point Data Types .. 15 FLOAT.

4 16 DOUBLE PRECISION .. 16 Fixed-Point Data Types .. 16 NUMERIC .. 17 DECIMAL .. 17 Data Types for Dates and Times .. 17 DATE .. 18 TIME .. 19 TIMESTAMP .. 19 Operations Using Date and Time Values .. 19 Character Data Types .. 20 Unicode .. 21 Client Character Set .. 21 Special Character Sets .. 21 Collation Sequence .. 21 Character Indexes .. 23 Character Types in Detail .. 24 Binary Data Types .. 25 BLOB Subtypes .. 25 BLOB Specifics.

5 26 ARRAY Type .. 26 Special Data Types .. 28 SQL_NULL Data Type .. 28 Conversion of Data Types .. 29 Firebird Language ReferencevExplicit Data Type Conversion .. 29 Implicit Data Type Conversion .. 33 Custom Data Types Domains .. 35 Domain Attributes .. 35 Domain Override .. 35 Creating and Administering Domains .. 364. Common Language Elements .. 38 Expressions .. 38 Constants .. 39 SQL Operators .. 42 Conditional Expressions .. 45 NULL in Expressions.

6 46 Subqueries .. 47 Predicates .. 49 Assertions .. 49 Comparison Predicates .. 49 Existential Predicates .. 60 Quantified Subquery Predicates .. 645. Data Definition (DDL) Statements .. 66 DATABASE .. 66 CREATE DATABASE .. 66 ALTER DATABASE .. 70 DROP DATABASE .. 73 SHADOW .. 73 CREATE SHADOW .. 74 DROP SHADOW .. 75 DOMAIN .. 76 CREATE DOMAIN .. 76 ALTER DOMAIN .. 81 DROP DOMAIN .. 84 TABLE .. 85 CREATE TABLE .. 85 ALTER TABLE.

7 98 DROP TABLE .. 105 RECREATE TABLE .. 105 INDEX .. 106 CREATE INDEX .. 106 ALTER INDEX .. 110 DROP INDEX .. 111 SET STATISTICS .. 112 VIEW .. 113 CREATE VIEW .. 113 ALTER VIEW .. 116 CREATE OR ALTER VIEW .. 117 DROP VIEW .. 118 RECREATE VIEW .. 119 TRIGGER .. 120 CREATE TRIGGER .. 120 ALTER TRIGGER .. 126 CREATE OR ALTER TRIGGER .. 128 DROP TRIGGER .. 129 RECREATE TRIGGER .. 129 Firebird Language ReferenceviPROCEDURE .. 130 CREATE PROCEDURE.

8 130 ALTER PROCEDURE .. 135 CREATE OR ALTER PROCEDURE .. 138 DROP PROCEDURE .. 138 RECREATE PROCEDURE .. 139 EXTERNAL FUNCTION .. 140 DECLARE EXTERNAL FUNCTION .. 140 ALTER EXTERNAL FUNCTION .. 143 DROP EXTERNAL FUNCTION .. 144 FILTER .. 145 DECLARE FILTER .. 145 DROP FILTER .. 147 SEQUENCE (GENERATOR) .. 147 CREATE SEQUENCE .. 148 ALTER SEQUENCE .. 149 SET GENERATOR .. 150 DROP SEQUENCE .. 150 EXCEPTION .. 151 CREATE EXCEPTION .. 151 ALTER EXCEPTION.

9 152 CREATE OR ALTER EXCEPTION .. 153 DROP EXCEPTION .. 154 RECREATE EXCEPTION .. 155 COLLATION .. 155 CREATE COLLATION .. 155 DROP COLLATION .. 158 CHARACTER SET .. 159 ALTER CHARACTER SET .. 159 ROLE .. 160 CREATE ROLE .. 160 ALTER ROLE .. 161 DROP ROLE .. 161 COMMENTS .. 162 COMMENT ON .. 1626. Data Manipulation (DML) Statements .. 164 SELECT .. 164 FIRST, SKIP .. 165 The SELECT Columns List .. 167 The FROM clause .. 170 Joins.

10 176 The WHERE clause .. 184 The GROUP BY clause .. 186 The PLAN clause .. 191 UNION .. 195 ORDER BY .. 196 ROWS .. 200 FOR UPDATE [OF] .. 202 WITH LOCK .. 203 INTO .. 206 Common Table Expressions ( WITH .. AS .. SELECT ) .. 207 INSERT .. 211 Firebird Language ReferenceviiINSERT .. VALUES .. 212 INSERT .. SELECT .. 212 INSERT .. DEFAULT VALUES .. 213 The RETURNING clause .. 214 Inserting into BLOB columns .. 215 UPDATE .. 215 Using an alias.