Ensure that the byte sequences that you create, especially when using the octalor hexadecimal escapes, compose valid characters in the server character setencoding. A useful alternative is to use Unicode escapes or the alternativeUnicode escape syntax; then the server will check that the character conversionis possible.

Note that Spanner supports only UTF-8 for strings.

The character with the code zero cannot be in a string constant.

String Constants With Unicode Escapes

PostgreSQL also supports another type of escape syntax for strings thatallows specifying arbitrary Unicode characters by code point. A Unicode escapestring constant starts with U& (upper or lower case letter U followed byampersand) immediately before the opening quote, without any spaces in between,for example U&'foo'. (Note that this creates an ambiguity with the operator &.Use spaces around the operator to avoid this problem.) Inside the quotes,Unicode characters can be specified in escaped form by writing a backslashfollowed by the four-digit hexadecimal code point number or alternatively abackslash followed by a plus sign followed by a six-digit hexadecimal code pointnumber. For example, the string 'data' could be written as the following:

U&'d\0061t\+000061'

The following less trivial example writes the Russian word "slon" (elephant) inCyrillic letters:

U&'\0441\043B\043E\043D'

If you want to use an escape character other than backslash, you can specify itusing theUESCAPE clause after the string, for example:

U&'d!0061t!+000061' UESCAPE '!'

The escape character can be any single character other than a hexadecimal digit,the plus sign, a single quote, a double quote, or a whitespace character.

To include the escape character in the string literally, write it twice.

Dollar-Quoted String Constants

Although the standard syntax for specifying string constants is usuallyconvenient, it can be difficult to understand when the string contains manysingle quotes or backslashes, because each of those must be doubled. To allowmore readable queries in such situations, PostgreSQL provides anotherway, called "dollar quoting", to write string constants. A dollar-quoted stringconstant consists of a dollar sign ($), an optional "tag" of zero or morecharacters, another dollar sign, an arbitrary sequence of characters that makesup the string content, a dollar sign, the same tag that began this dollar quote,and a dollar sign. For example, here are two different ways to specify thestring "Dianne's horse" using dollar quoting:

$$Dianne's horse$$$SomeTag$Dianne's horse$SomeTag$

Notice that inside the dollar-quoted string, single quotes can be used withoutneeding to be escaped. Indeed, no characters inside a dollar-quoted string areever escaped: the string content is always written literally. Backslashes arenot special, and neither are dollar signs, unless they are part of a sequencematching the opening tag.

It's possible to nest dollar-quoted string constants by choosing different tagsat each nesting level.

The tag, if any, of a dollar-quoted string follows the same rules as an unquotedidentifier, except that it cannot contain a dollar sign. Tags are casesensitive, so$tag$String content$tag$ is correct, but$TAG$Stringcontent$tag$ is not.

A dollar-quoted string that follows a keyword or identifier must be separatedfrom it by whitespace; otherwise the dollar quoting delimiter would be taken aspart of the preceding identifier.

Dollar quoting is not part of the SQL standard, but it is often a moreconvenient way to write complicated string literals than the standard-compliantsingle quote syntax. It is particularly useful when representing stringconstants inside other constants.

Numeric Constants

Numeric constants are accepted in these general forms:

digitsdigits.[digits][e[+-]digits][digits].digits[e[+-]digits]digitse[+-]digits

where digits is one or more decimal digits (0 through 9). At least one digitmust be before or after the decimal point, if one is used. At least one digitmust follow the exponent marker (e), if one is present. There cannot be anyspaces or other characters embedded in the constant. Note that any leading plusor minus sign is not actually considered part of the constant; it is an operatorapplied to the constant.

These are some examples of valid numeric constants:

423.54..0015e21.925e-3

A numeric constant that contains neither a decimal point nor an exponent isinitially presumed to be type bigint if its value fits in type bigint (64 bits);otherwise it is taken to be type numeric. Constants that contain decimal pointsor exponents are always initially presumed to be type numeric.

The initially assigned data type of a numeric constant is just a starting pointfor the type resolution algorithms. In most cases the constant will beautomatically coerced to the most appropriate type depending on context. Whennecessary, you can force a numeric value to be interpreted as a specific datatype by casting it. For example, you can force a bigint value to be treated astype numeric by writing:

NUMERIC '123'123::numeric

These are actually just special cases of the general casting notations discussedin the next section.

Constants Of Other Types

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

type 'string''string'::typeCAST ( 'string' AS type )

The string constant's text is passed to the input conversion routine for thetype calledtype. The result is a constant of the indicated type. The explicittype cast can be omitted if there is no ambiguity as to the type the constantmust be (for example, when it is assigned directly to a table column), in whichcase it is automatically coerced.

The string constant can be written using either regular SQL notation ordollar-quoting.

The:: andCAST() syntaxes can also be used to specify run-time typeconversions of arbitrary expressions. To avoid syntactic ambiguity, thetype'string' syntax can only be used to specify the type of a literal constant.

Another restriction on thetype 'string' syntax is that it does not work forarray types; use:: orCAST() to specify the type of an array constant.

TheCAST() syntax conforms to SQL. Thetype 'string' syntax is ageneralization of the standard: SQL specifies this syntax only for a few datatypes, but PostgreSQL allows it for all types. The syntax with:: ishistorical PostgreSQL usage.

Special characters

Some characters that are not alphanumeric have a special meaning that isdifferent from being an operator. Details on the usage can be found at thelocation where the respective syntax element is described. This section onlyexists to advise the existence and summarize the purposes of these characters.

• A dollar sign ($) followed by digits is used to represent a positionalparameter in the body of a prepared statement. In other contexts the dollarsign can be part of an identifier or a dollar-quoted string constant.

• Parentheses (()) have their usual meaning to group expressions and enforceprecedence. In some cases parentheses are required as part of the fixedsyntax of a particular SQL command.

• Brackets ([]) are used to select the elements of an array.

• Commas (,) are used in some syntactical constructs to separate the elementsof a list.

• The semicolon (;) terminates a SQL command. It cannot appear anywherewithin a command, except within a string constant or quoted identifier.

• The asterisk (*) is used in some contexts to denote all the fields of atable row or composite value. It also has a special meaning when used as theargument of an aggregate function, namely that the aggregate does notrequire any explicit parameter.

• The period (.) is used in numeric constants, and to separate table, andcolumn names.

Comments

A comment is a sequence of characters beginning with double dashes and extendingto the end of the line. For example:

-- This is a standard SQL comment

Alternatively, C-style block comments can be used:

/* multiline comment *   with nesting: /* nested block comment */ */

where the comment begins with /* and extends to the matching occurrence of */.These block comments nest, as specified in the SQL standard but unlike C, sothat one can comment out larger blocks of code that might contain existing blockcomments.

A comment is removed from the input stream before further syntax analysis and iseffectively replaced by whitespace. The exception is a comment that containshints, which are preserved for interpretation by the query planner.