Lexical Structure#

SQL input contains a sequence of commands. A command contains a sequence of tokens, terminated by a semicolon ; (or end of input stream). Tokens are usually separated by whitespace (space, tab, newline).

A token can be a:

  • key word

  • identifier

  • quoted identifier

  • literal / constant

  • special character symbol

Comments not tokens (treated like whitespace).

1. Identifiers and key words#

Key words have a fixed meaning in the SQL language, e.g. SELECT, UPDATE.

Identifiers are names of tables, columns, or other database objects; depending on the command they are used in.

Identifiers and key words:

  • Must begin with a letter or underscore. Subsequent characters can be letters, underscores, digits(0-9) or $ (non-standard).

  • Should be less than 63 bytes by default, or they’ll be truncated (NAMEDATALEN defaults to 64, and the limit is NAMEDATALEN - 1)

  • Are case insensitive, except for delimited / quoted identifiers(enclosed in ""; can include spaces, ampersands(&) and more).

    • In SQL, unquoted identifiers are folded to uppercase. In PostgreSQL they’re folded to lowercase.

    • A convention often used is to write key words in upper case and names in lower case.

      SELECT col_name FROM table_name;

Note

A delimited identifier is always an identifier e.g. "select" is a name but select is a key word.

2. Constants#

2.1 String constants#

Arbitrary sequences of characters bounded by single quotes '...' e.g. ‘Hello world!’.

To include a single-quote character within a string constant, write two adjacent single quotes:

mydb=> SELECT 'Jane''s book';
  ?column?   
-------------
 Jane's book
(1 row)

Two string constants that are only separated by whitespace and at least one newline are concatenated.

mydb=> SELECT 'some'
mydb-> 'text';
 ?column? 
----------
 sometext
(1 row)

2.2 String constants with C-style escapes#

PostgreSQL extension. Specified by e'...' or E'...'. \ begins a C-like backslash escape sequence:

Backslash Escape Sequence

Interpretation

\b

backspace

\f

form feed

\n

newline

\r

carriage return

\t

tab

\o, \oo, \ooo (o = 0–7)

octal byte value

\xh, \xhh (h = 0–9, A–F)

hexadecimal byte value

\uxxxx, \Uxxxxxxxx (x = 0–9, A–F)

16 or 32-bit hexadecimal Unicode character value

 
mydb=> SELECT e'some\trandom'
mydb-> 'text\n\nthere';  -- e'' required only in first line
      ?column?      
--------------------
 some    randomtext+
                   +
 there
(1 row)

2.3 String constants with Unicode escapes#

PostgreSQL extension. Specified by u&'...' or U&'...'. Allows specifying arbitrary Unicode characters by code point (4-digit or 6-digit hexadecimal, prefixed with \ or \+ respectively).

mydb=> SELECT U&'d\0061t\+000061';
 ?column? 
----------
 data
(1 row)

2.4 Dollar-quoted string constants#

PostgreSQL extension. Specified by $$...$$ or $optional_tag$...$optional_tag$. The optional tag is case-sensitive, and can be nested by choosing a different tag at each nesting level.

Contents are taken literally (no escapes), enhancing readability and eliminating the need to double escape characters.

mydb=> SELECT $$Jane's book$$;  -- equivalent to SELECT 'Jane''s book';
  ?column?   
-------------
 Jane's book
(1 row)

Particularly useful in function definitions in PostgreSQL.

2.5 Bit-string constants#

Binary notation only allows 0 and 1 e.g. B'101, b'111'. Hexadecimal notation is preceeded by x or X e.g. x'abc'.

2.6 Numeric constants#

General forms:

digits                          123456789
digits.[digits][e[+-]digits]    123.45678e-9
[digits].digits[e[+-]digits]    .78e9
digitse[+-]digits               1234e+56

  • At least one digit must be before or after the decimal point, if one is used.

  • At least one digit must follow the exponent marker (e), if one is present.

  • Any leading + or - is not part of the constant; it is an operator applied to the constant.

In most cases, a numeric constant will be automatically coerced to the most appropriate type depending on context.

mydb=> SELECT -123.456e-7;
   ?column?    
---------------
 -0.0000123456
(1 row)

2.7 Constants of other types#

A constant of an arbitrary type can be entered using any one of the following notations:

  • CAST ( 'string' AS type )

    mydb=> SELECT CAST (b'110' AS int); -- Standard SQL
int4 
------
    6
(1 row)

  • type 'string'

    mydb=> SELECT interval '1 decade';
interval 
----------
10 years
(1 row)

  • 'string'::type

    mydb=> SELECT '123'::numeric(5,2);  -- historical PostgreSQL
numeric 
---------
  123.00
(1 row)

3. Operators#

+ - * / < > = ~ ! @ # % ^ & | ` ?

Restrictions:

  • -- and /* cannot appear anywhere in an operator name: interpreted as start of comment.

  • multiple-character operator names cannot end in + or - unless they also contain at least one of

    ~ ! @ # % ^ & | ` ?

    e.g. @- is valid, but *- is not.

    This restriction allows PostgreSQL to parse SQL-compliant queries without requiring spaces between tokens.

  • When working with non-SQL-standard operator names, separate adjacent operators with spaces to avoid ambiguity.

4. Special characters#

  • $

    • if followed by digits e.g. $1, represents a positional parameter in the body of a function definition or a prepared statement

    • can be part of an identifier or a dollar-quoted string constant.

  • ()

    • groups expressions and enforces precedence

    • is required as part of the fixed syntax of particular SQL commands.

  • [] selects elements of an array.

  • , separates the elements of a list.

  • ; terminates SQL commands.

  • :

    • selects “slices” from arrays

    • is used in certain SQL dialects(such as Embedded SQL) to prefix variable names.

  • *

    • in some contexts denotes all the fields of a table row or composite value

    • in aggregate functions, specifies that the aggregate does not require any explicit parameter.

  • .

    • is used in numeric constants

    • separates schema, table, and column names.

5. Comments#

A comment is a sequence of characters beginning with double dashes and extending to the end of the line e.g.

-- A standard SQL comment

C-style block comments are also allowed:

/* Multi-line comment 
* with nesting: /* nested block comment */
*/;

Comments are removed from the input stream before further syntax analysis, and are effectively replaced by whitespace.

6. Operator precedence#

Operator/Element

Associativity

Description

.

left

table/column name separator

::

left

PostgreSQL-style typecast

[]

left

array element selection

+ -

right

unary plus, unary minus

^

left

exponentiation

* / %

left

multiplication, division, modulo

+ -

left

addition, subtraction

(any other operator)

left

all other native and user-defined operators

BETWEEN IN LIKE ILIKE SIMILAR

range containment, set membership, string matching

< > = <= >= <>

comparison operators

IS ISNULL NOTNULL

IS TRUE, IS FALSE, IS NULL, IS DISTINCT FROM, etc

NOT

right

logical negation

AND

left

logical conjunction

OR

left

logical disjunction

Note

Operator precedence rules above apply to user-defined operators that have the same names as the built-in operators: a + defined for some custom type will have similar precedence to the built in +.

When a schema-qualified operator name is used in the OPERATOR syntax, the OPERATOR construct is always taken to have the default precedence for any other operator in the table above.

mydb=> SELECT 3 * 2 + 4;
 ?column? 
----------
       10
(1 row)

mydb=> SELECT 3 OPERATOR(pg_catalog.*) 2 + 4;  -- * has lower precedence
 ?column? 
----------
       18
(1 row)