ROW

Represents structured data (structs) in external tables in the Parquet format only. Rows must use only primitive types and nested rows. Rows may not contain other complex types.

For more information, see Reading Structs as Inline Types.

Syntax

In column definitions:

ROW(field type[, ...])

In literals:

ROW(value[, ...])

Row Input Format for Column Definition

In a column definition in an external table, a ROW consists of one or more comma-separated pairs of field names and types. This syntax is similar to that for columns in table definitions. The following example defines a ROW to represent an address struct in the data.

=> CREATE EXTERNAL TABLE customers (name VARCHAR, 
	address ROW(street VARCHAR, city VARCHAR, zipcode INT))
    AS COPY FROM '...' PARQUET;

ROWs can be nested; a field can have a type of ROW:

=> CREATE EXTERNAL TABLE employees(
    employeeID INT, 
    personal ROW(
      name VARCHAR, 
      address ROW(street VARCHAR, city VARCHAR, zipcode INT),
      taxID INT), 
    department VARCHAR)
  AS COPY FROM '...' PARQUET;

The primitive types in the table definition must match those in the data. By default the ROW structure must also match; a ROW must contain all and only the fields in the struct in the data. You can violate the ROW schema match by passing skip_strong_schema_match='true' in the PARQUET clause.

ROW columns have several restrictions:

  • The maximum nesting depth is 10.
  • The maximum number of total columns and fields in a table is 1600.  The ROW itself is not counted, just its fields.
  • ROW columns cannot use any constraints (such as NOT NULL) or defaults.
  • ROW fields cannot be auto_increment or setof.
  • A ROW definition must include at least one field.
  • "Row" is a reserved keyword within a ROW definition, but is permitted as the name of a table or column.
  • ROW columns cannot be modified using ALTER TABLE...ALTER COLUMN. You must drop and recreate the external table to change a ROW column.
  • External tables containing ROW columns cannot also contain identity, auto-increment, or sequence columns.

Row Input Format for Literals

In a literal, such as a value in a comparison operation, a ROW consists of one or more values.   Omit field names; Vertica generates them automatically. If you do not coerce types, Vertica infers the types from the data values.

=> SELECT ROW('Amy',2,false);
                    row
--------------------------------------------
 {"field0":"Amy","field1":2,"field2":false}
(1 row)

You can coerce types explicitly:

=> SELECT ROW('Amy',2.5::int,false::varchar);
                   row
------------------------------------------
 {"field0":"Amy","field1":3,"field2":"f"}
(1 row)			

Supported Operators and Predicates

The ROW type supports the following query operators and predicates:

  • INNER and OUTER JOIN
  • Comparison, IN, BETWEEN (non-nullable filters only)
  • IS NULL, IS NOT NULL
  • CASE (only in outer SELECT)
  • GROUP BY, ORDER BY
  • SELECT DISTINCT

The following operators and predicates are not supported for ROW columns:

  • Math operators
  • Type coercion
  • BITWISE, LIKE
  • MLA (ROLLUP, CUBE, GROUPING SETS)
  • Aggregate functions including MAX, MIN, and SUM
  • Set operators including UNION, UNION ALL, MINUS, and INTERSECT
  • Subqueries and views with structs in SELECT output; however, you can include individual fields using rowname.fieldname notation

In comparison operations (including implicit comparisons like ORDER BY), a ROW is treated as the sequence of its field values.  For example, the following two statements are equivalent:

GROUP BY ROW(zipcode, city)
GROUP BY zipcode, city				

NULL Inputs

A ROW in which each field is null is equivalent to a null ROW:

=> SELECT ROW(null, null, null) IS NULL;
 ?column?
----------
 t
(1 row)				

Row Output Format

ROW values read from external tables are output in JSON format as in the following example.

=> CREATE EXTERNAL TABLE customers (name VARCHAR, 
	address ROW(street VARCHAR, city VARCHAR, zipcode INT))
    AS COPY FROM '...' PARQUET;

=> SELECT address FROM customers WHERE address.city ='Pasadena';
                              address
--------------------------------------------------------------------
 {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001}
 {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
 {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001}
 {"street":"15 Raymond Dr","city":"Pasadena","zipcode":91003}
(4 rows)

The following table specifies the mappings from Vertica data types to JSON data types.

Vertica Type JSON Type
Integer Integer
Float Numeric
Numeric Numeric
Boolean Boolean
All others String

Escape single quotes in literal inputs using single quotes, as in the following example:

=> SELECT ROW('Howard''s house',2,false);
                          row
-------------------------------------------------------
 {"field0":"Howard's house","field1":2,"field2":false}
(1 row)