FirebirdSQL logo
 About the Firebird 5.0 Language ReferenceData Types and Subtypes 

This reference describes the SQL language supported by Firebird.

Background to Firebird’s SQL Language

To begin, a few points about some characteristics of Firebird’s SQL implementation.

SQL Flavours

Distinct subsets of SQL apply to different areas of activity. The SQL subsets in Firebird’s language implementation are:

DSQL

Dynamic SQL
PSQL

Procedural SQL
ESQL

Embedded SQL
ISQL

Interactive SQL

Dynamic SQL is the major part of the language which corresponds to Part 2 (SQL/Foundation) of the SQL specification. DSQL represents statements passed by client applications through the public Firebird API and processed by the database engine.

Procedural SQL augments Dynamic SQL to allow compound statements containing local variables, assignments, conditions, loops and other procedural constructs. PSQL corresponds to Part 4 (SQL/PSM) of the SQL specifications. PSQL extensions are available in persistent stored modules (procedures, functions and triggers), and in Dynamic SQL as well (see EXECUTE BLOCK).

Embedded SQL is the SQL subset supported by Firebird gpre, the application which allows you to embed SQL constructs into your host programming language (C, C++, Pascal, Cobol, etc.) and preprocess those embedded constructs into the proper Firebird API calls.

Note

Only a subset of the statements and expressions implemented in DSQL are supported in ESQL.

Interactive ISQL refers to the language that can be executed using Firebird isql, the command-line application for accessing databases interactively. As a regular client application, its native language is DSQL. It also offers a few additional commands that are not available outside its specific environment.

Both DSQL and PSQL subsets are completely presented in this reference. Neither ESQL nor ISQL flavours are described here unless mentioned explicitly.

For ISQL, consult the manual Firebird Interactive SQL Utility.

SQL Dialects

SQL dialect is a term that defines the specific features of the SQL language that are available when accessing a database. SQL dialects can be defined at the database level and specified at the connection level. Three dialects are available:

  • Dialect 1 is intended solely to allow backward compatibility with legacy databases from old InterBase versions, version 5 and below. A “Dialect 1” database retains certain language features that differ from Dialect 3, the default for Firebird databases.

    • Date and time information are stored in a DATE data type. A TIMESTAMP data type is also available, that is identical to this DATE implementation.

    • Double quotes may be used as an alternative to apostrophes for delimiting string data. This is contrary to the SQL standard — double quotes are reserved for a distinct syntactic purpose both in standard SQL and in Dialect 3. Double-quoting strings is therefore to be avoided.

    • The precision for NUMERIC and DECIMAL data types is smaller than in Dialect 3 and, if the precision of a fixed decimal number is greater than 9, Firebird stores it internally as a double-precision floating point value.

    • The BIGINT (64-bit integer) data type is not supported.

    • Identifiers are case-insensitive and must always comply with the rules for regular identifiers — see the section [fblangref50-structure-identifiers] below.

    • Although generator values are stored as 64-bit integers, a Dialect 1 client request, SELECT GEN_ID (MyGen, 1), for example, will return the generator value truncated to 32 bits.

  • Dialect 2 is available only on a Firebird client connection and cannot be set in a database. It is intended to assist debugging of possible problems with legacy data when migrating a database from dialect 1 to 3.

  • In Dialect 3 databases,

    • numbers (DECIMAL and NUMERIC data types) are stored as fixed-point values (scaled integers) for all precisions; depending on the type and precision, they are stored as a SMALLINT, INTEGER, BIGINT or INT128.

    • The TIME data type is available for storing time-of-day data.

    • The DATE data type stores only date information.

    • The TIMESTAMP data type stores date and time information.

    • The 64-bit integer data type BIGINT is available.

    • Double quotes are reserved for delimiting non-regular identifiers, enabling object names that are case-sensitive or that do not meet the requirements for regular identifiers in other ways.

    • Strings must be delimited with single quotes (apostrophes) or using Q-strings.

    • Generator values are stored as 64-bit integers.

Important

Use of Dialect 3 is strongly recommended for newly developed databases and applications. Both database and connection dialects should match, except under migration conditions with Dialect 2.

This reference describes the semantics of SQL Dialect 3 unless specified otherwise.

Error Conditions

Processing of every SQL statement either completes successfully or fails due to a specific error condition. Error handling can be done both client-side in the application, or server-side using PSQL.

Basic Elements: Statements, Clauses, Keywords

The primary construct in SQL is the statement. A statement defines what the database management system should do with a particular data or metadata object. More complex statements contain simpler constructs — clauses and options.

Clauses

A clause defines a certain type of directive in a statement. For instance, the WHERE clause in a SELECT statement and in other data manipulation statements (e.g. UPDATE, DELETE) specifies criteria for searching one or more tables for the rows that are to be selected, updated or deleted. The ORDER BY clause specifies how the output data — result set — should be sorted.

Options

Options, being the simplest constructs, are specified in association with specific keywords to provide qualification for clause elements. Where alternative options are available, it is usual for one of them to be the default, used if nothing is specified for that option. For instance, the SELECT statement will return all rows that match the search criteria unless the DISTINCT option restricts the output to non-duplicated rows.

Keywords

All words that are included in the SQL lexicon are keywords. Some keywords are reserved, meaning their usage as identifiers for database objects, parameter names or variables is prohibited in some or all contexts. Non-reserved keywords can be used as identifiers, although this is not recommended. From time to time, non-reserved keywords may become reserved, or new (reserved or non-reserved) keywords may be added when new language feature are introduced. Although unlikely, reserved words may also change to non-reserved keywords, or keywords may be removed entirely, for example when parser rules change.

