Literals

• 1: Number-type literals
• 2: String literals
• 2.1: Character string literals
• 2.2: Dollar-quoted string literals
• 2.3: Unicode string literals
• 2.4: VARBINARY string literals
• 2.5: Extended string literals
• 3: Date/time literals
• 3.1: Time zone values
• 3.2: Day of the week names
• 3.3: Month names
• 3.4: Interval literal
• 3.4.1: Interval subtype units
• 3.4.2: Interval qualifier

1 - Number-type literals

Vertica supports three types of numbers: integers, numerics, and floats.

• Integers are whole numbers less than 2^63 and must be digits.

• Numerics are whole numbers larger than 2^63 or that include a decimal point with a precision and a scale. Numerics can contain exponents. Numbers that begin with 0x are hexadecimal numerics.

Numeric-type values can also be generated using casts from character strings. This is a more general syntax. See the Examples section below, as well as Data type coercion operators (CAST).

Syntax

digits
digits.[digits] | [digits].digits
digits e[+-]digits | [digits].digits e[+-]digits | digits.[digits] e[+-]digits

Parameters

digits

One or more numeric characters, 0 through 9

e

Exponent marker

Notes

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

• There cannot be any spaces or other characters embedded in the constant.

• Leading plus (+) or minus (–) signs are not considered part of the constant; they are unary operators applied to the constant.

• In most cases a numeric-type constant is automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it as described in Data type coercion operators (CAST).

• Floating point literals are not supported. If you specifically need to specify a float, you can cast as described in Data type coercion operators (CAST).

• Vertica follows the IEEE specification for floating point, including NaN (not a number) and Infinity (Inf).

• A NaN is not greater than and at the same time not less than anything, even itself. In other words, comparisons always return false whenever a NaN is involved.

• Dividing INTEGERS (x / y) yields a NUMERIC result. You can use the // operator to truncate the result to a whole number.

Examples

The following are examples of number-type literals:

42
3.5
4.
.001
5e2
1.925e-3

Scientific notation:

=> SELECT NUMERIC '1e10';
 ?column?
-------------
 10000000000
(1 row)

BINARY scaling:

=> SELECT NUMERIC '1p10';
 ?column?
----------
     1024
(1 row)
=> SELECT FLOAT 'Infinity';
  ?column?
----------
 Infinity
(1 row)

The following examples illustrated using the / and // operators to divide integers:

=> SELECT 40/25;
 ?column?
----------------------
 1.600000000000000000
(1 row)
=> SELECT 40//25;
 ?column?
----------
        1
(1 row)

See also

2 - String literals

String literals are string values surrounded by single or double quotes. Double-quoted strings are subject to the backslash, but single-quoted strings do not require a backslash, except for \' and \\.

You can embed single quotes and backslashes into single-quoted strings.

To include other backslash (escape) sequences, such as \t (tab), you must use the double-quoted form.

Precede single-quoted strings with a space between the string and its preceding word, since single quotes are allowed in identifiers.

See also

• SET STANDARD_CONFORMING_STRINGS
• SET ESCAPE_STRING_WARNING
• Internationalization parameters
• Implement locales for international data sets

2.1 - Character string literals

Character string literals are a sequence of characters from a predefined character set, enclosed by single quotes.

Syntax

'character-seq'

Parameters

character-seq

Arbitrary sequence of characters

Embedded single quotes

If a character string literal includes a single quote, it must be doubled. For example:

=> SELECT 'Chester''s gorilla';
  ?column?
-------------------
Chester's gorilla
(1 row)

Standard-conforming strings and escape characters

Vertica uses standard-conforming strings as specified in the SQL standard, so backslashes are treated as string literals and not escape characters.

Examples

=> SELECT 'This is a string';
  ?column?
------------------
 This is a string
(1 row)
=> SELECT 'This \is a string';
   WARNING:  nonstandard use of escape in a string literal at character 8
   HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
     ?column?
------------------
 This is a string
(1 row)
vmartdb=> SELECT E'This \is a string';
     ?column?
------------------
 This is a string
=> SELECT E'This is a \n new line';
       ?column?
----------------------
 This is a
 new line
(1 row)
=> SELECT 'String''s characters';
      ?column?
--------------------
 String's characters
(1 row)

See also

• SET STANDARD_CONFORMING_STRINGS
• SET ESCAPE_STRING_WARNING
• Implement locales for international data sets

2.2 - Dollar-quoted string literals

