SELECT
Returns a result set from one or more data sources—tables, views, joined tables, and named subqueries.
Syntax
[ AT epoch ] SELECT [ /*+ LABEL(label‑name)*/ ] [ ALL | DISTINCT ] { * | expression [ [AS] output-name] }[,…] [ into-table-clause ] [ from-clause ] [ where‑clause ] [ time‑series‑clause ] [ group‑by‑clause[,…] ] [ having-clause[,…] ] [ match-clause ] [ UNION { ALL | DISTINCT } ] [ except‑clause ] [ intersect‑clause ] [ ORDER BY expression { ASC | DESC }[,…] ] [ LIMIT { count | ALL } ] [ OFFSET start‑row ] [ FOR UPDATE [ OF table-name[,…] ] ]
Parameters
SELECT
clauses such as INTO
and WHERE
are discussed in sub-sections of this page.
AT epoch |
Returns data from the specified epoch, where epoch is one of the following:
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. |
/*+LABEL(label‑name)*/ |
Assigns a label to a query so you can identify it for profiling and debugging. In a |
ALL | DISTINCT |
The |
* |
Lists all columns in the queried tables. 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
|
expression[[AS] output‑name]
|
A table column or column expression to select from the queried tables. You can optionally qualify expression
|
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
|
Privileges
Non-superusers:
- USAGE on the schema
- SELECT on the table or view
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.
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;