SELECT
Returns a result set from one or more data sources—tables, views, joined tables, and named subqueries.
Syntax
[ AT epoch ] [ WITH-clause ] SELECT [ ALL | DISTINCT ]
{ * | { MATCH_COLUMNS('pattern') | expression [ [AS] alias ] }[,...] }
[ into-table-clause ]
[ from-clause ]
[ where-clause ]
[ time-series-clause ]
[ group-by-clause[,...] ]
[ having-clause[,...] ]
[ match-clause ]
[ union-clause ]
[ intersect-clause ]
[ except-clause ]
[ order-by-clause [ offset-clause ]]
[ limit-clause ]
[ FOR UPDATE [ OF table-name[,...] ] ]
Note
SELECT statements can also embed various directives, or hints, that let you control how a given query is handled—for example, join hints such as JTYPE, which enforces the join type (merge or hash join).
For details on using Vertica hints, see Hints.
Parameters
ATepoch- Returns data from the specified epoch, where
epochis one of the following:-
EPOCH LATEST: Return data up to but not including the current epoch. The result set includes data from the latest committed DML transaction.
-
EPOCH
integer: Return data up to and including theinteger-specified epoch. -
TIME '
timestamp': Return data from thetimestamp-specified epoch.
Note
These options are ignored if used to query temporary or external tables.See Epochs for additional information about how Vertica uses epochs.
For details, see Historical queries.
-
ALL | DISTINCT-
ALL(default): Retains duplicate rows in result set or group. -
DISTINCT: Removes duplicate rows from the result set or group.
ALLorDISTINCTqualifier must immediately follow theSELECTkeyword. 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. -
MATCH_COLUMNS('pattern') - Returns all columns in the queried tables that match
pattern. expression[[AS]alias]- An expression that typically resolves to column data from the queried tables—for example, names of columns that are specified in the FROM clause; also:
-
Literals (constants)
-
Subqueries in the SELECT list
You can optionally assign a temporary alias to each column expression and reference that alias elsewhere in the SELECT statement—for example, in the query predicate or ORDER BY clause. Vertica uses the alias as the column heading in query output.
-
FOR UPDATE- Specifies to obtain an X lock on all tables specified in the query, most often used from
READ COMMITTEDisolation.FOR UPDATErequires update/delete permissions on the queried tables and cannot be issued from a read-only transaction.
Privileges
Non-superusers:
-
USAGE on the schema
-
SELECT on the table or view
Note
As view owner, you can grant other users SELECT privilege on the view only if one of the following is true:
-
You own the view's base table.
-
You have SELECT...WITH GRANT OPTION privilege on the view's base table.
Examples
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;