For example, the following statement will be executed without errors because, although ABS is a keyword, it is not a reserved word.

CREATE TABLE T (ABS INT NOT NULL);

On the contrary, the following statement will return an error because ADD is both a keyword and a reserved word.

CREATE TABLE T (ADD INT NOT NULL);

Refer to the list of reserved words and keywords in the chapter Reserved Words and Keywords.

Identifiers

All database objects have names, often called identifiers. The maximum identifier length is 63 characters character set UTF8 (252 bytes).

Note

It is possible to restrict the actual maximum identifier length through configuration. Consult the Firebird 4.0 Release Notes for details. In this language reference we assume the default configuration of 63 characters (252 bytes).

Two types of names are valid as identifiers: regular identifiers, similar to variable names in regular programming languages, and delimited identifiers that are specific to SQL. To be valid, each type of identifier must conform to a set of rules, as follows:

Rules for Regular Identifiers

  • Length cannot exceed 63 characters

  • The name must start with an unaccented, 7-bit ASCII alphabetic character. It may be followed by other 7-bit ASCII letters, digits, underscores or dollar signs. No other characters, including spaces, are valid. The name is case-insensitive, meaning it can be declared and used in either upper or lower case. Thus, from the system’s point of view, the following names are the same:

    fullname
FULLNAME
FuLlNaMe
FullName

  • Regular identifiers are stored in uppercase

Regular name syntax
<name> ::=
  <letter> | <name><letter> | <name><digit> | <name>_ | <name>$

<letter> ::= <upper letter> | <lower letter>

<upper letter> ::= A | B | C | D | E | F | G | H | I | J | K | L | M |
                   N | O | P | Q | R | S | T | U | V | W | X | Y | Z

<lower letter> ::= a | b | c | d | e | f | g | h | i | j | k | l | m |
                   n | o | p | q | r | s | t | u | v | w | x | y | z

<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

Rules for Delimited Identifiers

  • Length cannot exceed 63 characters in character set UTF8 (252 bytes). Identifiers are stored in character set UTF8, which means characters outside the ASCII range are stored using 2 to 4 bytes.

  • The entire string must be enclosed in double-quotes, e.g. "anIdentifier"

  • A double-quote in an identifier can be escaped by doubling it, e.g. "with""doublequote"; we recommend avoiding double-quotes in delimited identifiers.

  • It may contain any character from the UTF8 character set, including accented characters, spaces and special characters

  • An identifier can be a reserved word

  • Delimited identifiers are stored as-is and are case-sensitive in all contexts

  • Trailing spaces in delimited identifiers are removed, as with any string constant

  • Delimited identifiers are available in Dialect 3 only. For more details on dialects, see [fblangref50-structure-dialects]

Delimited identifier syntax
<delimited identifier> ::= "<permitted character>[<permitted character> ...]"
Note

A delimited identifier such as "FULLNAME" is the same as the regular identifiers FULLNAME, fullname, FullName, and so on. The reason is that Firebird stores regular identifiers in upper case, regardless of how they were defined or declared. Delimited identifiers are always stored according to the exact case of their definition or declaration. Thus, "FullName" (quoted, or delimited) is different from FullName (unquoted, or regular) which is stored as FULLNAME in the metadata.

Literals

Literals are used to directly represent values in a statement. Examples of standard types of literals are:

integer        - 0, -34, 45, 0X080000000;
fixed-point    - 0.0, -3.14
floating-point - 3.23e-23;
string         - 'text', 'don''t!', Q'{don't!}';
binary string  - x'48656C6C6F20776F726C64'
date           - DATE '2018-01-19';
time           - TIME '15:12:56';
timestamp      - TIMESTAMP '2018-01-19 13:32:02';
boolean        - true, false, unknown
null state     - null

Details about literals for each data type are discussed in section Literals (Constants) of chapter Common Language Elements.

Operators and Special Characters

A set of special characters is reserved for use as operators or separators.

<special char> ::=
  any of <space> or "%&'()*+,-./:;<=>?|^{}

Some of these characters, alone or in combination, may be used as operators (arithmetical, string, logical), as SQL command separators, to quote identifiers, or to mark the limits of string literals or comments.

Operator Syntax
<operator> ::=
    <string concatenation operator>
  | <arithmetic operator>
  | <comparison operator>
  | <logical operator>

<string concatentation operator> ::= '||'

<arithmetic operator> ::= * | / | + | -

<comparison operator> ::=
    =  | <> | != | ~= | ^= | > | < | >= | <=
  | !> | ~> | ^> | !< | ~< | ^<

<logical operator> ::= NOT | AND | OR

For more details on operators, see Expressions.

Comments

Comments may be present in SQL scripts, SQL statements and PSQL modules. A comment can be any text, usually used to document how particular parts of the code work. The parser ignores the text of comments.

Firebird supports two types of comments: block (or bracketed) and in-line (or simple).

Syntax
<comment> ::= <block comment> | <single-line comment>

<block comment> ::=
  /* <character>[<character> ...] */

<single-line comment> ::=
  -- <character>[<character> ...]<end line>

Block comments start with the /* character pair and end with the */ character pair. Text in block comments may be of any length and can occupy multiple lines.

In-line comments start with a pair of hyphen characters, -- and continue until the first linebreak (end of line).

Example
CREATE PROCEDURE P(APARAM INT)
  RETURNS (B INT)
AS
BEGIN
  /* This text will be ignored during the execution of the statement
     since it is a comment
  */
  B = A + 1; -- In-line comment
  SUSPEND;
END