An end-of-file marker, not a real token

A keyword (like SELECT) or an optionally quoted SQL identifier

An unsigned numeric literal

A character that could not be tokenized

Single quoted string: i.e: ‘string’

Double quoted string: i.e: “string”

Triple single quoted strings: Example ‘’‘abc’‘’ BigQuery

Triple double quoted strings: Example “”“abc”“” BigQuery

Dollar quoted string: i.e: $$string$$ or $tag_name$string$tag_name$

Byte string literal: i.e: b’string’ or B’string’ (note that some backends, such as PostgreSQL, may treat this syntax as a bit string literal instead, i.e: b’10010101’)

Byte string literal: i.e: b“string“ or B“string“

Triple single quoted literal with byte string prefix. Example B'''abc''' BigQuery

Triple double quoted literal with byte string prefix. Example B"""abc""" BigQuery

Single quoted literal with raw string prefix. Example R'abc' BigQuery

Double quoted literal with raw string prefix. Example R"abc" BigQuery

Triple single quoted literal with raw string prefix. Example R'''abc''' BigQuery

Triple double quoted literal with raw string prefix. Example R"""abc""" BigQuery

“National” string literal: i.e: N’string’

Quote delimited literal. Examples Q'{ab'c}', Q'|ab'c|', Q'|ab|c|' Oracle

“Nationa” quote delimited literal. Examples NQ'{ab'c}', NQ'|ab'c|', NQ'|ab|c|' Oracle

“escaped” string literal, which are an extension to the SQL standard: i.e: e’first \n second’ or E ‘first \n second’

Unicode string literal: i.e: U&‘first \000A second’

Hexadecimal string literal: i.e.: X’deadbeef’

Comma

Whitespace (space, tab, etc)

Double equals sign ==

Equality operator =

Not Equals operator <> (or != in some dialects)

Less Than operator <

Greater Than operator >

Less Than Or Equals operator <=

Greater Than Or Equals operator >=

Spaceship operator <=>

Plus operator +

Minus operator -

Multiplication operator *

Division operator /

Integer division operator // in DuckDB

Modulo Operator %

String concatenation ||

Left parenthesis (

Right parenthesis )

Period (used for compound identifiers or projections into nested types)

Colon :

DoubleColon :: (used for casting in PostgreSQL)

Assignment := (used for keyword argument in DuckDB macros and some functions, and for variable declarations in DuckDB and Snowflake)

SemiColon ; used as separator for COPY and payload

Backslash \ used in terminating the COPY payload with \.

Left bracket [

Right bracket ]

Ampersand &

Pipe |

Caret ^

Left brace {

Right brace }

Right Arrow =>

Sharp # used for PostgreSQL Bitwise XOR operator, also PostgreSQL/Redshift geometrical unary/binary operator (Number of points in path or polygon/Intersection)

## PostgreSQL/Redshift geometrical binary operator (Point of closest proximity)

Tilde ~ used for PostgreSQL Bitwise NOT operator or case sensitive match regular expression operator

~* , a case insensitive match regular expression operator in PostgreSQL

!~ , a case sensitive not match regular expression operator in PostgreSQL

!~* , a case insensitive not match regular expression operator in PostgreSQL

~~, a case sensitive match pattern operator in PostgreSQL

~~*, a case insensitive match pattern operator in PostgreSQL

!~~, a case sensitive not match pattern operator in PostgreSQL

!~~*, a case insensitive not match pattern operator in PostgreSQL

<<, a bitwise shift left operator in PostgreSQL

>>, a bitwise shift right operator in PostgreSQL

&&, an overlap operator in PostgreSQL

Exclamation Mark ! used for PostgreSQL factorial operator

Double Exclamation Mark !! used for PostgreSQL prefix factorial operator

AtSign @ used for PostgreSQL abs operator, also PostgreSQL/Redshift geometrical unary/binary operator (Center, Contained or on)

^@, a “starts with” string operator in PostgreSQL

|/, a square root math operator in PostgreSQL

||/, a cube root math operator in PostgreSQL

? or $ , a prepared statement arg placeholder

->, used as a operator to extract json field in PostgreSQL

->>, used as a operator to extract json field as text in PostgreSQL

#>, extracts JSON sub-object at the specified path

@-@ PostgreSQL/Redshift geometrical unary operator (Length or circumference)

?- PostgreSQL/Redshift geometrical unary/binary operator (Is horizontal?/Are horizontally aligned?)

&< PostgreSQL/Redshift geometrical binary operator (Overlaps to left?)

&> PostgreSQL/Redshift geometrical binary operator (Overlaps to right?)`

&<| PostgreSQL/Redshift geometrical binary operator (Does not extend above?)`

|&> PostgreSQL/Redshift geometrical binary operator (Does not extend below?)`

<-> PostgreSQL/Redshift geometrical binary operator (Distance between)

<^ PostgreSQL/Redshift geometrical binary operator (Is below?)

>^ PostgreSQL/Redshift geometrical binary operator (Is above?)

?# PostgreSQL/Redshift geometrical binary operator (Intersects or overlaps)

?-| PostgreSQL/Redshift geometrical binary operator (Is perpendicular?)

?|| PostgreSQL/Redshift geometrical binary operator (Are parallel?)

~= PostgreSQL/Redshift geometrical binary operator (Same as)

`<<| PostgreSQL/Redshift geometrical binary operator (Is strictly below?)

`|>> PostgreSQL/Redshift geometrical binary operator (Is strictly above?)

`|> BigQuery pipe operator

#>>, extracts JSON sub-object at the specified path as text

jsonb @> jsonb -> boolean: Test whether left json contains the right json

jsonb <@ jsonb -> boolean: Test whether right json contains the left json

jsonb #- text[] -> jsonb: Deletes the field or array element at the specified path, where path elements can be either field keys or array indexes.

jsonb @? jsonpath -> boolean: Does JSON path return any item for the specified JSON value?

jsonb @@ jsonpath → boolean: Returns the result of a JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then NULL is returned.

jsonb ? text -> boolean: Checks whether the string exists as a top-level key within the jsonb object

jsonb ?& text[] -> boolean: Check whether all members of the text array exist as top-level keys within the jsonb object

jsonb ?| text[] -> boolean: Check whether any member of the text array exists as top-level keys within the jsonb object

Custom binary operator This is used to represent any custom binary operator that is not part of the SQL standard. PostgreSQL allows defining custom binary operators using CREATE OPERATOR.