Vertica provides the standard SQL type BOOLEAN, which has two states: true and false. The third state in SQL boolean logic is unknown, which is represented by the NULL value.

Syntax

BOOLEAN

Semantics

Valid literal data values for input are:

 

TRUE	't'	'true'	'y'	'yes'	'1'
FALSE	'f'	'false'	'n'	'no'	'0'

Notes

• Do not confuse the BOOLEAN data type with Boolean Operators or the Boolean-predicate.

  The Boolean predicate retrieves rows where the value of an expression is true, false, or unknown (null).

  Syntax

  expression IS [NOT] TRUE

  expression IS [NOT] FALSE

  expression IS [NOT] UNKNOWN

  Semantics

  A Boolean predicate always return true or false, never a null value, even when the operand is null. A null input is treated as the value UNKNOWN.

  Notes

  • Do not confuse the boolean-predicate with Boolean Operators or the Boolean data type, which can have only two values: true and false.

    Syntax

    [ AND | OR | NOT ]

    Semantics

    SQL uses a three-valued Boolean logic where the null value represents "unknown."

     

    a	b	a AND b	a OR b
    TRUE	TRUE	TRUE	TRUE
    TRUE	FALSE	FALSE	TRUE
    TRUE	NULL	NULL	TRUE
    FALSE	FALSE	FALSE	FALSE
    FALSE	NULL	FALSE	NULL
    NULL	NULL	NULL	NULL

     

    a	NOT a
    TRUE	FALSE
    FALSE	TRUE
    NULL	NULL

    Notes

    • The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
    • Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
  • IS UNKNOWN and IS NOT UNKNOWN are effectively the same as the NULL-predicate, except that the input expression does not have to be a single column value. To check a single column value for NULL, use the NULL-predicate.

  Syntax

  [ AND | OR | NOT ]

  Semantics

  SQL uses a three-valued Boolean logic where the null value represents "unknown."

   

  a	b	a AND b	a OR b
  TRUE	TRUE	TRUE	TRUE
  TRUE	FALSE	FALSE	TRUE
  TRUE	NULL	NULL	TRUE
  FALSE	FALSE	FALSE	FALSE
  FALSE	NULL	FALSE	NULL
  NULL	NULL	NULL	NULL

   

  a	NOT a
  TRUE	FALSE
  FALSE	TRUE
  NULL	NULL

  Notes

  • The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
  • Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
• The keywords TRUE and FALSE are preferred (and SQL-compliant).
• All other values must be enclosed in single quotes.
• Boolean values are output using the letters t and f.

The CHARACTER type is a fixed-length, blank padded string.

Syntax

[ CHARACTER | CHAR ] (n)

Semantics

 

n	specifies the length of the string. The default length is one (1). The maximum length is 4000.

Notes

• On input, strings are truncated if necessary to the specified number of characters.
• Remember to include the extra bytes required for multibyte characters in the column width declaration.
• String literals in SQL statements must be enclosed in single quotes.
• Vertica does not support the PostgreSQL TEXT type. Use VARCHAR instead.
• Due to Vertica's use of compression, the cost of over-estimating the length of
  these fields is incurred mostly at load time and during sorts.
• CHAR fields are padded on the right with spaces as needed.
• No embedded NULs are allowed.
• In CHAR fields NUL characters are handled as ordinary characters.
• NULL appears last (largest) in ascending order.

The CHARACTER VARYING type is a variable-length string.

Syntax

[ CHARACTER VARYING | VARCHAR ] (n)

Semantics

 

n	specifies the maximum length of the string. The default length is 80. The maximum length is 4000.

Notes

• On input, strings are truncated if necessary to the specified number of characters.
• Remember to include the extra bytes required for multibyte characters in the column width declaration.
• String literals in SQL statements must be enclosed in single quotes.
• Vertica does not support the PostgreSQL TEXT type. Use VARCHAR instead.
• Due to Vertica's use of compression, the cost of over-estimating the length of
  these fields is incurred mostly at load time and during sorts.
• VARCHAR values terminate at their first NUL byte if any.
• A VARCHAR(n) field is processed internally as a NUL-padded string of maximum length n.
• No embedded NULs are allowed.
• NULL appears last (largest) in ascending order.

 

