SELECT

Retrieves a result set from one or more tables.

Syntax

[{ AT EPOCH {LATEST | epoch-number} | AT TIME 'timestamp'}]
SELECT [ /*+ LABEL(label‑name)*/ ] [ ALL | DISTINCT ] 
... { * | expression [ [AS] output-name] }[,...]
... [ INTO TABLE ]
... [ from-clause ]
... [ WHERE condition ]
... [ TIMESERIES slice-time ]
... [ GROUP BY expression[,...] ]
... [ HAVING condition[,...] ]
... [ MATCH ]
... [ UNION { ALL | DISTINCT } ] 
... [ EXCEPT ]
... [ INTERSECT ]
... [ ORDER BY expression { ASC | DESC }[,...] ]
... [ LIMIT { count | ALL } ]
... [ OFFSET start ]
... [ FOR UPDATE [ OF table-name[,...] ] ]

Parameters

Note: SELECT clauses such as INTO and WHERE are discussed in sub-sections of this page.

AT EPOCH LATEST

Queries all data in the database up to but not including the current epoch without holding a lock or blocking write operations. See Snapshot Isolation for more information. AT EPOCH LATEST is ignored when applied to temporary tables (all rows are returned).

By default, queries run under the READ COMMITTED isolation level, which means:

  • AT EPOCH LATEST includes data from the latest committed DML transaction.
  • Each epoch contains exactly one transaction—the one that modified the data.
  • The Tuple Mover can perform moveout and mergeout operations on committed data immediately.
epoch‑number Queries all data in the database up to and including the current epoch without holding a lock or blocking write operations.
AT TIME 'timestamp'

Specifies an historical query, where all data in the database is queried, up to and including the epoch representing the specified date and time. This query holds no locks and blocks no write operations.

AT TIME is ignored when applied to temporary tables (all rows are returned).

/*+LABEL(label‑name)*/

Assigns a label to a query so you can identify it for profiling and debugging.

In a UNION statement, only the first SELECT statement can be labeled; Vertica ignores labels in subsequent SELECT statements.

ALL | DISTINCT
  • ALL (default): Retains duplicate rows in result set or group.
  • DISTINCT: Removes duplicate rows from the result set or group.

The ALL or DISTINCT qualifier must immediately follow the SELECT keyword. Only one instance of this keyword can appear in the select list.

*

Lists all columns in the queried tables.

Caution: Selecting all columns from the queried tables can produce a very large wide set, which can adversely affect performance.

expression

Forms the output rows of the SELECT statement. The expression can contain:

expression
[[AS] output‑name]

A table column or column expression to select from the queried tables. You can optionally qualify expression
with an output name, which can be used in several ways:

  • Label the column for display.
  • Refer to the column's value in ORDER BY and GROUP BY clauses (it cannot be referenced in WHERE or HAVING clauses).
from-clause

A comma-separated list of data sources to query.

FOR UPDATE

Specifies to obtain an X lock on all tables specified in the query, most often used from READ COMMITTED isolation.

FOR UPDATE requires update/delete permissions on the queried tables and cannot be issued from a read-only transaction.

Privileges

You must have USAGE privileges on the schemas that contain the queried tables, as well as one, but not both, of the following:

If the SELECT statement queries a view, the view owner must have SELECT privileges on the view's anchor tables or views.

Example

When multiple clients run transactions as in the following example query, deadlocks can occur if FOR UPDATE is not used. Two transactions acquire an S lock, and when both attempt to upgrade to an X lock, they encounter deadlocks:

=> SELECT balance FROM accounts WHERE account_id=3476 FOR UPDATE;
 ...
=> UPDATE accounts SET balance = balance+10 WHERE account_id=3476; 
=> COMMIT;