Dollar-quoted string literals are rarely used, but are provided here for your convenience.

The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax.

Syntax

$$characters$$

Parameters

characters

Arbitrary sequence of characters bounded by paired dollar signs ($$)

Dollar-quoted string content is treated as a literal. Single quote, backslash, and dollar sign characters have no special meaning within a dollar-quoted string.

Notes

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

Examples

=> SELECT $$Fred's\n car$$;
     ?column?
-------------------
 Fred's\n car
(1 row)

=> SELECT 'SELECT 'fact';';
ERROR:  syntax error at or near "';'" at character 21
LINE 1: SELECT 'SELECT 'fact';';

=> SELECT 'SELECT $$fact';$$;
   ?column?
---------------
 SELECT $$fact
(1 row)

=> SELECT 'SELECT ''fact'';';
    ?column?
----------------
 SELECT 'fact';
(1 row)

2.3 - Unicode string literals

Syntax

U&'characters' [ UESCAPE '<Unicode escape character>' ]

Parameters

characters

Arbitrary sequence of UTF-8 characters bounded by single quotes (')

Unicode escape character

A single character from the source language character set other than a hexit, plus sign (+), quote ('), double quote (''), or white space

Using standard conforming strings

With StandardConformingStrings enabled, Vertica supports SQL standard Unicode character string literals (the character set is UTF-8 only).

Before you enter a Unicode character string literal, enable standard conforming strings in one of the following ways.

• To enable for all sessions, update the configuration parameter StandardConformingStrings.

• To treat backslashes as escape characters for the current session, use the SET STANDARD_CONFORMING_STRINGS statement.

See also Extended String Literals.

Examples

To enter a Unicode character in hexadecimal, such as the Russian phrase for "thank you, use the following syntax:

=> SET STANDARD_CONFORMING_STRINGS TO ON;
=> SELECT U&'\0441\043F\0430\0441\0438\0431\043E' as 'thank you';
 thank you
-----------
 спасибо
(1 row)

To enter the German word mude (where u is really u-umlaut) in hexadecimal:

=> SELECT U&'m\00fcde';
?column?
----------
müde
(1 row)
=> SELECT 'ü';
?column?
----------
ü
(1 row)

To enter the LINEAR B IDEOGRAM B240 WHEELED CHARIOT in hexadecimal:

=> SELECT E'\xF0\x90\x83\x8C';
?column?
----------
(wheeled chariot character)
(1 row)

See also

• SET STANDARD_CONFORMING_STRINGS
• SET ESCAPE_STRING_WARNING
• Internationalization parameters
• Implement locales for international data sets

2.4 - VARBINARY string literals

VARBINARY string literals allow you to specify hexadecimal or binary digits in a string literal.

Syntax

X''
B''

Parameters

X or x

Specifies hexadecimal digits. The <hexadecimal digits> string must be enclosed in single quotes (').

B or b

Specifies binary digits. The <binary digits> string must be enclosed in single quotes (').

Examples

=> SELECT X'abcd';
 ?column?
----------
 \253\315
(1 row)

=> SELECT B'101100';
 ?column?
----------
 ,
(1 row)

2.5 - Extended string literals

Syntax

E'characters'

Parameters

characters

Arbitrary sequence of characters bounded by single quotes (') You can use C-style backslash sequence in extended string literals, which are an extension to the SQL standard. You specify an extended string literal by writing the letter E as a prefix (before the opening single quote); for example:

E'extended character string\n'

Within an extended string, the backslash character (\) starts a C-style backslash sequence, in which the combination of backslash and following character or numbers represent a special byte value, as shown in the following list. Any other character following a backslash is taken literally; for example, to include a backslash character, write two backslashes (\\).

• \\ is a backslash

• \b is a backspace

• \f is a form feed

• \n is a newline

• \r is a carriage return

• \t is a tab

• \x##,where ## is a 1 or 2-digit hexadecimal number; for example \x07 is a tab

• \###, where ### is a 1, 2, or 3-digit octal number representing a byte with the corresponding code.

When an extended string literal is concatenated across lines, write only E before the first opening quote:

=> SELECT E'first part o'
    'f a long line';
         ?column?
---------------------------
 first part of a long line
(1 row)

Two adjacent single quotes are used as one single quote:

=> SELECT 'Aren''t string literals fun?';
          ?column?
-----------------------------
 Aren't string literals fun?
(1 row)

Standard conforming strings and escape characters

When interpreting commands, such as those entered in vsql or in queries passed via JDBC or ODBC, Vertica uses standard conforming strings as specified in the SQL standard. In standard conforming strings, backslashes are treated as string literals (ordinary characters), not escape characters.

• To treat back slashes as escape characters, set configuration parameter StandardConformingStrings to 0.

• To enable standard conforming strings permanently, set the StandardConformingStrings parameter to '1', as described below.

• To enable standard conforming strings per session, use SET STANDARD_CONFORMING_STRING TO ON, which treats backslashes as escape characters for the current session only.

Identifying strings that are not standard conforming

The following procedure can be used to identify nonstandard conforming strings in your application so that you can convert them into standard conforming strings:

1. Be sure the StandardConformingStrings parameter is off, as described in Internationalization parameters.

   => ALTER DATABASE DEFAULT SET StandardConformingStrings = 0;
   

   Note

   Vertica recommends that you migrate your application to use standard conforming strings .

2. If necessary, turn on the EscapeStringWarning parameter.

   => ALTER DATABASE DEFAULT SET EscapeStringWarning = 1;
   

   Vertica now returns a warning each time it encounters an escape string within a string literal. For example, Vertica interprets the \n in the following example as a new line:

   => SELECT 'a\nb';
      WARNING:  nonstandard use of escape in a string literal at character 8
      HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
    ?column?
   ----------
    a
   b
   (1 row)
   

   When StandardConformingStrings is ON, the string is interpreted as four characters: a \ n b.

   Modify each string that Vertica flags by extending it as in the following example:

   E'a\nb'
   

   Or if the string has quoted single quotes, double them; for example, 'one'' double'.

3. Turn on the StandardConformingStrings parameter for all sessions:

   => ALTER DATABASE DEFAULT SET StandardConformingStrings = 1;
   

Doubled single quotes

This section discusses vsql inputs that are not passed on to the server. Vertica recognizes two consecutive single quotes within a string literal as one single quote character. For example, the following inputs, 'You''re here!' ignored the second consecutive quote and returns the following:

=> SELECT 'You''re here!';
   ?column?
--------------
 You're here!at
(1 row)

This is the SQL standard representation and is preferred over the form, 'You\'re here!', because backslashes are not parsed as before. You need to escape the backslash:

=> SELECT (E'You\'re here!');
   ?column?
--------------
 You're here!
(1 row)

This behavior change introduces a potential incompatibility in the use of the vsql meta-command \set, which automatically concatenates its arguments. For example:

\set file  '\''  'pwd'  '/file.txt'  '\''\echo :file

vsql takes the four arguments and outputs the following:

'/home/vertica/file.txt'

Vertica parses the adjacent single quotes as follows:

\set file  '\'''pwd''/file.txt''\''\echo :file
'/home/vertica/file.txt''

Note the extra single quote at the end. This is due to the pair of adjacent single quotes together with the backslash-quoted single quote.

The extra quote can be resolved either as in the first example above, or by combining the literals as follows:

\set file '\''`pwd`'/file.txt'''\echo :file
'/home/vertica/file.txt'

In either case the backslash-quoted single quotes should be changed to doubled single quotes as follows:

\set file '''' `pwd` '/file.txt'''

Additional examples

=> SELECT 'This \is a string';
     ?column?
------------------
 This \is a string
(1 row)

=> SELECT E'This \is a string';
     ?column?
------------------
 This is a string

=> SELECT E'This is a \n new line';
       ?column?
----------------------
 This is a
 new line
(1 row)

=> SELECT 'String''s characters';
      ?column?
--------------------
 String's characters
(1 row)

3 - Date/time literals

Date or time literal input must be enclosed in single quotes. Input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others.

Vertica handles date/time input more flexibly than the SQL standard requires. The exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones are described in Date/time expressions.

3.1 - Time zone values

Vertica attempts to be compatible with the SQL standard definitions for time zones. However, the SQL standard has an odd mix of date and time types and capabilities. Obvious problems are:

• Although the DATE type does not have an associated time zone, the TIME type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries.

• Vertica assumes your local time zone for any data type containing only date or time.

• The default time zone is specified as a constant numeric offset from UTC. It is therefore not possible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.

To address these difficulties, OpenText recommends using Date/Time types that contain both date and time when you use time zones. OpenText recommends that you do not use the type TIME WITH TIME ZONE, even though it is supported it for legacy applications and for compliance with the SQL standard.

Time zones and time-zone conventions are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900's, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules.

Vertica currently supports daylight-savings rules over the time period 1902 through 2038, corresponding to the full range of conventional UNIX system time. Times outside that range are taken to be in "standard time" for the selected time zone, no matter what part of the year in which they occur.

3.2 - Day of the week names

The following tokens are recognized as names of days of the week:

3.3 - Month names

The following tokens are recognized as names of months:

3.4 - Interval literal

A literal that represents a time span.

Syntax

[ @ ] [-] { quantity subtype-unit }[...] [ AGO ]

Parameters

@

Ignored

- (minus)

Specifies a negative interval value.

quantity

Integer numeric constant

subtype-unit

See Interval subtype units for valid values. Subtype units must be specified for year-month intervals; they are optional for day-time intervals.

AGO

Specifies a negative interval value. AGO and - (minus) are synonymous.

Notes

• The amounts of different units are implicitly added up with appropriate sign accounting.

• The boundaries of an interval constant are:

  • 9223372036854775807 usec to -9223372036854775807 usec

  • 296533 years 3 mons 21 days 04:00:54.775807 to -296533 years -3 mons -21 days -04:00:54.775807

• The range of an interval constant is +/– 263 – 1 microseconds.

• In Vertica, interval fields are additive and accept large floating-point numbers.

Examples

See Specifying interval input.

3.4.1 - Interval subtype units

The following tables lists subtype units that you can specify in an interval literal, divided into major categories:

• Year-month subtype units

• Day-time subtype units

Year-month subtype units

Day-time subtype units

Processing m input

Vertica uses context to interpret the input unit m as months or minutes. For example, the following command creates a one-column table with an interval value:

=> CREATE TABLE int_test(i INTERVAL YEAR TO MONTH);

Given the following INSERT statement, Vertica interprets the interval literal 1y 6m as 1 year 6 months:

=> INSERT INTO int_test VALUES('1y 6m');
 OUTPUT
--------
      1
(1 row)
=> COMMIT;
COMMIT
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT * FROM int_test;
        i
-----------------
 1 year 6 months
(1 row)

The following ALTER TABLE statement adds a DAY TO MINUTE interval column to table int_test:

=>  ALTER TABLE int_test ADD COLUMN x INTERVAL DAY TO MINUTE;
ALTER TABLE

The next INSERT statement sets the first and second columns to 3y 20m and 1y 6m, respectively. In this case, Vertica interprets the m input literals in two ways:

• For column i, Vertica interprets the m input as months, and displays 4 years 8 months.

• For column x, Vertica interprets the m input as minutes. Because the interval is defined as DAY TO MINUTE, it converts the inserted input value 1y 6m to 365 days 6 minutes:

=> INSERT INTO int_test VALUES ('3y 20m', '1y 6m');
 OUTPUT
--------
      1
(1 row)

=> SELECT * FROM int_test;
        i         |        x
------------------+-----------------
 1 year 6 months  |
 4 years 8 months | 365 days 6 mins
(2 rows)

3.4.2 - Interval qualifier

Specifies how to interpret and format an interval literal for output, and optionally sets precision. Interval qualifiers are composed of one or two units:

unit [ TO unit ] [ (p) ]

where:

• unit specifies a day-time or year-month subtype.

• p specifies precision, an integer between 0 and 6.

  Note

  Precision only applies to SECOND units, specifying the number of decimal digits to show after the seconds value decimal point. The default precision for SECOND is 6.

  When SECOND is the second unit of a qualifier—for example, DAY TO SECOND or MINUTE TO SECOND—it has a precision of 2 places before the decimal point.

  For example:

  => SELECT INTERVAL '6 122.538987' MINUTE TO SECOND (5);
    ?column?
  -------------
   08:02.53899
  (1 row)
  

  For details, see Specifying interval precision.

If an interval omits an interval qualifier, the default is DAY TO SECOND(6).

Interval qualifiers are divided into two categories:

• day-time

• year-month

Day-time interval qualifiers

Year-month interval qualifiers

YEAR

Unconstrained

MONTH

Months within year

YEAR TO MONTH

Span of years and months

=> SELECT INTERVALYM '1 2';
 ?column?
----------
 1-2
(1 row)
=> SELECT INTERVAL '1 2' YEAR TO MONTH;
?column?
----------
1-2
(1 row)

Examples

See Controlling interval format.