Vertica supports the full set of SQL date and time types. In most cases, a combination of DATE, TIME, TIMESTAMP WITHOUT TIME ZONE, and TIMESTAMP WITH TIME ZONE should provide a complete range of date/time functionality required by any application. However, in compliance with the SQL standard, Vertica also supports the TIME WITH TIMEZONE data type.

Notes

• Vertica uses Julian dates for all date/time calculations. They can correctly predict and calculate any date more recent than 4713 BC to far into the future, based on the assumption that the length of the year is 365.2425 days.
• All date/time types are stored in eight bits.
• A date/time value of NULL appears first (smallest) in ascending order.
• All the date/time data types accept the special literal value NOW to specify the current date and time. For example:

  SELECT TIMESTAMP 'NOW';

Vertica supports the numeric data type DOUBLE PRECISION, which is the IEEE-754 8-byte floating point type along with most of the usual floating point operations.

Syntax

[ DOUBLE PRECISION | FLOAT | FLOAT8 ]

Semantics

On a machine whose floating-point arithmetic does not follow IEEE 754, these values probably do not work as expected.

Double precision is an inexact, variable-precision numeric type. In other words, some values cannot be represented exactly and are stored as approximations. Thus, input and output operations involving double precision may show slight discrepancies.

• For exact numeric storage and calculations (money for example), use INTEGER.
• Floating point calculations depend on the behavior of the underlying processor, operating system, and compiler.
• Comparing two floating-point values for equality may or may not work as expected.

Values

COPY accepts floating-point data in the following format:

1. optional leading white space
2. an optional plus ("+") or minus sign ("-")
3. a decimal number, a hexadecimal number, an infinity, a NAN, or a null value

A decimal number consists of a non-empty sequence of decimal digits possibly containing a radix character (decimal point, locale dependent, usually "."), optionally followed by a decimal exponent. A decimal exponent consists of an "E" or "e", followed by an optional plus or minus sign, followed by a non-empty sequence of decimal digits, and indicates multiplication by a power of 10.

A hexadecimal number consists of a "0x" or "0X" followed by a non-empty sequence of hexadecimal digits possibly containing a radix character, optionally followed by a binary exponent. A binary exponent consists of a "P" or "p", followed by an optional plus or minus sign, followed by a non-empty sequence of decimal digits, and indicates multiplication by a power of 2. At least one of radix character and binary exponent must be present.

An infinity is either "INF" or "INFINITY", disregarding case.

A NAN (Not A Number) is "NAN" (disregarding case) optionally followed by a sequence of characters enclosed in parentheses. The character string specifies the value of NAN in an implementation-dependent manner. (The Vertica internal representation of NAN is 0xfff8000000000000LL on x86 machines.)

When writing infinity or NAN values as constants in a SQL statement, enclose them in single quotes. For example:

UPDATE table SET x = 'Infinity'

 

Note: Vertica follows the IEEE definition of NaNs (IEEE 754). (The SQL standards do not specify how floating point works in detail.) IEEE defines NaNs as a set of floating point values where each one is not equal to anything, even to itself. 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. However, for the purpose of sorting data, NaN values must be placed somewhere in the result. The value generated 'NaN' appears in the context of a floating point number matches the NaN value generated by the hardware. For example, Intel hardware generates (0xfff8000000000000LL), which is technically a Negative, Quiet, Non-signaling NaN. Vertica uses a different NaN value to represent floating point NULL (0x7ffffffffffffffeLL). This is a Positive, Quiet, Non-signaling NaN and is reserved by Vertica.

The load file format of a null value is user defined, as described in the COPY command. The Vertica internal representation of a null value is 0x7fffffffffffffffLL. The interactive format is controlled by the vsql printing option null. For example:

\pset null '(null)'

The default option is not to print anything.

Rules

• -0 == +0
• 1/0 = Infinity
• 0/0 == Nan
• NaN != anything (even NaN)

To search for NaN column values, use the following predicate:

... WHERE column != column

This is necessary because WHERE column = 'Nan' cannot be true by definition.

Sort Order (Ascending)

• NaN
• -Inf
• numbers
• +Inf
• Null

Notes

• Vertica does not support REAL (FLOAT4) or NUMERIC.
• NULL appears last (largest) in ascending order.

