SQL Syntax (Part 03)
Tags: apacheage, postgres, bitnine, devcommunity
Published: 2023-09-13 18:41:16

## Dollar-Quoted String Constants

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

Although dollar quoting is not supported by the SQL standard, it is frequently more practical than using the single quote syntax to construct complex string literals. It is very helpful for encoding string constants inside of other constants, which is frequently required in the definitions of procedural functions. Each backslash in the above example would have to be expressed using single-quote syntax as four backslashes, which would be reduced to two backslashes in the first parsing of the string constant and then to one in the subsequent parsing of the inner string constant during function execution.

## Bit-String Constants
Bit-string constants resemble conventional string constants but lack the whitespace between the beginning quotation and the B (in upper or lower case), for example, B'1001'. 

Bitstring constants can only contain the letters 0 and 1.
Bit-string constants can also be written in hexadecimal notation with a leading X (in upper- or lower-case), for example, X'1FF'. Each hexadecimal digit in this notation corresponds to a bit-string constant of four binary digits.
Similar to conventional string constants, both types of bit-string constants can be carried across lines.
A bit-string constant cannot employ dollar quoting.

## Numeric Constants
If the value of a numeric constant that lacks either a decimal point or an exponent fits within the type integer (32 bits), the constant is originally assumed to be of type integer. If the value falls within the type bigint (64 bits), the constant is assumed to be of type numeric. The initial assumption is that any constants with exponents and/or decimal points are of type numerical.

The type resolution methods only use the data type that was first allocated to a numeric constant as a starting point. Most of the time, the constant will be automatically forced to the best type based on the situation. When required, you may use casting to compel a numeric value to be interpreted as a certain data type.

## Special Characters
Other than being an operator, certain non-alphanumeric characters have a specific purpose.
You may get further information on the usage in the section where the relevant syntactic element is discussed. This section is merely included to inform readers of the characters' existence and to summarise their goals.
launching it.

- In the body of a function definition or a prepared statement, a positional parameter is denoted by a dollar sign ($), followed by digits. The dollar symbol can also be used as an identifier or as a dollar-quoted string constant in other places.

- The purpose of brackets (()) is typically to group expressions and establish precedence. Sometimes a certain SQL command has a set syntax that includes brackets.

- In various syntactical constructions, commas (,) are employed to demarcate the components of a list.

- An SQL command comes to an end with a semicolon (;). It can only exist within a string constant or quoted identifier in a command; otherwise, it cannot appear elsewhere.

- In order to choose "slices" from arrays, use the colon (:). Refer to Section 8.15. The colon is used to prefix variable names in some SQL dialects (such as Embedded SQL).

- In some situations, the asterisk (*) is used to indicate every field of a table row or composite value. When used as the argument of an aggregate function, it also has a specific meaning, indicating that the aggregate does not need any explicit parameters.

- Numeric constants and the names of schema, tables, and columns are separated by a period (.).