Tokens

Tomasz Bogusz

What Are Tokens in PostgreSQL?

A Postgres SQL command is a programmer's means to communicate to Postgres what they want the Postgres database to effect.

An SQL command is built using tokens terminated with a semicolon ;.

SELECT first_name FROM users WHERE id = 42;

A token can be:

  • a key word (SELECT, FROM, WHERE),

  • an identifier (first_name, users, id),

  • a constant, aka literal (42),

  • an operator (=), or

  • a special character (;).

Key Words

In SQL a key word is a word with fixed meaning used for denoting fixed command parts such as SELECT, INSERT, ANALYZE, DISTINCT , INTO, JOIN, SET, IS, NULL, NOT, AND.

Key words cannot be quoted and are case insensitive however a convention is to use uppercase.

Identifiers

Identifiers are names dedicated to denoting database objects such as:

  • roles,

  • databases,

  • tables, and

  • columns.

Generally, an identifier should not coincide with an SQL key word such as SELECT or UPDATE but Postgres does not enforce it.

There are two types of identifiers in Postgres:

  • unquoted identifiers, and

  • quoted identifiers.

Unquoted identifiers must begin with:

  • a letter, or

  • an underscore (_).

Subsequent characters of an unquoted identifier can include letters, digits and underscores.

In Postgres unquoted identifiers are case insensitive and are always folded to lower case. This is not congruent with the SQL Standard which directs to fold to uppercase.

Quoted identifiers (aka delimited identifiers) are identifiers enclosed in double quotes ("...") and are case sensitive. A quoted word in an SQL command is always an identifier not a key word. Quoted identifiers can include almost any characters even spaces.

Constants

In Postgres there are the following types of implicitly typed constants:

  • strings - a sequence of characters most often enclosed in single quotes or using other manners like E (escape string constants), U& (unicode escape string constants), $$ (non SQL standard dollar quoted escape string constants) when "escape" capabilities are needed,

  • bit strings - binary strings denoted with binary notation (e.g. B'101010') or hexadecimal notation (e.g. X'2A'),

  • numbers - e.g. 42, -42, 4.2, 4., .2, 4e2 (numeric constants are often coerced to the context applicable type).

In addition, Postgres allows for user specifying explicitly typed constants.

Operators

An operator is a set of specific non-alphanumeric characters which often denotes an action to be performed between some constants.

The notable operators are:

  • =, >, <, >=, <= (comparisons), and

  • +, -, *, /, %, ^ (mathematical operations).

Special Characters

Special characters are non-alphanumeric characters that denote a special meaning within a Postgres SQL command, such as for example:

  • . - specifying database objects within other database objects (e.g. a column within a table - users.first_name),

  • ; - terminating an SQL command,

  • () - grouping and precedence, and

  • * - all columns.