Vertica supports the numeric data type INTEGER, a signed eight-byte (64-bit) data type.

Syntax

[ INTEGER | INT | BIGINT ]

Semantics

The difference between integer and bigint is in the way that vsql performs input and output. INTEGER is handled as a 32-bit data type and BIGINT is handled as a 64-bit data type.

Notes

The JDBC type INTEGER is 4-bytes, and is not supported by Vertica; use BIGINT instead.

• The range of values is -2^63+1 to 2^63-1.
• 2^63 = 9,223,372,036,854,775,808 (19 digits).
• The value -2^63 is reserved to represent NULL.
• Vertica does not support the SQL/JDBC types NUMERIC, SMALLINT, or TINYINT.
• Vertica does not check for overflow (positive or negative) except in the aggregate function SUM(). If you encounter overflow when using SUM, use SUM_FLOAT() which converts to floating point.
• NULL appears first (smallest) in ascending order.
• Vertica does not have an explicit four-byte (32-bit integer) type. Vertica's encoding and compression automatically eliminate extra space.

Adds a referential integrity constraint to the metadata of a column. See Adding Join Constraints in the Database Administrator's Guide.

Syntax

[ CONSTRAINT constraint-name ]

{ [ NOT ] NULL

| PRIMARY KEY

| REFERENCES table-name [ ( column-name ) ]

[ DEFAULT constant ]

}

Semantics

 

CONSTRAINT constraint-name	optionally assigns a name to the constraint. Vertica recommends that you name all constraints.
NULL	(default) specifies that the column is allowed to contain null values.
NOT NULL	specifies that the column must receive a value during INSERT and UPDATE operations. If no DEFAULT value is specified and no value is provided, the INSERT or UPDATE statement returns an error because no default value exists.
PRIMARY KEY	adds a referential integrity constraint defining the column as the primary key. Referential integrity in Vertica consists of a set of constraints (logical schema objects) that define primary key and foreign key columns. In a star schema or snowflake schema: Each dimension table must have a PRIMARY KEY constraint. The fact table must contain columns that can be used to join the fact table to dimension tables. Fact table join columns must have FOREIGN KEY constraints in order to participate in pre-join projections. Outer join queries produce expected results only when the fact table join column used in the query does not have a FOREIGN KEY constraint.
REFERENCES	adds a referential integrity constraint defining the column as a foreign key. If column is omitted, the default is the primary key of table.
table-name	specifies the table to which the REFERENCES constraint applies.
column-name	specifies the column to which the REFERENCES constraint applies. If column is omitted, the default is the primary key of table-name.
DEFAULT constant	specifies a constant to be used by default in any INSERT operation that does not specify a value for the column. The data type of the constant must match the data type of the column. If no default value is specified, the default is null.

Notes

• You must define primary key and foreign key constraints in all tables that participate in joins. See Adding Constraints.
• You must specify NOT NULL constraints on columns that will be given PRIMARY and REFERENCES constraints.
• Vertica does not support expressions in the DEFAULT clause.

Referential integrity in Vertica consists of a set of constraints (logical schema objects) that define primary key and foreign key columns. In a star schema or snowflake schema:

• Each dimension table must have a PRIMARY KEY constraint.
• The fact table must contain columns that can be used to join the fact table to dimension tables.
• Fact table join columns must have FOREIGN KEY constraints in order to participate in pre-join projections.
• Outer join queries produce expected results only when the fact table join column used in the query does not have a FOREIGN KEY constraint.

Referential integrity in Vertica consists of a set of constraints (logical schema objects) that define primary key and foreign key columns. In a star schema or snowflake schema:

• Each dimension table must have a PRIMARY KEY constraint.
• The fact table must contain columns that can be used to join the fact table to dimension tables.
• Fact table join columns must have FOREIGN KEY constraints in order to participate in pre-join projections.
• Outer join queries produce expected results only when the fact table join column used in the query does not have a FOREIGN KEY constraint.

A functional dependency is a relationship between two sets of column values in a table where one column value can determine the other. It represents knowledge of the data that cannot otherwise be expressed in the logical schema. In Vertica functional dependencies are expressed as CORRELATION constraints and are used by the Database Designer to produce more efficient physical schema designs.