ROW

Represents structured data (structs).

Represents structured data (structs). A ROW can contain fields of any primitive or complex type supported by Vertica.

Syntax

In column definitions:

ROW([field] type[, ...])

If the field name is omitted, Vertica generates names starting with "f0".

In literals:

ROW(value [AS field] [, ...]) [AS name(field[, ...])]

Syntax for column definition

In a column definition, a ROW consists of one or more comma-separated pairs of field names and types. In the following example, the Parquet data file contains a struct for the address, which is read as a ROW in an external table:

=> 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 TABLE employees(
    employeeID INT,
    personal ROW(
      name VARCHAR,
      address ROW(street VARCHAR, city VARCHAR, zipcode INT),
      taxID INT),
    department VARCHAR);

ROWs can contain arrays:

=> CREATE TABLE customers(
  name VARCHAR,
  contact ROW(
    street VARCHAR,
    city VARCHAR,
    zipcode INT,
    email ARRAY[VARCHAR]
  ),
  accountid INT );

When loading data, the primitive types in the table definition must match those in the data. The ROW structure must also match; a ROW must contain all and only the fields in the struct in the data.

Restrictions on ROW columns

ROW columns have several restrictions:

  • Maximum nesting depth is 100.
  • Vertica tables support up to 9800 columns and fields. The ROW itself is not counted, only its fields.
  • ROW columns cannot use any constraints (such as NOT NULL) or defaults.
  • ROW fields cannot be auto_increment or setof.
  • 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.
  • Tables containing ROW columns cannot also contain IDENTITY, default, SET USING, or named sequence columns.

Syntax for direct construction (literals)

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

=> SELECT ROW('Amy',2,false);
                row
--------------------------------------------
 {"f0":"Amy","f1":2,"f2":false}
(1 row)

You can use an AS clause to name the ROW and its fields:

=> SELECT ROW('Amy',2,false) AS student(name, id, current);
               student
--------------------------------------------
 {"name":"Amy","id":2,"current":false}
(1 row)

You can also name individual fields using AS. This query produces the same output as the previous one:

=> SELECT ROW('Amy' AS name, 2 AS id, false AS current) AS student;

You do not need to name all fields.

In an array of ROW elements, if you use AS to name fields and the names differ among the elements, Vertica uses the right-most names for all elements:

=> SELECT ARRAY[ROW('Amy' AS name, 2 AS id),ROW('Fred' AS first_name, 4 AS id)];
                           array
------------------------------------------------------------
 [{"first_name":"Amy","id":2},{"first_name":"Fred","id":4}]
(1 row)

You can coerce types explicitly:

=> SELECT ROW('Amy',2.5::int,false::varchar);
               row
------------------------------------------
 {"f0":"Amy","f1":3,"f2":"f"}
(1 row)

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

=> SELECT ROW('Howard''s house',2,false);
                      row
---------------------------------------------------
 {"f0":"Howard's house","f1":2,"f2":false}
(1 row)

You can use fields of all scalar types, ROW, and ARRAY, as in the following example:

=> SELECT id.name, major, GPA FROM students
   WHERE id = ROW('alice',119, ARRAY['alice@example.com','ap16@cs.example.edu']);
 name  |               major                | GPA
-------+------------------------------------+-----
 alice | [{"school":"Science","dept":"CS"}] | 3.8
(1 row)

Output format

ROW values 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

Comparisons

ROW supports equality (=), inequality (<>), and null-safe equality (<=>) between inputs that have the same set of fields. ROWs that contain only primitive types, including nested ROWs of primitive types, also support comparison operators (<, <=, >, >=).

Two ROWs are equal if and only if all fields are equal. Vertica compares fields in order until an inequality is found or all fields have been compared. The evaluation of the first non-equal field determines which ROW is greater:

=> SELECT ROW(1, 'joe') > ROW(2, 'bob');
?column?
----------
f
(1 row)

Comparisons between ROWs with different schemas fail:

