'a string'BNF for SQL Grammar
Reserved Keywords
Non-Reserved Keywords
Reserved Keywords For Future Use
ALLOCATE |
ARE |
ASENSITIVE |
ASYMETRIC |
AUTHORIZATION |
BINARY |
CALLED |
CASCADED |
CHARACTER |
CHECK |
CLOSE |
COLLATE |
CONNECT |
CORRESPONDING |
CRITERIA |
CURRENT_USER |
CURSOR |
CYCLE |
DATALINK |
DEALLOCATE |
DEC |
DEREF |
DESCRIBE |
DETERMINISTIC |
DISCONNECT |
DLNEWCOPY |
DLPREVIOUSCOPY |
DLURLCOMPLETE |
DLURLCOMPLETEONLY |
DLURLCOMPLETEWRITE |
DLURLPATH |
DLURLPATHONLY |
DLURLPATHWRITE |
DLURLSCHEME |
DLURLSERVER |
DLVALUE |
DYNAMIC |
ELEMENT |
EXTERNAL |
FREE |
GET |
HAS |
HOLD |
IDENTITY |
INDICATOR |
INPUT |
INSENSITIVE |
INT |
INTERVAL |
ISOLATION |
LARGE |
LOCALTIME |
LOCALTIMESTAMP |
MATCH |
MEMBER |
METHOD |
MODIFIES |
MODULE |
MULTISET |
NATIONAL |
NATURAL |
NCHAR |
NCLOB |
NEW |
NUMERIC |
OLD |
OPEN |
OUTPUT |
OVERLAPS |
PRECISION |
PREPARE |
RANGE |
READS |
RECURSIVE |
REFERENCING |
RELEASE |
ROLLBACK |
SAVEPOINT |
SCROLL |
SEARCH |
SENSITIVE |
SESSION_USER |
SPECIFIC |
SPECIFICTYPE |
SQL |
START |
STATIC |
SUBMULTILIST |
SYMETRIC |
SYSTEM |
SYSTEM_USER |
TIMEZONE_HOUR |
TIMEZONE_MINUTE |
TRANSLATION |
TREAT |
VALUE |
VARYING |
WHENEVER |
WINDOW |
WITHIN |
XMLBINARY |
XMLDOCUMENT |
Tokens
Production Cross-Reference
Productions
string ::=
A string literal value. Use '' to escape ' in the string.
Example:
'it''s a string'non-reserved identifier ::=
Allows non-reserved keywords to be parsed as identifiers
Example: SELECT COUNT FROM …
create trigger ::=
-
CREATE TRIGGER ( <identifier> )? ON <identifier> ( ( INSTEAD OF ) | AFTER ) ( INSERT | UPDATE | DELETE ) AS <for each row trigger action>
Creates a trigger action on the given target.
Example:
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... ENDalter ::=
-
ALTER ( ( VIEW <identifier> AS <query expression> ) | ( PROCEDURE <identifier> AS <statement> ) | ( TRIGGER ( <identifier> )? ON <identifier> ( ( INSTEAD OF ) | AFTER ) ( INSERT | UPDATE | DELETE ) ( ( AS <for each row trigger action> ) | ENABLED | DISABLED ) ) )
Alter the given target.
Example:
ALTER VIEW vw AS SELECT col FROM tblfor each row trigger action ::=
Defines an action to perform on each row.
Example:
FOR EACH ROW BEGIN ATOMIC ... ENDdirectly executable statement ::=
A statement that can be executed at runtime.
Example:
SELECT * FROM tblcreate temporary table ::=
-
CREATE ( LOCAL )? TEMPORARY TABLE <identifier> <lparen> <temporary table element> ( <comma> <temporary table element> )* ( <comma> PRIMARY KEY <column list> )? <rparen> ( ON COMMIT PRESERVE ROWS )?
Creates a temporary table.
Example:
CREATE LOCAL TEMPORARY TABLE tmp (col integer)temporary table element ::=
-
<identifier> ( <basic data type> | SERIAL ) ( NOT NULL )?
Defines a temporary table column.
Example:
col string NOT NULLraise error statement ::=
Raises an error with the given message.
Example:
ERROR 'something went wrong'raise statement ::=
-
RAISE ( SQLWARNING )? <exception reference>
Raises an error or warning with the given message.
Example:
RAISE SQLEXCEPTION 'something went wrong'exception reference ::=
a reference to an exception
Example:
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2sql exception ::=
-
SQLEXCEPTION <common value expression> ( SQLSTATE <common value expression> ( <comma> <common value expression> )? )? ( CHAIN <exception reference> )?
creates a sql exception or warning with the specified message, state, and code
Example:
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2statement ::=
-
( ( <identifier> <colon> )? ( <loop statement> | <while statement> | <compound statement> ) )
A procedure statement.
Example:
IF (x = 5) BEGIN ... ENDdelimited statement ::=
-
( <assignment statement> | <data statement> | <raise error statement> | <raise statement> | <declare statement> | <branching statement> | <return statement> ) <semicolon>
A procedure statement terminated by ;.
Example:
SELECT * FROM tbl;compound statement ::=
A procedure statement block contained in BEGIN END.
Example:
BEGIN NOT ATOMIC ... ENDbranching statement ::=
-
( ( BREAK | CONTINUE ) ( <identifier> )? )
-
( LEAVE <identifier> )
A procedure branching control statement, which typically specifies a label to return control to.
Example:
BREAK xwhile statement ::=
A procedure while statement that executes until its condition is false.
Example:
WHILE (var) BEGIN ... ENDloop statement ::=
-
LOOP ON <lparen> <query expression> <rparen> AS <identifier> <statement>
A procedure loop statement that executes over the given cursor.
Example:
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... ENDif statement ::=
A procedure loop statement that executes over the given cursor.
Example:
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 ENDdeclare statement ::=
-
DECLARE ( <data type> | EXCEPTION ) <identifier> ( <eq> <assignment statement operand> )?
A procedure declaration statement that creates a variable and optionally assigns a value.
Example:
DECLARE STRING x = 'a'assignment statement ::=
-
<identifier> <eq> ( <assignment statement operand> | ( <call statement> ( ( WITH | WITHOUT ) RETURN )? ) )
Assigns a variable a value in a procedure.
Example:
x = 'b'assignment statement operand ::=
A value or command that can be used in an assignment. {note}All assignments except for expression are deprecated.{note}
data statement ::=
-
( <directly executable statement> | <dynamic data statement> ) ( ( WITH | WITHOUT ) RETURN )?
A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.
procedure body definition ::=
Defines a procedure body on a Procedure metadata object.
Example:
BEGIN ... ENDdynamic data statement ::=
-
( EXECUTE | EXEC ) ( STRING | IMMEDIATE )? <expression> ( AS <typed element list> ( INTO <identifier> )? )? ( USING <set clause list> )? ( UPDATE ( <unsigned integer> | <star> ) )?
A procedure statement that can execute arbitrary sql.
Example:
EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #tempset clause list ::=
-
<identifier> <eq> <expression> ( <comma> <identifier> <eq> <expression> )*
A list of value assignments.
Example:
col1 = 'x', col2 = 'y' ...typed element list ::=
-
<identifier> <basic data type> ( <comma> <identifier> <basic data type> )*
A list of typed elements.
Example:
col1 string, col2 integer ...callable statement ::=
-
<lbrace> ( <qmark> <eq> )? CALL <identifier> ( <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> )? <rbrace> ( <option clause> )?
A callable statement defined using JDBC escape syntax.
Example:
{? = CALL proc}call statement ::=
-
( ( EXEC | EXECUTE | CALL ) <identifier> <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> ) ( <option clause> )?
Executes the procedure with the given parameters.
Example:
CALL proc('a', 1)named parameter list ::=
-
( <identifier> <eq> ( <gt> )? <expression> ( <comma> <identifier> <eq> ( <gt> )? <expression> )* )
A list of named parameters.
Example:
param1 => 'x', param2 => 1insert statement ::=
-
( INSERT | MERGE | UPSERT ) INTO <identifier> ( <column list> )? <query expression> ( <option clause> )?
Inserts values into the given target.
Example:
INSERT INTO tbl (col1, col2) VALUES ('a', 1)expression list ::=
-
<expression> ( <comma> <expression> )*
A list of expressions.
Example:
col1, 'a', ...update statement ::=
-
UPDATE <identifier> ( ( AS )? <identifier> )? SET <set clause list> ( <where clause> )? ( <option clause> )?
Update values in the given target.
Example:
UPDATE tbl SET (col1 = 'a') WHERE col2 = 1delete statement ::=
-
DELETE FROM <identifier> ( ( AS )? <identifier> )? ( <where clause> )? ( <option clause> )?
Delete rows from the given target.
Example:
DELETE FROM tbl WHERE col2 = 1query expression ::=
-
( WITH <with list element> ( <comma> <with list element> )* )? <query expression body>
A declarative query for data.
Example:
SELECT * FROM tbl WHERE col2 = 1with list element ::=
-
<identifier> ( <column list> )? AS <lparen> <query expression> <rparen>
A query expression for use in the enclosing query.
Example:
X (Y, Z) AS (SELECT 1, 2)query expression body ::=
-
<query term> ( ( UNION | EXCEPT ) ( ALL | DISTINCT )? <query term> )* ( <order by clause> )? ( <limit clause> )? ( <option clause> )?
The body of a query expression, which can optionally be ordered and limited.
Example:
SELECT * FROM tbl ORDER BY col1 LIMIT 1query term ::=
-
<query primary> ( INTERSECT ( ALL | DISTINCT )? <query primary> )*
Used to establish INTERSECT precedence.
Example:
SELECT * FROM tblSELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2query primary ::=
-
<query>
-
( VALUES <lparen> <expression list> <rparen> ( <comma> <lparen> <expression list> <rparen> )* )
-
( TABLE <identifier> )
-
( <lparen> <query expression body> <rparen> )
A declarative source of rows.
Example:
TABLE tblSELECT * FROM tbl1query ::=
-
<select clause> ( <into clause> )? ( <from clause> ( <where clause> )? ( <group by clause> )? ( <having clause> )? )?
A SELECT query.
Example:
SELECT col1, max(col2) FROM tbl GROUP BY col1into clause ::=
Used to direct the query into a table. {note}This is deprecated. Use INSERT INTO with a query expression instead.{note}
Example:
INTO tblselect clause ::=
-
SELECT ( ALL | DISTINCT )? ( <star> | ( <select sublist> ( <comma> <select sublist> )* ) )
The columns returned by a query. Can optionally be distinct.
Example:
SELECT *SELECT DISTINCT a, b, cselect derived column ::=
-
( <expression> ( ( AS )? <identifier> )? )
A select clause item that selects a single column. {note}This is slightly different than a derived column in that the AS keyword is optional.{note}
Example:
tbl.col AS xderived column ::=
-
( <expression> ( AS <identifier> )? )
An optionally named expression.
Example:
tbl.col AS xordered aggregate function ::=
-
( XMLAGG | ARRAY_AGG | JSONARRAY_AGG ) <lparen> <expression> ( <order by clause> )? <rparen>
An aggregate function that can optionally be ordered.
Example:
XMLAGG(col1) ORDER BY col2ARRAY_AGG(col1)text aggreate function ::=
-
TEXTAGG <lparen> ( FOR )? <derived column> ( <comma> <derived column> )* ( DELIMITER <character> )? ( ( QUOTE <character> ) | ( NO QUOTE ) )? ( HEADER )? ( ENCODING <identifier> )? ( <order by clause> )? <rparen>
An aggregate function for creating separated value clobs.
Example:
TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)analytic aggregate function ::=
-
( ROW_NUMBER | RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST ) <lparen> <rparen>
An analytic aggregate function.
Example:
ROW_NUMBER()filter clause ::=
-
FILTER <lparen> WHERE <boolean primary> <rparen>
An aggregate filter clause applied prior to accumulating the value.
Example:
FILTER (WHERE col1='a')from clause ::=
-
FROM ( <table reference> ( <comma> <table reference> )* )
A query from clause containing a list of table references.
Example:
FROM a, bFROM a right outer join b, c, d join e".</p>table reference ::=
-
( <escaped join> <joined table> <rbrace> )
An optionally escaped joined table.
Example:
aa inner join bjoined table ::=
-
<table primary> ( <cross join> | <qualified table> )*
A table or join.
Example:
aa inner join btable primary ::=
-
( <text table> | <array table> | <xml table> | <object table> | <table name> | <table subquery> | ( <lparen> <joined table> <rparen> ) ) ( ( MAKEDEP <make dep options> ) | MAKENOTDEP )? ( ( MAKEIND <make dep options> ) )?
A single source of rows.
Example:
axml serialize ::=
-
XMLSERIALIZE <lparen> ( DOCUMENT | CONTENT )? <expression> ( AS ( STRING | VARCHAR | CLOB | VARBINARY | BLOB ) )? ( ENCODING <identifier> )? ( VERSION <string> )? ( ( INCLUDING | EXCLUDING ) XMLDECLARATION )? <rparen>
Serializes an XML value.
Example:
XMLSERIALIZE(col1 AS CLOB)array table ::=
-
ARRAYTABLE <lparen> <value expression primary> COLUMNS <typed element list> <rparen> ( AS )? <identifier>
The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.
Example:
ARRAYTABLE (col1 COLUMNS x STRING) AS ytext table ::=
-
TEXTTABLE <lparen> <common value expression> ( SELECTOR <string> )? COLUMNS <text table column> ( <comma> <text table column> )* ( ( NO ROW DELIMITER ) | ( ROW DELIMITER <character> ) )? ( DELIMITER <character> )? ( ( ESCAPE <character> ) | ( QUOTE <character> ) )? ( HEADER ( <unsigned integer> )? )? ( SKIP <unsigned integer> )? ( NO TRIM )? <rparen> ( AS )? <identifier>
The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.
Example:
TEXTTABLE (file COLUMNS x STRING) AS ytext table column ::=
-
<identifier> ( ( FOR ORDINALITY ) | ( ( HEADER <string> )? <basic data type> ( WIDTH <unsigned integer> ( NO TRIM )? )? ( SELECTOR <string> <unsigned integer> )? ) )
A text table column.
Example:
x INTEGER WIDTH 6xml query ::=
-
XMLEXISTS <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? <rparen>
Executes an XQuery to return an XML result.
Example:
XMLQUERY('<a>...</a>' PASSING doc)xml query ::=
-
XMLQUERY <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( ( NULL | EMPTY ) ON EMPTY )? <rparen>
Executes an XQuery to return an XML result.
Example:
XMLQUERY('<a>...</a>' PASSING doc)object table ::=
-
OBJECTTABLE <lparen> ( LANGUAGE <string> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? COLUMNS <object table column> ( <comma> <object table column> )* <rparen> ( AS )? <identifier>
Returns table results by processing a script.
Example:
OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS Xobject table column ::=
-
<identifier> <basic data type> <string> ( DEFAULT <expression> )?
object table column.
Example:
y integer 'teiid_row_number'xml table ::=
-
XMLTABLE <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( COLUMNS <xml table column> ( <comma> <xml table column> )* )? <rparen> ( AS )? <identifier>
Returns table results by processing an XQuery.
Example:
XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS Xxml table column ::=
-
<identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( DEFAULT <expression> )? ( PATH <string> )? ) )
XML table column.
Example:
y FOR ORDINALITYtable subquery ::=
-
( TABLE | LATERAL )? <lparen> ( <query expression> | <call statement> ) <rparen> ( AS )? <identifier>
A table defined by a subquery.
Example:
(SELECT * FROM tbl) AS xtable name ::=
-
( <identifier> ( ( AS )? <identifier> )? )
A table named in the FROM clause.
Example:
tbl AS xboolean value expression ::=
-
<boolean term> ( OR <boolean term> )*
An optionally ORed boolean expression.
boolean primary ::=
-
( <common value expression> ( <between predicate> | <match predicate> | <like regex predicate> | <in predicate> | <is null predicate> | <quantified comparison predicate> | <comparison predicate> | <is distinct> )? )
A boolean predicate or simple expression.
Example:
col LIKE 'a%'is distinct ::=
-
IS ( NOT )? DISTINCT FROM <common value expression>
Is Distinct Right Hand Side
Example:
IS DISTINCT FROM expressionsubquery ::=
-
<lparen> ( <query expression> | <call statement> ) <rparen>
A subquery.
Example:
(SELECT * FROM tbl)quantified comparison predicate ::=
-
<comparison operator> ( ANY | SOME | ALL ) ( <subquery> | ( <lparen> <expression> <rparen> ) )
A subquery comparison.
Example:
= ANY (SELECT col FROM tbl)like regex predicate ::=
-
( NOT )? LIKE_REGEX <common value expression>
A regular expression match.
Example:
LIKE_REGEX 'a.*b'in predicate ::=
-
( NOT )? IN ( <subquery> | ( <lparen> <common value expression> ( <comma> <common value expression> )* <rparen> ) )
A comparison with multiple values.
Example:
IN (1, 5)group by clause ::=
-
GROUP BY ( ROLLUP <lparen> <expression list> <rparen> | <expression list> )
Defines the grouping columns
Example:
GROUP BY col1, col2order by clause ::=
-
ORDER BY <sort specification> ( <comma> <sort specification> )*
Specifices row ordering.
Example:
ORDER BY x, y DESClimit clause ::=
-
( LIMIT <integer parameter> ( ( <comma> <integer parameter> ) | ( OFFSET <integer parameter> ) )? )
-
( OFFSET <integer parameter> ( ROW | ROWS ) ( <fetch clause> )? )
Limits and/or offsets the resultant rows.
Example:
LIMIT 2option clause ::=
-
OPTION ( MAKEDEP <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKEIND <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKENOTDEP <identifier> ( <comma> <identifier> )* | NOCACHE ( <identifier> ( <comma> <identifier> )* )? )*
Specifies query options.
Example:
OPTION MAKEDEP tblcommon value expression ::=
-
( <numeric value expression> ( ( <double_amp_op> | <concat_op> ) <numeric value expression> )* )
Establishes the precedence of concat.
Example:
'a' || 'b'term ::=
-
( <value expression primary> ( <star or slash> <value expression primary> )* )
A numeric term
Example:
1 * 2value expression primary ::=
-
( <plus or minus> )? ( <unsigned numeric literal> | ( <unsigned value expression primary> ( <lsbrace> <numeric value expression> <rsbrace> )* ) )
A simple value expression.
Example:
+col1parameter reference ::=
-
<qmark>
-
( <dollar> <unsigned integer> )
A parameter reference to be bound later.
Example:
?unescapedFunction ::=
-
( ( <text aggreate function> | <standard aggregate function> | <ordered aggregate function> ) ( <filter clause> )? ( <window specification> )? ) | ( <analytic aggregate function> ( <filter clause> )? <window specification> ) | ( <function> ( <window specification> )? )
nested expression ::=
-
( <lparen> ( <expression> ( <comma> <expression> )* )? ( <comma> )? <rparen> )
An expression nested in parens
Example:
(1)ARRAY expression constructor ::=
-
( ARRAY <lsbrace> ( <expression> ( <comma> <expression> )* )? <rsbrace> )
Creates and array of the given expressions.
Example:
----ARRAY[1,2]
----window specification ::=
-
OVER <lparen> ( PARTITION BY <expression list> )? ( <order by clause> )? <rparen>
The window specification for an analytical or windowed aggregate function.
Example:
OVER (PARTION BY col1)case expression ::=
-
CASE <expression> ( WHEN <expression> THEN <expression> )+ ( ELSE <expression> )? END
If/then/else chain using a common search predicand.
Example:
CASE col1 WHEN 'a' THEN 1 ELSE 2searched case expression ::=
-
CASE ( WHEN <condition> THEN <expression> )+ ( ELSE <expression> )? END
If/then/else chain using multiple search conditions.
Example:
CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2function ::=
-
( CONVERT <lparen> <expression> <comma> <data type> <rparen> )
-
( SUBSTRING <lparen> <expression> ( ( FROM <expression> ( FOR <expression> )? ) | ( <comma> <expression list> ) ) <rparen> )
-
( EXTRACT <lparen> ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND ) FROM <expression> <rparen> )
-
( TRIM <lparen> ( ( ( ( LEADING | TRAILING | BOTH ) ( <expression> )? ) | <expression> ) FROM )? <expression> <rparen> )
-
( ( TO_CHARS | TO_BYTES ) <lparen> <expression> <comma> <string> ( <comma> <expression> )? <rparen> )
-
( ( TIMESTAMPADD | TIMESTAMPDIFF ) <lparen> <time interval> <comma> <expression> <comma> <expression> <rparen> )
-
( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT | XMLTEXT ) <lparen> ( <expression list> )? <rparen> )
-
( ( TRANSLATE | INSERT ) <lparen> ( <expression list> )? <rparen> )
-
( XMLPI <lparen> ( ( NAME )? <identifier> ) ( <comma> <expression> )? <rparen> )
-
( POSITION <lparen> <common value expression> IN <common value expression> <rparen> )
-
( <identifier> <lparen> ( ALL | DISTINCT )? ( <expression list> )? ( <order by clause> )? <rparen> ( <filter clause> )? )
-
( CURRENT_DATE ( <lparen> <rparen> )? )
-
( ( CURRENT_TIMESTAMP | CURRENT_TIME ) ( <lparen> <unsigned integer> <rparen> )? )
Calls a scalar function.
Example:
func('1', col1)xml parse ::=
-
XMLPARSE <lparen> ( DOCUMENT | CONTENT ) <expression> ( WELLFORMED )? <rparen>
Parses the given value as XML.
Example:
XMLPARSE(DOCUMENT doc WELLFORMED)querystring function ::=
-
QUERYSTRING <lparen> <expression> ( <comma> <derived column> )* <rparen>
Produces a URL query string from the given arguments.
Example:
QUERYSTRING('path', col1 AS opt, col2 AS val)xml element ::=
-
XMLELEMENT <lparen> ( ( NAME )? <identifier> ) ( <comma> <xml namespaces> )? ( <comma> <xml attributes> )? ( <comma> <expression> )* <rparen>
Creates an XML element.
Example:
XMLELEMENT(NAME "root", child)xml attributes ::=
-
XMLATTRIBUTES <lparen> <derived column> ( <comma> <derived column> )* <rparen>
Creates attributes for the containing element.
Example:
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)json object ::=
Produces a JSON object containing name value pairs.
Example:
JSONOBJECT(col1 AS val1, col2 AS val2)derived column list ::=
-
<derived column> ( <comma> <derived column> )*
a list of name value pairs
Example:
col1 AS val1, col2 AS val2xml forest ::=
-
XMLFOREST <lparen> ( <xml namespaces> <comma> )? <derived column list> <rparen>
Produces an element for each derived column.
Example:
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)xml namespaces ::=
-
XMLNAMESPACES <lparen> <xml namespace element> ( <comma> <xml namespace element> )* <rparen>
Defines XML namespace URI/prefix combinations
Example:
XMLNAMESPACES('http://foo' AS foo)simple data type ::=
-
( STRING ( <lparen> <unsigned integer> <rparen> )? )
-
( VARCHAR ( <lparen> <unsigned integer> <rparen> )? )
-
( CHAR ( <lparen> <unsigned integer> <rparen> )? )
-
( BIGINTEGER ( <lparen> <unsigned integer> <rparen> )? )
-
( BIGDECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
-
( DECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
-
( OBJECT ( <lparen> <unsigned integer> <rparen> )? )
-
( BLOB ( <lparen> <unsigned integer> <rparen> )? )
-
( CLOB ( <lparen> <unsigned integer> <rparen> )? )
-
( VARBINARY ( <lparen> <unsigned integer> <rparen> )? )
A non-collection data type.
Example:
STRINGddl statement ::=
-
<create table> ( <create table> | <create procedure> )?
-
<create procedure> ( <ddl statement> )?
A data definition statement.
Example:
CREATE FOREIGN TABLE X (Y STRING)option namespace ::=
-
SET NAMESPACE <string> AS <identifier>
A namespace used to shorten the full name of an option key.
Example:
SET NAMESPACE 'http://foo' AS foocreate database ::=
-
CREATE DATABASE <identifier> ( VERSION <string> )? ( <options clause> )?
create a new database
Example:
CREATE DATABASE foo [VERSION 'version'] OPTIONS(...)use database ::=
-
USE DATABASE <identifier> ( VERSION <string> )?
database into working context
Example:
USE DATABASE foo [VERSION 'version']create schema ::=
-
CREATE ( VIRTUAL | FOREIGN )? SCHEMA <identifier> ( SERVER <identifier list> )? ( <options clause> )?
create a schema in database
Example:
CREATE [VIRTUAL] SCHEMA foo SERVER (s1,s2,s3) OPTIONS(...)drop schema ::=
-
DROP ( VIRTUAL | FOREIGN )? SCHEMA <identifier>
drop a schema in database
Example:
----DROP SCHEMA foo
----create a domain or type alias ::=
creates a named type with optional constraints
Example:
CREATE DOMAIN my_type AS INTEGER NOT NULLcreate data wrapper ::=
-
CREATE FOREIGN ( DATA WRAPPER | TRANSLATOR ) <Unqualified identifier> ( TYPE <identifier> )? ( <options clause> )?
Defines a translator; use the options to override the translator properties.
Example:
CREATE FOREIGN (DATA WRAPPER|TRANSLATOR) wrapper OPTIONS(properties)Drop data wrapper ::=
-
DROP FOREIGN ( DATA WRAPPER | TRANSLATOR ) <identifier>
Deletes a translator
Example:
DROP FOREIGN (DATA WRAPPER|TRANSLATOR) wrappercreate role ::=
-
CREATE ROLE <Unqualified identifier> ( WITH <with role> )?
Defines data role for the database
Example:
CREATE DATA ROLE <data-role> [WITH JAAS ROLE <string>(,<string>)*]with role ::=
-
( JAAS ROLE <identifier list> | ANY AUTHENTICATED ) ( WITH ( JAAS ROLE <identifier list> | ANY AUTHENTICATED ) )*
Create GRANT ::=
-
GRANT ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION ( CONSTRAINT )? <string> )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION ( CONSTRAINT )? <string> )? | SCHEMA <identifier> | COLUMN <identifier> ( MASK ( ORDER <unsigned integer> )? <string> )? ) ) | ( ALL PRIVILEGES ) | ( TEMPORARY TABLE ) | ( USAGE ON LANGUAGE <identifier> ) ) TO <identifier>
Defines GRANT for a role
Example:
GRANT SELECT ON TABLE x.y TO roleRevoke GRANT ::=
-
REVOKE ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION )? | SCHEMA <identifier> | COLUMN <identifier> ( MASK )? ) ) | ( ALL PRIVILEGES ) | ( TEMPORARY TABLE ) | ( USAGE ON LANGUAGE <identifier> ) ) FROM <identifier>
Revokes GRANT for a role
Example:
REVOKE SELECT ON TABLE x.y TO rolecreate server, aka data source ::=
-
CREATE SERVER <Unqualified identifier> ( TYPE <string> )? ( VERSION <string> )? FOREIGN ( DATA WRAPPER | TRANSLATOR ) <Unqualified identifier> ( <options clause> )?
Defines connection to foreign source
Example:
CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ] FOREIGN (<DATA> <WRAPPER>|<TRANSLATOR>) fdw_name [ OPTIONS ( option 'value' [, ... ] ) ]drop server, aka data source ::=
Defines dropping connection to foreign source
Example:
----DROP SERVER server_name
----create procedure ::=
-
CREATE ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) <Unqualified identifier> ( <lparen> ( <procedure parameter> ( <comma> <procedure parameter> )* )? <rparen> ( RETURNS ( <options clause> )? ( ( ( TABLE )? <lparen> <procedure result column> ( <comma> <procedure result column> )* <rparen> ) | <data type> ) )? ( <options clause> )? ( AS <statement> )? )
Defines a procedure or function invocation.
Example:
CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRINGprocedure parameter ::=
-
( IN | OUT | INOUT | VARIADIC )? <identifier> <data type> ( NOT NULL )? ( RESULT )? ( DEFAULT <expression> )? ( <options clause> )?
A procedure or function parameter
Example:
OUT x INTEGERprocedure result column ::=
-
<identifier> <data type> ( NOT NULL )? ( <options clause> )?
A procedure result column.
Example:
x INTEGERcreate foreign or global temporary table ::=
-
( ( FOREIGN TABLE ) | ( GLOBAL TEMPORARY TABLE ) ) <Unqualified identifier> <create table body>
Defines a foreign or global temporary table.
Example:
FOREIGN TABLE ft (col integer)create view ::=
-
( VIRTUAL )? VIEW <Unqualified identifier> ( <create view body> | ( <options clause> )? ) AS <query expression>
Defines a view.
Example:
VIEW vw AS SELECT 1create foreign temp table ::=
-
CREATE ( LOCAL )? FOREIGN TEMPORARY TABLE <identifier> <create table body> ON <identifier>
Defines a foreign temp table
Example:
CREATE FOREIGN TEMPORARY TABLE t (x string) ON zcreate table body ::=
-
<lparen> <table element> ( <comma> ( <table constraint> | <table element> ) )* <rparen> ( <options clause> )?
Defines a table.
Example:
(x string) OPTIONS (CARDINALITY 100)create view body ::=
-
<lparen> <view element> ( <comma> ( <table constraint> | <view element> ) )* <rparen> ( <options clause> )?
Defines a view.
Example:
(x) OPTIONS (CARDINALITY 100)table constraint ::=
-
( CONSTRAINT <identifier> )? ( <primary key> | <other constraints> | <foreign key> ) ( <options clause> )?
Defines a constraint on a table or view.
Example:
FOREIGN KEY (a, b) REFERENCES tbl (x, y)foreign key ::=
-
FOREIGN KEY <column list> REFERENCES <identifier> ( <column list> )?
Defines the foreign key referential constraint.
Example:
FOREIGN KEY (a, b) REFERENCES tbl (x, y)other constraints ::=
-
( ( UNIQUE | ACCESSPATTERN ) <column list> )
-
( INDEX <lparen> <expression list> <rparen> )
Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.
Example:
UNIQUE (a)column list ::=
-
<lparen> <identifier> ( <comma> <identifier> )* <rparen>
A list of column names.
Example:
(a, b)table element ::=
-
<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) <post create column>
Defines a table column.
Example:
x INTEGER NOT NULLview element ::=
-
<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) )? <post create column>
Defines a view column with optional type.
Example:
x INTEGER NOT NULLpost create column ::=
-
( <inline constraint> )? ( DEFAULT <expression> )? ( <options clause> )?
Common options trailing a column
Example:
PRIMARY KEYoptions clause ::=
-
OPTIONS <lparen> <option pair> ( <comma> <option pair> )* <rparen>
A list of statement options.
Example:
OPTIONS ('x' 'y', 'a' 'b')option pair ::=
-
<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )
An option key/value pair.
Example:
'key' 'value'alter option pair ::=
-
<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )
Alter An option key/value pair.
Example:
'key' 'value'alterStatement ::=
-
ALTER ( <ALTER TABLE> | <ALTER PROCEDURE> | <ALTER TRIGGER> | <ALTER SERVER> | <ALTER DATA WRAPPER> | <ALTER DATABASE> )
ALTER TABLE ::=
-
( ( ( VIRTUAL )? VIEW <identifier> ) | ( ( FOREIGN )? TABLE <identifier> ) ) ( ( AS <query expression> ) | <ADD column> | <ADD constraint> | <alter options list> | <DROP column> | ( ALTER COLUMN <alter column options> ) | ( RENAME ( <RENAME Table> | ( COLUMN <rename column options> ) ) ) )
alters options of database
Example:
ALTER TABLE foo (ADD|DROP|ALTER) COLUMN <name> <type> OPTIONS ( (ADD|SET|DROP) x y)ADD column ::=
alters table and adds a column
Example:
ADD COLUMN bar type OPTIONS (ADD updatable true)alter column options ::=
-
<identifier> ( ( TYPE ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) ) | <alter child options list> )
alters a set of column options
Example:
ALTER COLUMN bar OPTIONS (ADD updatable true)rename column options ::=
-
<identifier> TO <identifier>
renames either a table column or procedure’s parameter name
Example:
RENAME COLUMN bar TO fooALTER PROCEDURE ::=
-
( VIRTUAL | FOREIGN )? PROCEDURE <identifier> ( ( AS <statement> ) | <alter options list> | ( ALTER PARAMETER <alter column options> ) | ( RENAME PARAMETER <rename column options> ) )
alters options of database
Example:
ALTER PROCEDURE foo [AS <stmt>] OPTIONS (ADD x y)ALTER TRIGGER ::=
alters options of table triggers
Example:
ALTER TRIGGER ON <id> INSTEAD OF (INSERT|UPDATE|DELETE) AS [ENABLED|DISABLED]ALTER DATA WRAPPER ::=
-
( DATA WRAPPER | TRANSLATOR ) <identifier> <alter options list>
alters options of data wrapper
Example:
ALTER [DATA WRAPPER|TRANSLATOR] foo OPTIONS (ADD x y)alter options list ::=
-
OPTIONS <lparen> ( <add set option> | <drop option> ) ( <comma> ( <add set option> | <drop option> ) )* <rparen>
a list of alterations to options
Example:
OPTIONS (ADD updatable true)add set option ::=
-
( ADD | SET ) <alter option pair>
add or set an option pair
Example:
ADD updatable truealter child options list ::=
-
OPTIONS <lparen> ( <add set child option> | <drop option> ) ( <comma> ( <add set child option> | <drop option> ) )* <rparen>
a list of alterations to options
Example:
OPTIONS (ADD updatable true)add set child option ::=
-
( ADD | SET ) <alter child option pair>
add or set an option pair
Example:
ADD updatable truealter child option pair ::=
-
<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )
Alter An option key/value pair.
Example:
'key' 'value'Import foreign schema ::=
-
IMPORT FOREIGN SCHEMA <identifier> FROM ( SERVER | REPOSITORY ) <identifier> INTO <identifier> ( <options clause> )?
imports schema metadata from server
Example:
IMPORT FOREIGN SCHEMA foo [LIMIT TO (x,y,z)|EXCEPT (x,y,z)] FROM SERVER barImport another Database ::=
imports another database into current database
Example:
IMPORT DATABASE <id> VERSION <string-val> [WITH ACCESS CONTROL]