=> SELECT ROW(1, 'joe') > ROW(2, 'bob', 123);
ERROR 5162:  Unequal number of entries in row expressions

If the result of a comparison depends on a null field, the result is null:

=>  select row(1, null, 3) = row(1, 2, 3);
 ?column?
----------

(1 row)

Null-handling

If a struct exists but a field value is null, Vertica assigns NULL as its value in the ROW. A struct where all fields are null is treated as a ROW with null fields. If the struct itself is null, Vertica reads the ROW as NULL.

Casting

Casting a ROW casts each field. You can therefore cast between data types following the same rules as for casts of scalar values.

The following example casts the contact ROW in the customers table, changing the zipcode field from INT to VARCHAR and adding a bound to the array:

=> SELECT contact::ROW(VARCHAR,VARCHAR,VARCHAR,ARRAY[VARCHAR,20]) FROM customers;
                                                         contact

--------------------------------------------------------------------------------
-----------------------------------------
 {"street":"911 San Marcos St","city":"Austin","zipcode":"73344","email":["missy@mit.edu","mcooper@cern.gov"]}
 {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":"91001","email":["shelly@meemaw.name","cooper@caltech.edu"]}
 {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":"91001","email":["hofstadter@caltech.edu"]}
 {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":"91001","email":[]}
 {"street":null,"city":"Pasadena","zipcode":"91001","email":["raj@available.com"]}

(6 rows)

You can specify new field names to change them in the output:

=> SELECT contact::ROW(str VARCHAR, city VARCHAR, zip VARCHAR, email ARRAY[VARCHAR,
20]) FROM customers;
                                                     contact

--------------------------------------------------------------------------------
----------------------------------
 {"str":"911 San Marcos St","city":"Austin","zip":"73344","email":["missy@mit.edu","mcooper@cern.gov"]}
 {"str":"100 Main St Apt 4B","city":"Pasadena","zip":"91001","email":["shelly@meemaw.name","cooper@caltech.edu"]}
 {"str":"100 Main St Apt 4A","city":"Pasadena","zip":"91001","email":["hofstadter@caltech.edu"]}
 {"str":"23 Fifth Ave Apt 8C","city":"Pasadena","zip":"91001","email":[]}
 {"str":null,"city":"Pasadena","zip":"91001","email":["raj@available.com"]}
(6 rows)

Supported operators and predicates

ROW values may be used in queries in the following ways:

  • INNER and OUTER JOIN

  • Comparisons, IN, BETWEEN (non-nullable filters only)

  • IS NULL, IS NOT NULL

  • CASE

  • GROUP BY, ORDER BY

  • SELECT DISTINCT

  • Arguments to user-defined scalar, transform, and analytic functions

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

  • Math operators

  • Type coercion of whole rows (coercion of field values is supported)

  • BITWISE, LIKE

  • MLA (ROLLUP, CUBE, GROUPING SETS)

  • Aggregate functions including MAX, MIN, and SUM

  • Set operators including UNION, UNION ALL, MINUS, and INTERSECT

COUNT is not supported for ROWs returned from user-defined scalar functions, but is supported for ROW columns and literals.

In comparison operations (including implicit comparisons like ORDER BY), a ROW literal 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

Using rows in views and subqueries

You can use ROW columns to construct views and in subqueries. Consider employee and customer tables with the following definitions:

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

=> 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 following example creates a view and queries it.

=> CREATE VIEW neighbors (num_neighbors, area(city, zipcode))
AS SELECT count(*), ROW(address.city, address.zipcode)
FROM customers GROUP BY address.city, address.zipcode;
CREATE VIEW

=> SELECT employees.personal.name, neighbors.area FROM neighbors, employees
WHERE employees.personal.address.zipcode=neighbors.area.zipcode AND neighbors.nu
m_neighbors > 1;
        name        |                area
--------------------+-------------------------------------
 Sheldon Cooper     | {"city":"Pasadena","zipcode":91001}
 Leonard Hofstadter | {"city":"Pasadena","zipcode":91001}
(2 rows)