Chapter 12. BNF for SQL Grammar

12.1. Reserved Keywords

Keyword	Usage
ADD	add set child option, add set option, ADD column, ADD constraint
ALL	standard aggregate function, CREATE POLICY, function, GRANT, query expression body, query term, Revoke GRANT, select clause, quantified comparison predicate
ALTER	alter, ALTER PROCEDURE , alterStatement, ALTER TABLE , grant type
AND	between predicate, boolean term, window frame
ANY	standard aggregate function, with role, quantified comparison predicate
ARRAY	ARRAY expression constructor
ARRAY_AGG	ordered aggregate function
AS	alter, ALTER PROCEDURE , ALTER TABLE , ALTER TRIGGER , array table, create procedure, create a domain or type alias, option namespace, create trigger, create view, delete statement, derived column, dynamic data statement, function, json table, loop statement, xml namespace element, object table, select derived column, table subquery, text table, table name, unescapedFunction, update statement, with list element, xml serialize, xml table
ASC	sort specification
ATOMIC	compound statement, for each row trigger action
AUTHENTICATED	with role
BEGIN	compound statement, for each row trigger action
BETWEEN	between predicate, window frame
BIGDECIMAL	simple data type
BIGINT	simple data type
BIGINTEGER	simple data type
BLOB	simple data type, xml serialize
BOOLEAN	simple data type
BOTH	function
BREAK	branching statement
BY	group by clause, order by clause, window specification
BYTE	simple data type
CALL	callable statement, call statement
CASE	case expression, searched case expression
CAST	function
CHAR	function, simple data type
CLOB	simple data type, xml serialize
COLUMN	ADD column, DROP column, ALTER TABLE , GRANT, Revoke GRANT
COMMIT	create temporary table
CONSTRAINT	GRANT, table constraint
CONTINUE	branching statement
CONVERT	function
CREATE	create procedure, create data wrapper, create database, create a domain or type alias, create foreign temp table, CREATE POLICY, create role , create schema, create server, create table, create temporary table, create trigger
CROSS	cross join
CUME_DIST	analytic aggregate function
CURRENT_DATE	function
CURRENT_TIME	function
CURRENT_TIMESTAMP	function
DATE	non numeric literal, simple data type
DAY	function
DECIMAL	simple data type
DECLARE	declare statement
DELETE	alter, ALTER TRIGGER , CREATE POLICY, create trigger, delete statement, grant type
DESC	sort specification
DISTINCT	standard aggregate function, function, is distinct, query expression body, query term, select clause
DOUBLE	simple data type
DROP	DROP column, drop option, Drop data wrapper, drop option, DROP POLICY, drop procedure, drop role , drop schema, drop server, drop table, drop table, grant type
EACH	for each row trigger action
ELSE	case expression, if statement, searched case expression
END	case expression, compound statement, for each row trigger action, searched case expression
ERROR	raise error statement
ESCAPE	match predicate, text table
EXCEPT	query expression body
EXEC	dynamic data statement, call statement
EXECUTE	dynamic data statement, grant type, call statement
EXISTS	exists predicate
FALSE	explain option, json table, non numeric literal
FETCH	fetch clause
FILTER	filter clause
FLOAT	simple data type
FOR	CREATE POLICY, for each row trigger action, function, json table column, text aggreate function, text table column, xml table column
FOREIGN	ALTER PROCEDURE , ALTER TABLE , create procedure, create data wrapper, create foreign or global temporary table, create foreign temp table, create schema, create server, Drop data wrapper, drop procedure, drop schema, drop table, foreign key, Import foreign schema, with role
FROM	delete statement, from clause, function, Import foreign schema, is distinct, Revoke GRANT
FULL	qualified table
FUNCTION	create procedure, drop procedure, GRANT, Revoke GRANT
GLOBAL	create foreign or global temporary table, drop table
GRANT	GRANT
GROUP	function, group by clause
HANDLER	create data wrapper
HAVING	having clause
HOUR	function
IF	if statement
IMMEDIATE	dynamic data statement
IMPORT	Import another Database, Import foreign schema
IN	function, procedure parameter, in predicate
INNER	qualified table
INOUT	procedure parameter
INSERT	alter, ALTER TRIGGER , CREATE POLICY, create trigger, function, insert statement, grant type
INTEGER	simple data type
INTERSECT	query term
INTO	dynamic data statement, Import foreign schema, insert statement, into clause
IS	is distinct, is null predicate
JOIN	cross join, make dep options, qualified table
LANGUAGE	GRANT, object table, Revoke GRANT
LATERAL	table subquery
LEADING	function
LEAVE	branching statement
LEFT	function, qualified table
LIKE	match predicate
LIKE_REGEX	like regex predicate
LIMIT	limit clause
LOCAL	create foreign temp table, create temporary table
LONG	simple data type
LOOP	loop statement
MAKEDEP	option clause, table primary
MAKEIND	option clause, table primary
MAKENOTDEP	option clause, table primary
MERGE	insert statement
MINUTE	function
MONTH	function
NO	make dep options, xml namespace element, text aggreate function, text table column, text table
NOCACHE	option clause
NOT	alter column options, between predicate, compound statement, table element, create a domain or type alias, view element, GRANT, is distinct, is null predicate, match predicate, boolean factor, procedure parameter, procedure result column, like regex predicate, in predicate, temporary table element
NULL	alter column options, table element, create a domain or type alias, view element, is null predicate, non numeric literal, procedure parameter, procedure result column, temporary table element, xml query
OF	alter, ALTER TRIGGER , create trigger
OFFSET	limit clause
ON	alter, ALTER TRIGGER , create foreign temp table, CREATE POLICY, create temporary table, create trigger, DROP POLICY, GRANT, loop statement, qualified table, Revoke GRANT, xml query
ONLY	fetch clause
OPTION	option clause
OPTIONS	alter child options list, alter options list, options clause
OR	boolean value expression
ORDER	GRANT, order by clause
OUT	procedure parameter
OUTER	qualified table
OVER	window specification
PARAMETER	ALTER PROCEDURE
PARTITION	window specification
PERCENT_RANK	analytic aggregate function
PRIMARY	create temporary table, inline constraint, primary key
PROCEDURE	alter, ALTER PROCEDURE , create procedure, CREATE POLICY, DROP POLICY, drop procedure, GRANT, Revoke GRANT
RANGE	window frame
REAL	simple data type
REFERENCES	foreign key
RETURN	assignment statement, return statement, data statement
RETURNS	create procedure
REVOKE	Revoke GRANT
RIGHT	function, qualified table
ROLLUP	group by clause
ROW	array table, fetch clause, for each row trigger action, limit clause, text table, window frame bound
ROWS	array table, create temporary table, fetch clause, limit clause, window frame
SECOND	function
SELECT	CREATE POLICY, grant type, select clause
SERVER	ALTER SERVER , create schema, create server, drop server, Import foreign schema
SET	add set child option, add set option, option namespace, update statement, set schema
SHORT	simple data type
SIMILAR	match predicate
SMALLINT	simple data type
SOME	standard aggregate function, quantified comparison predicate
SQLEXCEPTION	sql exception
SQLSTATE	sql exception
SQLWARNING	raise statement
STRING	dynamic data statement, simple data type, xml serialize
TABLE	ALTER TABLE , create procedure, create foreign or global temporary table, create foreign temp table, create temporary table, drop table, drop table, GRANT, query primary, Revoke GRANT, table subquery
TEMPORARY	create foreign or global temporary table, create foreign temp table, create temporary table, drop table, GRANT, Revoke GRANT
THEN	case expression, searched case expression
TIME	non numeric literal, simple data type
TIMESTAMP	non numeric literal, simple data type
TINYINT	simple data type
TO	rename column options, RENAME Table, CREATE POLICY, DROP POLICY, GRANT, match predicate
TRAILING	function
TRANSLATE	function
TRIGGER	alter, ALTER TRIGGER , create trigger
TRUE	explain option, json table, non numeric literal
UNION	cross join, query expression body
UNIQUE	other constraints, inline constraint
UNKNOWN	non numeric literal
UPDATE	alter, ALTER TRIGGER , CREATE POLICY, create trigger, dynamic data statement, grant type, update statement
USER	function
USING	CREATE POLICY, dynamic data statement
VALUES	query primary
VARBINARY	simple data type, xml serialize
VARCHAR	simple data type, xml serialize
VIRTUAL	ALTER PROCEDURE , ALTER TABLE , create procedure, create schema, create view, drop procedure, drop schema, drop table
WHEN	case expression, searched case expression
WHERE	filter clause, where clause
WHILE	while statement
WITH	assignment statement, create role , Import another Database, query expression, data statement
WITHIN	function
WITHOUT	assignment statement, data statement
WRAPPER	ALTER DATA WRAPPER , create data wrapper, create server, Drop data wrapper
XML	explain option, simple data type
XMLAGG	ordered aggregate function
XMLATTRIBUTES	xml attributes
XMLCAST	unescapedFunction
XMLCOMMENT	function
XMLCONCAT	function
XMLELEMENT	xml element
XMLEXISTS	xml query
XMLFOREST	xml forest
XMLNAMESPACES	xml namespaces
XMLPARSE	xml parse
XMLPI	function
XMLQUERY	xml query
XMLSERIALIZE	xml serialize
XMLTABLE	xml table
XMLTEXT	function
YEAR	function

12.2. Non-Reserved Keywords

Name	Usage
ACCESS	basicNonReserved, Import another Database
ACCESSPATTERN	basicNonReserved, other constraints
AFTER	alter, basicNonReserved, create trigger
ANALYZE	basicNonReserved, explain option
ARRAYTABLE	array table, basicNonReserved
AUTO_INCREMENT	alter column options, basicNonReserved, table element, view element
AVG	standard aggregate function, basicNonReserved
CHAIN	basicNonReserved, sql exception
COLUMNS	array table, basicNonReserved, json table, object table, text table, xml table
CONDITION	basicNonReserved, GRANT, Revoke GRANT
CONTENT	basicNonReserved, xml parse, xml serialize
CONTROL	basicNonReserved, Import another Database
COUNT	standard aggregate function, basicNonReserved
COUNT_BIG	standard aggregate function, basicNonReserved
CURRENT	basicNonReserved, window frame bound
DATA	ALTER DATA WRAPPER , basicNonReserved, create data wrapper, create server, Drop data wrapper
DATABASE	ALTER DATABASE , basicNonReserved, create database, Import another Database, use database
DEFAULT	xml namespace element, non-reserved identifier, object table column, post create column, procedure parameter, xml table column
DELIMITER	basicNonReserved, text aggreate function, text table
DENSE_RANK	analytic aggregate function, basicNonReserved
DISABLED	alter, ALTER TRIGGER , basicNonReserved
DOCUMENT	basicNonReserved, xml parse, xml serialize
DOMAIN	basicNonReserved, create a domain or type alias
EMPTY	basicNonReserved, xml query
ENABLED	alter, ALTER TRIGGER , basicNonReserved
ENCODING	basicNonReserved, text aggreate function, xml serialize
EPOCH	basicNonReserved, function
EVERY	standard aggregate function, basicNonReserved
EXCEPTION	compound statement, declare statement, non-reserved identifier
EXCLUDING	basicNonReserved, xml serialize
EXPLAIN	basicNonReserved, explain
EXTRACT	basicNonReserved, function
FIRST	basicNonReserved, fetch clause, sort specification
FOLLOWING	basicNonReserved, window frame bound
FORMAT	basicNonReserved, explain option
GEOGRAPHY	non-reserved identifier, simple data type
GEOMETRY	non-reserved identifier, simple data type
HEADER	basicNonReserved, text aggreate function, text table column, text table
INCLUDING	basicNonReserved, xml serialize
INDEX	other constraints, inline constraint, non-reserved identifier
INSTEAD	alter, ALTER TRIGGER , basicNonReserved, create trigger
JAAS	basicNonReserved, with role
JSON	non-reserved identifier, simple data type
JSONARRAY_AGG	basicNonReserved, ordered aggregate function
JSONOBJECT	basicNonReserved, json object
JSONTABLE	basicNonReserved, json table
KEY	basicNonReserved, create temporary table, foreign key, inline constraint, primary key
LAST	basicNonReserved, sort specification
LISTAGG	basicNonReserved, function
MASK	basicNonReserved, GRANT, Revoke GRANT
MAX	standard aggregate function, basicNonReserved, make dep options
MIN	standard aggregate function, basicNonReserved
NAME	basicNonReserved, function, xml element
NAMESPACE	basicNonReserved, option namespace
NEXT	basicNonReserved, fetch clause
NONE	basicNonReserved
NULLS	basicNonReserved, sort specification
OBJECT	non-reserved identifier, simple data type
OBJECTTABLE	basicNonReserved, object table
ORDINALITY	basicNonReserved, json table column, text table column, xml table column
PASSING	basicNonReserved, object table, xml query, xml query, xml table
PATH	basicNonReserved, json table column, xml table column
POLICY	basicNonReserved, CREATE POLICY, DROP POLICY
POSITION	basicNonReserved, function
PRECEDING	basicNonReserved, window frame bound
PRESERVE	basicNonReserved, create temporary table
PRIVILEGES	basicNonReserved, GRANT, Revoke GRANT
QUARTER	basicNonReserved, function
QUERYSTRING	basicNonReserved, querystring function
QUOTE	basicNonReserved, text aggreate function, text table
RAISE	basicNonReserved, raise statement
RANK	analytic aggregate function, basicNonReserved
RENAME	ALTER PROCEDURE , ALTER TABLE , basicNonReserved
REPOSITORY	basicNonReserved, Import foreign schema
RESULT	basicNonReserved, procedure parameter
ROLE	basicNonReserved, create role , drop role , with role
ROW_NUMBER	analytic aggregate function, basicNonReserved
SCHEMA	basicNonReserved, create schema, drop schema, GRANT, Import foreign schema, Revoke GRANT, set schema
SELECTOR	basicNonReserved, text table column, text table
SERIAL	alter column options, table element, view element, non-reserved identifier, temporary table element
SKIP	basicNonReserved, text table
SQL_TSI_DAY	basicNonReserved, time interval
SQL_TSI_FRAC_SECOND	basicNonReserved, time interval
SQL_TSI_HOUR	basicNonReserved, time interval
SQL_TSI_MINUTE	basicNonReserved, time interval
SQL_TSI_MONTH	basicNonReserved, time interval
SQL_TSI_QUARTER	basicNonReserved, time interval
SQL_TSI_SECOND	basicNonReserved, time interval
SQL_TSI_WEEK	basicNonReserved, time interval
SQL_TSI_YEAR	basicNonReserved, time interval
STDDEV_POP	standard aggregate function, basicNonReserved
STDDEV_SAMP	standard aggregate function, basicNonReserved
SUBSTRING	basicNonReserved, function
SUM	standard aggregate function, basicNonReserved
TEXT	basicNonReserved, explain option
TEXTAGG	basicNonReserved, text aggreate function
TEXTTABLE	basicNonReserved, text table
TIMESTAMPADD	basicNonReserved, function
TIMESTAMPDIFF	basicNonReserved, function
TO_BYTES	basicNonReserved, function
TO_CHARS	basicNonReserved, function
TRANSLATOR	ALTER DATA WRAPPER , basicNonReserved, create data wrapper, create server, Drop data wrapper
TRIM	basicNonReserved, function, text table column, text table
TYPE	alter column options, basicNonReserved, create data wrapper, create server
UNBOUNDED	basicNonReserved, window frame bound
UPSERT	basicNonReserved, insert statement
USAGE	basicNonReserved, GRANT, Revoke GRANT
USE	basicNonReserved, use database
VARIADIC	basicNonReserved, procedure parameter
VAR_POP	standard aggregate function, basicNonReserved
VAR_SAMP	standard aggregate function, basicNonReserved
VERSION	basicNonReserved, create database, create server, Import another Database, use database, xml serialize
VIEW	alter, ALTER TABLE , basicNonReserved, create view, drop table
WELLFORMED	basicNonReserved, xml parse
WIDTH	basicNonReserved, text table column
XMLDECLARATION	basicNonReserved, xml serialize
YAML	basicNonReserved, explain option

12.3. 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	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	XMLBINARY
XMLDOCUMENT	XMLITERATE	XMLVALIDATE

12.4. Tokens

Name	Definition	Usage
all in group identifier	<identifier> <period> <star>	all in group
binary string literal	"X" \| "x" "\'" (<hexit> <hexit>)+ "\'"	non numeric literal
colon	":"	make dep options, statement
comma	","	alter child options list, alter options list, ARRAY expression constructor, column list, create procedure, typed element list, CREATE POLICY, create table body, create temporary table, create view body, derived column list, sql exception, named parameter list, explain, expression list, from clause, function, GRANT, identifier list, json table, limit clause, nested expression, object table, option clause, options clause, order by clause, simple data type, query expression, query primary, querystring function, Revoke GRANT, select clause, set clause list, in predicate, text aggreate function, text table, xml attributes, xml element, xml query, xml forest, xml namespaces, xml query, xml table
concat_op	"\|\|"	common value expression
decimal numeric literal	(<digit>)* <period> <unsigned integer literal>	unsigned numeric literal
digit	\["0"\-"9"\]
dollar	"$"	parameter reference
double_amp_op	"&&"	common value expression
eq	"="	assignment statement, callable statement, declare statement, named parameter list, comparison operator, set clause list
escaped function	"{" "fn"	unsigned value expression primary
escaped join	"{" "oj"	table reference
escaped type	"{" ("d" \| "t" \| "ts" \| "b")	non numeric literal
approximate numeric literal	<digit> <period> <unsigned integer literal> \["e","E"\] (<plus> \| <minus>)? <unsigned integer literal>	unsigned numeric literal
ge	">="	comparison operator
gt	">"	named parameter list, comparison operator
hexit	\["a"\-"f","A"\-"F"\] \| <digit>
identifier	<quoted_id> (<period> <quoted_id>)*	create a domain or type alias, identifier, data type, Unqualified identifier, unsigned value expression primary
id_part	("" \| "@" \| "#" \| <letter>) (<letter> \| "" \| <digit>)*
lbrace	"{"	callable statement, match predicate
le	"⇐"	comparison operator
letter	\["a"\-"z","A"\-"Z"\] \| \["\u0153"\-"\ufffd"\]
lparen	"("	standard aggregate function, alter child options list, alter options list, analytic aggregate function, ARRAY expression constructor, array table, callable statement, column list, other constraints, create procedure, CREATE POLICY, create table body, create temporary table, create view body, explain, filter clause, function, group by clause, if statement, json object, json table, loop statement, make dep options, nested expression, object table, options clause, ordered aggregate function, simple data type, query primary, querystring function, in predicate, call statement, subquery, quantified comparison predicate, table subquery, table primary, text aggreate function, text table, unescapedFunction, while statement, window specification, with list element, xml attributes, xml element, xml query, xml forest, xml namespaces, xml parse, xml query, xml serialize, xml table
lsbrace	"["	ARRAY expression constructor, basic data type, data type, value expression primary
lt	"<"	comparison operator
minus	"-"	plus or minus
ne	"<>"	comparison operator
ne2	"!="	comparison operator
period	"."
plus	"+"	plus or minus
qmark	"?"	callable statement, parameter reference
quoted_id	<id_part> \| "\"" ("\"\"" \| ~\["\""\])+ "\""
rbrace	"}"	callable statement, match predicate, non numeric literal, table reference, unsigned value expression primary
rparen	")"	standard aggregate function, alter child options list, alter options list, analytic aggregate function, ARRAY expression constructor, array table, callable statement, column list, other constraints, create procedure, CREATE POLICY, create table body, create temporary table, create view body, explain, filter clause, function, group by clause, if statement, json object, json table, loop statement, make dep options, nested expression, object table, options clause, ordered aggregate function, simple data type, query primary, querystring function, in predicate, call statement, subquery, quantified comparison predicate, table subquery, table primary, text aggreate function, text table, unescapedFunction, while statement, window specification, with list element, xml attributes, xml element, xml query, xml forest, xml namespaces, xml parse, xml query, xml serialize, xml table
rsbrace	"]"	ARRAY expression constructor, basic data type, data type, value expression primary
semicolon	";"	delimited statement
slash	"/"	star or slash
star	"*"	standard aggregate function, dynamic data statement, select clause, star or slash
string literal	("N" \| "E")? "\'" ("\'\'" \| ~\["\'"\])* "\'"	string
unsigned integer literal	(<digit>)+	unsigned integer, unsigned numeric literal

12.5. Production Cross-Reference

Name	Usage
add set child option	alter child options list
add set option	alter options list
standard aggregate function	unescapedFunction
all in group	select sublist
alter	directly executable statement
ADD column	ALTER TABLE
ADD constraint	ALTER TABLE
alter child option pair	add set child option
alter child options list	alter column options
alter column options	ALTER PROCEDURE , ALTER TABLE
ALTER DATABASE	alterStatement
DROP column	ALTER TABLE
alter option pair	add set option
alter options list	ALTER DATABASE , ALTER PROCEDURE , ALTER SERVER , ALTER TABLE , ALTER DATA WRAPPER
ALTER PROCEDURE	alterStatement
rename column options	ALTER PROCEDURE , ALTER TABLE
RENAME Table	ALTER TABLE
ALTER SERVER	alterStatement
alterStatement	ddl statement
ALTER TABLE	alterStatement
ALTER DATA WRAPPER	alterStatement
ALTER TRIGGER	alterStatement
analytic aggregate function	unescapedFunction
ARRAY expression constructor	unsigned value expression primary
array table	table primary
assignment statement	delimited statement
assignment statement operand	assignment statement, declare statement
basicNonReserved	create a domain or type alias, non-reserved identifier, data type
between predicate	boolean primary
boolean primary	CREATE POLICY, filter clause, boolean factor
branching statement	delimited statement
callable statement
case expression	unsigned value expression primary
character	match predicate, text aggreate function, text table
column list	other constraints, create temporary table, foreign key, insert statement, primary key, with list element
common value expression	between predicate, boolean primary, comparison predicate, sql exception, function, is distinct, match predicate, like regex predicate, in predicate, text table
comparison predicate	boolean primary
boolean term	boolean value expression
boolean value expression	condition
compound statement	statement, directly executable statement
other constraints	table constraint
table element	ADD column, create table body
create procedure	ddl statement
create data wrapper	ddl statement
create database	ddl statement
create a domain or type alias	ddl statement
typed element list	array table, dynamic data statement
create foreign or global temporary table	create table
create foreign temp table	directly executable statement
option namespace	ddl statement
CREATE POLICY	ddl statement
create role	ddl statement
create schema	ddl statement
create server	ddl statement
create table	ddl statement
create table body	create foreign or global temporary table, create foreign temp table
create temporary table	directly executable statement
create trigger	ddl statement, directly executable statement
create view	create table
create view body	create view
view element	create view body
condition	expression, having clause, if statement, qualified table, searched case expression, where clause, while statement
cross join	joined table
ddl statement	ddl statement
declare statement	delimited statement
delete statement	assignment statement operand, directly executable statement
delimited statement	statement
derived column	derived column list, object table, querystring function, text aggreate function, xml attributes, xml query, xml query, xml table
derived column list	json object, xml forest
drop option	alter child options list
Drop data wrapper	ddl statement
drop option	alter options list
DROP POLICY	ddl statement
drop procedure	ddl statement
drop role	ddl statement
drop schema	ddl statement
drop server	ddl statement
drop table	directly executable statement
drop table	ddl statement
dynamic data statement	data statement
raise error statement	delimited statement
sql exception	assignment statement operand, exception reference
exception reference	sql exception, raise statement
named parameter list	callable statement, call statement
exists predicate	boolean primary
explain
explain option	explain
expression	standard aggregate function, ARRAY expression constructor, assignment statement operand, case expression, derived column, dynamic data statement, raise error statement, named parameter list, expression list, function, nested expression, object table column, ordered aggregate function, post create column, procedure parameter, querystring function, return statement, searched case expression, select derived column, set clause list, sort key, quantified comparison predicate, unescapedFunction, xml table column, xml element, xml parse, xml serialize
expression list	callable statement, other constraints, function, group by clause, query primary, call statement, window specification
fetch clause	limit clause
filter clause	function, unescapedFunction
for each row trigger action	alter, ALTER TRIGGER , create trigger
foreign key	table constraint
from clause	query
function	unescapedFunction, unsigned value expression primary
GRANT	ddl statement
group by clause	query
having clause	query
identifier	alter, alter child option pair, alter column options, ALTER DATABASE , DROP column, alter option pair, ALTER PROCEDURE , rename column options, RENAME Table, ALTER SERVER , ALTER TABLE , ALTER DATA WRAPPER , ALTER TRIGGER , array table, assignment statement, branching statement, callable statement, column list, compound statement, table element, create data wrapper, create database, typed element list, create foreign temp table, option namespace, CREATE POLICY, create schema, create trigger, view element, declare statement, delete statement, derived column, drop option, Drop data wrapper, drop option, DROP POLICY, drop procedure, drop role , drop schema, drop server, drop table, drop table, dynamic data statement, exception reference, named parameter list, foreign key, function, GRANT, identifier list, Import another Database, Import foreign schema, insert statement, into clause, json table column, json table, loop statement, xml namespace element, object table column, object table, option clause, option pair, procedure parameter, procedure result column, query primary, Revoke GRANT, select derived column, set clause list, statement, call statement, table subquery, table constraint, temporary table element, text aggreate function, text table column, text table, table name, update statement, use database, set schema, with list element, xml table column, xml element, xml serialize, xml table
identifier list	create schema, with role
if statement	statement
Import another Database	ddl statement
Import foreign schema	ddl statement
inline constraint	post create column
insert statement	assignment statement operand, directly executable statement
integer parameter	fetch clause, limit clause
unsigned integer	dynamic data statement, function, GRANT, integer parameter, make dep options, parameter reference, simple data type, text table column, text table, window frame bound
time interval	function
into clause	query
is distinct	boolean primary
is null predicate	boolean primary
joined table	table primary, table reference
json table column	json table
json object	function
json table	table primary
limit clause	query expression body
loop statement	statement
make dep options	option clause, table primary
match predicate	boolean primary
xml namespace element	xml namespaces
nested expression	unsigned value expression primary
non numeric literal	alter child option pair, alter option pair, option pair, value expression primary
non-reserved identifier	identifier, Unqualified identifier, unsigned value expression primary
boolean factor	boolean term
object table column	object table
object table	table primary
comparison operator	comparison predicate, quantified comparison predicate
option clause	callable statement, delete statement, insert statement, query expression body, call statement, update statement
option pair	options clause
options clause	create procedure, create data wrapper, create database, create schema, create server, create table body, create view, create view body, Import foreign schema, post create column, procedure parameter, procedure result column, table constraint
order by clause	function, ordered aggregate function, query expression body, text aggreate function, window specification
ordered aggregate function	unescapedFunction
parameter reference	unsigned value expression primary
basic data type	typed element list, json table column, object table column, data type, temporary table element, text table column, xml table column
data type	alter column options, table element, create procedure, create a domain or type alias, view element, declare statement, function, procedure parameter, procedure result column, unescapedFunction
simple data type	basic data type
numeric value expression	common value expression, value expression primary
plus or minus	alter child option pair, alter option pair, option pair, numeric value expression, value expression primary
post create column	table element, view element
primary key	table constraint
procedure parameter	create procedure
procedure result column	create procedure
qualified table	joined table
query	query primary
query expression	alter, ALTER TABLE , ARRAY expression constructor, assignment statement operand, create view, insert statement, loop statement, subquery, table subquery, directly executable statement, with list element
query expression body	query expression, query primary
query primary	query term
querystring function	function
query term	query expression body
raise statement	delimited statement
grant type	GRANT, Revoke GRANT
with role	create role
like regex predicate	boolean primary
return statement	delimited statement
Revoke GRANT	ddl statement
searched case expression	unsigned value expression primary
select clause	query
select derived column	select sublist
select sublist	select clause
set clause list	dynamic data statement, update statement
in predicate	boolean primary
sort key	sort specification
sort specification	order by clause
data statement	delimited statement
statement	alter, ALTER PROCEDURE , compound statement, create procedure, for each row trigger action, if statement, loop statement, while statement
call statement	assignment statement, subquery, table subquery, directly executable statement
string	character, create database, option namespace, create server, function, GRANT, Import another Database, json table column, json table, xml namespace element, non numeric literal, object table column, object table, text table column, text table, use database, xml table column, xml query, xml query, xml serialize, xml table
subquery	exists predicate, in predicate, quantified comparison predicate, unsigned value expression primary
quantified comparison predicate	boolean primary
table subquery	table primary
table constraint	ADD constraint, create table body, create view body
temporary table element	create temporary table
table primary	cross join, joined table
table reference	from clause, qualified table
text aggreate function	unescapedFunction
text table column	text table
text table	table primary
term	numeric value expression
star or slash	term
table name	table primary
unescapedFunction	unsigned value expression primary
Unqualified identifier	create procedure, create data wrapper, create foreign or global temporary table, create foreign temp table, create role , create server, create temporary table, create view
unsigned numeric literal	alter child option pair, alter option pair, option pair, value expression primary
unsigned value expression primary	integer parameter, value expression primary
update statement	assignment statement operand, directly executable statement
use database	ddl statement
set schema	ddl statement
directly executable statement	explain, data statement
value expression primary	array table, json table, term
where clause	delete statement, query, update statement
while statement	statement
window frame	window specification
window frame bound	window frame
window specification	unescapedFunction
with list element	query expression
xml attributes	xml element
xml table column	xml table
xml element	function
xml query	boolean primary
xml forest	function
xml namespaces	xml element, xml query, xml forest, xml query, xml table
xml parse	function
xml query	function
xml serialize	function
xml table	table primary

12.6. Productions

12.6.1. string ::=

<string literal>

A string literal value. Use '' to escape ' in the string.

Example:

'a string'

'it''s a string'

12.6.2. non-reserved identifier ::=

Allows non-reserved keywords to be parsed as identifiers

Example: SELECT COUNT FROM …

12.6.3. basicNonReserved ::=

12.6.4. Unqualified identifier ::=

Unqualified name of a single entity.

Example:

"tbl"

12.6.5. identifier ::=

Partial or full name of a single entity.

Example:

tbl.col

"tbl"."col"

12.6.6. 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 ... END

12.6.7. alter ::=

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 tbl

12.6.8. for each row trigger action ::=

FOR EACH ROW ( ( BEGIN ( ATOMIC )? ( <statement> )* END ) | <statement> )

Defines an action to perform on each row.

Example:

FOR EACH ROW BEGIN ATOMIC ... END

12.6.9. explain ::=

EXPLAIN ( <lparen> <explain option> ( <comma> <explain option> )* <rparen> )? <directly executable statement>

Returns the query plan for the statement

Example: EXPLAIN select 1

12.6.10. explain option ::=

( ANALYZE ( TRUE | FALSE )? )
( FORMAT ( XML | TEXT | YAML )? )

Option for the explain statement

Example: FORMAT YAML

12.6.11. directly executable statement ::=

A statement that can be executed at runtime.

Example:

SELECT * FROM tbl

12.6.12. drop table ::=

DROP TABLE <identifier>

Drop the given table.

Example:

DROP TABLE #temp

12.6.13. create temporary table ::=

CREATE ( LOCAL )? TEMPORARY TABLE <Unqualified 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)

12.6.14. temporary table element ::=

<identifier> ( <basic data type> | SERIAL ) ( NOT NULL )?

Defines a temporary table column.

Example:

col string NOT NULL

12.6.15. raise error statement ::=

ERROR <expression>

Raises an error with the given message.

Example:

ERROR 'something went wrong'

12.6.16. raise statement ::=

RAISE ( SQLWARNING )? <exception reference>

Raises an error or warning with the given message.

Example:

RAISE SQLEXCEPTION 'something went wrong'

12.6.17. exception reference ::=

<identifier>
<sql exception>

a reference to an exception

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

12.6.18. sql 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', 2

12.6.19. statement ::=

( ( <identifier> <colon> )? ( <loop statement> | <while statement> | <compound statement> ) )
<if statement> | <delimited statement>

A procedure statement.

Example:

IF (x = 5) BEGIN ... END

12.6.20. delimited 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;

12.6.21. compound statement ::=

BEGIN ( ( NOT )? ATOMIC )? ( <statement> )* ( EXCEPTION <identifier> ( <statement> )* )? END

A procedure statement block contained in BEGIN END.

Example:

BEGIN NOT ATOMIC ... END

12.6.22. branching statement ::=

( ( BREAK | CONTINUE ) ( <identifier> )? )
( LEAVE <identifier> )

A procedure branching control statement, which typically specifies a label to return control to.

Example:

BREAK x

12.6.23. return statement ::=

RETURN ( <expression> )?

A return statement.

Example:

RETURN 1

12.6.24. while statement ::=

WHILE <lparen> <condition> <rparen> <statement>

A procedure while statement that executes until its condition is false.

Example:

WHILE (var) BEGIN ... END

12.6.25. loop 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 ... END

12.6.26. if statement ::=

IF <lparen> <condition> <rparen> <statement> ( ELSE <statement> )?

A procedure loop statement that executes over the given cursor.

Example:

IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END

12.6.27. declare 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'

12.6.28. assignment statement ::=

<identifier> <eq> ( <assignment statement operand> | ( <call statement> ( ( WITH | WITHOUT ) RETURN )? ) )

Assigns a variable a value in a procedure.

Example:

x = 'b'

12.6.29. assignment statement operand ::=

A value or command that can be used in an assignment. {note}All assignments except for expression are deprecated.{note}

12.6.30. 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.

12.6.31. dynamic 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 #temp

12.6.32. set clause list ::=

<identifier> <eq> <expression> ( <comma> <identifier> <eq> <expression> )*

A list of value assignments.

Example:

col1 = 'x', col2 = 'y' ...

12.6.33. typed element list ::=

<identifier> <basic data type> ( <comma> <identifier> <basic data type> )*

A list of typed elements.

Example:

col1 string, col2 integer ...

12.6.34. 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}

12.6.35. 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)

12.6.36. named parameter list ::=

( <identifier> <eq> ( <gt> )? <expression> ( <comma> <identifier> <eq> ( <gt> )? <expression> )* )

A list of named parameters.

Example:

param1 => 'x', param2 => 1

12.6.37. insert 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)

12.6.38. expression list ::=

<expression> ( <comma> <expression> )*

A list of expressions.

Example:

col1, 'a', ...

12.6.39. 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 = 1

12.6.40. delete statement ::=

DELETE FROM <identifier> ( ( AS )? <identifier> )? ( <where clause> )? ( <option clause> )?

Delete rows from the given target.

Example:

DELETE FROM tbl WHERE col2 = 1

12.6.41. query expression ::=

( WITH <with list element> ( <comma> <with list element> )* )? <query expression body>

A declarative query for data.

Example:

SELECT * FROM tbl WHERE col2 = 1

12.6.42. with 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)

12.6.43. 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 1

12.6.44. query term ::=

<query primary> ( INTERSECT ( ALL | DISTINCT )? <query primary> )*

Used to establish INTERSECT precedence.

Example:

SELECT * FROM tbl

SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2

12.6.45. query 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 tbl

SELECT * FROM tbl1

12.6.46. query ::=

<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 col1

12.6.47. into clause ::=

INTO <identifier>

Used to direct the query into a table. {note}This is deprecated. Use INSERT INTO with a query expression instead.{note}

Example:

INTO tbl

12.6.48. select 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, c

12.6.49. select sublist ::=

<select derived column>
<all in group >

An element in the select clause

Example:

tbl.*

tbl.col AS x

12.6.50. select 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 x

12.6.51. derived column ::=

( <expression> ( AS <identifier> )? )

An optionally named expression.

Example:

tbl.col AS x

12.6.52. all in group ::=

<all in group identifier>

A select sublist that can select all columns from the given group.

Example:

tbl.*

12.6.53. ordered 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 col2

ARRAY_AGG(col1)

12.6.54. 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)

12.6.55. standard aggregate function ::=

( ( COUNT | COUNT_BIG ) <lparen> <star> <rparen> )
( ( COUNT | COUNT_BIG | SUM | AVG | MIN | MAX | EVERY | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | SOME | ANY ) <lparen> ( DISTINCT | ALL )? <expression> <rparen> )

A standard aggregate function.

Example:

COUNT(*)

12.6.56. analytic aggregate function ::=

( ROW_NUMBER | RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST ) <lparen> <rparen>

An analytic aggregate function.

Example:

ROW_NUMBER()

12.6.57. filter clause ::=

FILTER <lparen> WHERE <boolean primary> <rparen>

An aggregate filter clause applied prior to accumulating the value.

Example:

FILTER (WHERE col1='a')

12.6.58. from clause ::=

FROM ( <table reference> ( <comma> <table reference> )* )

A query from clause containing a list of table references.

Example:

FROM a, b

FROM a right outer join b, c, d join e".</p>

12.6.59. table reference ::=

( <escaped join> <joined table> <rbrace> )
<joined table>

An optionally escaped joined table.

Example:

a inner join b

12.6.60. joined table ::=

<table primary> ( <cross join> | <qualified table> )*

A table or join.

Example:

a inner join b

12.6.61. cross join ::=

( ( CROSS | UNION ) JOIN <table primary> )

A cross join.

Example:

a CROSS JOIN b

12.6.62. qualified table ::=

( ( ( RIGHT ( OUTER )? ) | ( LEFT ( OUTER )? ) | ( FULL ( OUTER )? ) | INNER )? JOIN <table reference> ON <condition> )

An INNER or OUTER join.

Example:

a inner join b

12.6.63. table primary ::=

( <text table> | <array table> | <json 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:

12.6.64. make dep options ::=

( <lparen> ( MAX <colon> <unsigned integer> )? ( ( NO )? JOIN )? <rparen> )?

options for the make dep hint

Example:

(min:10000)

12.6.65. xml 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)

12.6.66. array table ::=

ARRAYTABLE <lparen> ( ROW | ROWS )? <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 y

12.6.67. json table ::=

JSONTABLE <lparen> <value expression primary> <comma> <string> ( <comma> ( TRUE | FALSE ) )? COLUMNS <json table column> ( <comma> <json table column> )* <rparen> ( AS )? <identifier>

The JSONTABLE table function creates tabular results from JSON. It can be used as a nested table reference.

Example:

JSONTABLE (col1, '$..book', false COLUMNS x STRING) AS y

12.6.68. json table column ::=

<identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( PATH <string> )? ) )

json table column.

Example:

col FOR ORDINALITY

12.6.69. text 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 y

12.6.70. text 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 6

12.6.71. xml 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)

12.6.72. 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)

12.6.73. 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 X

12.6.74. object table column ::=

<identifier> <basic data type> <string> ( DEFAULT <expression> )?

object table column.

Example:

y integer 'teiid_row_number'

12.6.75. 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 X

12.6.76. xml table column ::=

<identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( DEFAULT <expression> )? ( PATH <string> )? ) )

XML table column.

Example:

y FOR ORDINALITY

12.6.77. unsigned integer ::=

<unsigned integer literal>

An unsigned interger value.

Example:

12.6.78. table subquery ::=

( TABLE | LATERAL )? <lparen> ( <query expression> | <call statement> ) <rparen> ( AS )? <identifier>

A table defined by a subquery.

Example:

(SELECT * FROM tbl) AS x

12.6.79. table name ::=

( <identifier> ( ( AS )? <identifier> )? )

A table named in the FROM clause.

Example:

tbl AS x

12.6.80. where clause ::=

WHERE <condition>

Specifies a search condition

Example:

WHERE x = 'a'

12.6.81. condition ::=

<boolean value expression>

A boolean expression.

12.6.82. boolean value expression ::=

<boolean term> ( OR <boolean term> )*

An optionally ORed boolean expression.

12.6.83. boolean term ::=

<boolean factor> ( AND <boolean factor> )*

An optional ANDed boolean factor.

12.6.84. boolean factor ::=

( NOT )? <boolean primary>

A boolean factor.

Example:

NOT x = 'a'

12.6.85. boolean primary ::=

( <common value expression> ( <between predicate> | <match predicate> | <like regex predicate> | <in predicate> | <is null predicate> | <quantified comparison predicate> | <comparison predicate> | <is distinct> )? )
<exists predicate>
<xml query>

A boolean predicate or simple expression.

Example:

col LIKE 'a%'

12.6.86. comparison operator ::=

<eq>
<ne>
<ne2>
<lt>
<le>
<gt>
<ge>

A comparison operator.

Example:

12.6.87. is distinct ::=

IS ( NOT )? DISTINCT FROM <common value expression>

Is Distinct Right Hand Side

Example:

IS DISTINCT FROM expression

12.6.88. comparison predicate ::=

<comparison operator> <common value expression>

A value comparison.

Example:

= 'a'

12.6.89. subquery ::=

<lparen> ( <query expression> | <call statement> ) <rparen>

A subquery.

Example:

(SELECT * FROM tbl)

12.6.90. quantified comparison predicate ::=

<comparison operator> ( ANY | SOME | ALL ) ( <subquery> | ( <lparen> <expression> <rparen> ) )

A subquery comparison.

Example:

= ANY (SELECT col FROM tbl)

12.6.91. match predicate ::=

( NOT )? ( LIKE | ( SIMILAR TO ) ) <common value expression> ( ESCAPE <character> | ( <lbrace> ESCAPE <character> <rbrace> ) )?

Matches based upon a pattern.

Example:

LIKE 'a_'

12.6.92. like regex predicate ::=

( NOT )? LIKE_REGEX <common value expression>

A regular expression match.

Example:

LIKE_REGEX 'a.*b'

12.6.93. character ::=

<string>

A single character.

Example:

'a'

12.6.94. between predicate ::=

( NOT )? BETWEEN <common value expression> AND <common value expression>

A comparison between two values.

Example:

BETWEEN 1 AND 5

12.6.95. is null predicate ::=

IS ( NOT )? NULL

A null test.

Example:

IS NOT NULL

12.6.96. in predicate ::=

( NOT )? IN ( <subquery> | ( <lparen> <common value expression> ( <comma> <common value expression> )* <rparen> ) )

A comparison with multiple values.

Example:

IN (1, 5)

12.6.97. exists predicate ::=

EXISTS <subquery>

A test if rows exist.

Example:

EXISTS (SELECT col FROM tbl)

12.6.98. group by clause ::=

GROUP BY ( ROLLUP <lparen> <expression list> <rparen> | <expression list> )

Defines the grouping columns

Example:

GROUP BY col1, col2

12.6.99. having clause ::=

HAVING <condition>

Search condition applied after grouping.

Example:

HAVING max(col1) = 5

12.6.100. order by clause ::=

ORDER BY <sort specification> ( <comma> <sort specification> )*

Specifices row ordering.

Example:

ORDER BY x, y DESC

12.6.101. sort specification ::=

<sort key> ( ASC | DESC )? ( NULLS ( FIRST | LAST ) )?

Defines how to sort on a particular expression

Example:

col1 NULLS FIRST

12.6.102. sort key ::=

<expression>

A sort expression.

Example:

col1

12.6.103. integer parameter ::=

A literal integer or parameter reference to an integer.

Example:

12.6.104. limit clause ::=

( LIMIT <integer parameter> ( ( <comma> <integer parameter> ) | ( OFFSET <integer parameter> ) )? )
( OFFSET <integer parameter> ( ROW | ROWS ) ( <fetch clause> )? )
<fetch clause>

Limits and/or offsets the resultant rows.

Example:

LIMIT 2

12.6.105. fetch clause ::=

FETCH ( FIRST | NEXT ) ( <integer parameter> )? ( ROW | ROWS ) ONLY

ANSI limit.

Example:

FETCH FIRST 1 ROWS ONLY

12.6.106. option 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 tbl

12.6.107. expression ::=

<condition>

A value.

Example:

col1

12.6.108. common value expression ::=

( <numeric value expression> ( ( <double_amp_op> | <concat_op> ) <numeric value expression> )* )

Establishes the precedence of concat.

Example:

'a' || 'b'

12.6.109. numeric value expression ::=

( <term> ( <plus or minus> <term> )* )

Example:

1 + 2

12.6.110. plus or minus ::=

<plus>
<minus>

The + or - operator.

Example:

12.6.111. term ::=

( <value expression primary> ( <star or slash> <value expression primary> )* )

A numeric term

Example:

1 * 2

12.6.112. star or slash ::=

<star>
<slash>

The * or / operator.

Example:

12.6.113. value expression primary ::=

<non numeric literal>
( <plus or minus> )? ( <unsigned numeric literal> | ( <unsigned value expression primary> ( <lsbrace> <numeric value expression> <rsbrace> )* ) )

A simple value expression.

Example:

+col1

12.6.114. parameter reference ::=

<qmark>
( <dollar> <unsigned integer> )

A parameter reference to be bound later.

Example:

12.6.115. unescapedFunction ::=

( ( <text aggreate function> | <standard aggregate function> | <ordered aggregate function> ) ( <filter clause> )? ( <window specification> )? ) | ( <analytic aggregate function> ( <filter clause> )? <window specification> ) | ( <function> ( <window specification> )? )
( XMLCAST <lparen> <expression> AS <data type> <rparen> )

12.6.116. nested expression ::=

( <lparen> ( <expression> ( <comma> <expression> )* )? ( <comma> )? <rparen> )

An expression nested in parens

Example:

(1)

12.6.117. unsigned value expression primary ::=

<parameter reference>
( <escaped function> <function> <rbrace> )
<unescapedFunction>
<identifier> | <non-reserved identifier>
<subquery>
<nested expression>
<ARRAY expression constructor>
<searched case expression>
<case expression>

An unsigned simple value expression.

Example:

col1

12.6.118. ARRAY expression constructor ::=

ARRAY ( ( <lsbrace> ( <expression> ( <comma> <expression> )* )? <rsbrace> ) | ( <lparen> <query expression> <rparen> ) )

Creates and array of the given expressions.

Example:

ARRAY[1,2]

12.6.119. window specification ::=

OVER <lparen> ( PARTITION BY <expression list> )? ( <order by clause> )? ( <window frame> )? <rparen>

The window specification for an analytical or windowed aggregate function.

Example:

OVER (PARTION BY col1)

12.6.120. window frame ::=

( RANGE | ROWS ) ( ( BETWEEN <window frame bound> AND <window frame bound> ) | <window frame bound> )

Defines the mode, start, and optionally end of the window frame

Example:

RANGE UNBOUNDED PRECEDING

12.6.121. window frame bound ::=

( ( UNBOUNDED | <unsigned integer> ) ( FOLLOWING | PRECEDING ) )
( CURRENT ROW )

Defines the start or end of a window frame

Example:

CURRENT ROW

12.6.122. 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 2

12.6.123. searched 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 2

12.6.124. function ::=

( CONVERT <lparen> <expression> <comma> <data type> <rparen> )
( CAST <lparen> <expression> AS <data type> <rparen> )
( SUBSTRING <lparen> <expression> ( ( FROM <expression> ( FOR <expression> )? ) | ( <comma> <expression list> ) ) <rparen> )
( EXTRACT <lparen> ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | QUARTER | EPOCH ) 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> )
<querystring function>
( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT | XMLTEXT ) <lparen> ( <expression list> )? <rparen> )
( ( TRANSLATE | INSERT ) <lparen> ( <expression list> )? <rparen> )
<xml parse>
<xml element>
( XMLPI <lparen> ( ( NAME )? <identifier> ) ( <comma> <expression> )? <rparen> )
<xml forest>
<json object>
<xml serialize>
<xml query>
( POSITION <lparen> <common value expression> IN <common value expression> <rparen> )
( LISTAGG <lparen> <expression> ( <comma> <string> )? <rparen> WITHIN GROUP <lparen> <order by clause> <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)

12.6.125. xml parse ::=

XMLPARSE <lparen> ( DOCUMENT | CONTENT ) <expression> ( WELLFORMED )? <rparen>

Parses the given value as XML.

Example:

XMLPARSE(DOCUMENT doc WELLFORMED)

12.6.126. 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)

12.6.127. xml element ::=

XMLELEMENT <lparen> ( ( NAME )? <identifier> ) ( <comma> <xml namespaces> )? ( <comma> <xml attributes> )? ( <comma> <expression> )* <rparen>

Creates an XML element.

Example:

XMLELEMENT(NAME "root", child)

12.6.128. xml attributes ::=

XMLATTRIBUTES <lparen> <derived column> ( <comma> <derived column> )* <rparen>

Creates attributes for the containing element.

Example:

XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)

12.6.129. json object ::=

JSONOBJECT <lparen> <derived column list> <rparen>

Produces a JSON object containing name value pairs.

Example:

JSONOBJECT(col1 AS val1, col2 AS val2)

12.6.130. derived column list ::=

<derived column> ( <comma> <derived column> )*

a list of name value pairs

Example:

col1 AS val1, col2 AS val2

12.6.131. xml 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)

12.6.132. xml namespaces ::=

XMLNAMESPACES <lparen> <xml namespace element> ( <comma> <xml namespace element> )* <rparen>

Defines XML namespace URI/prefix combinations

Example:

XMLNAMESPACES('http://foo' AS foo)

12.6.133. xml namespace element ::=

An xml namespace

Example:

NO DEFAULT

12.6.134. simple data type ::=

( STRING ( <lparen> <unsigned integer> <rparen> )? )
( VARCHAR ( <lparen> <unsigned integer> <rparen> )? )
BOOLEAN
BYTE
TINYINT
SHORT
SMALLINT
( CHAR ( <lparen> <unsigned integer> <rparen> )? )
INTEGER
LONG
BIGINT
( BIGINTEGER ( <lparen> <unsigned integer> <rparen> )? )
FLOAT
REAL
DOUBLE
( BIGDECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
( DECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
DATE
TIME
( TIMESTAMP ( <lparen> <unsigned integer> <rparen> )? )
( OBJECT ( <lparen> <unsigned integer> <rparen> )? )
( BLOB ( <lparen> <unsigned integer> <rparen> )? )
( CLOB ( <lparen> <unsigned integer> <rparen> )? )
JSON
( VARBINARY ( <lparen> <unsigned integer> <rparen> )? )
GEOMETRY
GEOGRAPHY
XML

A non-collection data type.

Example:

STRING

12.6.135. basic data type ::=

<simple data type> ( <lsbrace> <rsbrace> )*

A data type.

Example:

STRING[]

12.6.136. data type ::=

<basic data type>
( ( <identifier> | <basicNonReserved> ) ( <lsbrace> <rsbrace> )* )

A data type.

Example:

STRING[]

12.6.137. time interval ::=

A time interval keyword.

Example:

SQL_TSI_HOUR

12.6.138. non numeric literal ::=

<string>
<binary string literal>
FALSE
TRUE
UNKNOWN
NULL
( <escaped type> <string> <rbrace> )
( ( DATE | TIME | TIMESTAMP ) <string> )

An escaped or simple non numeric literal.

Example:

'a'

12.6.139. unsigned numeric literal ::=

An unsigned numeric literal value.

Example:

1.234

12.6.140. ddl statement ::=

<create table> ( <create table> | <create procedure> )?
<option namespace>
<alterStatement>
<create trigger>
<create a domain or type alias>
<create server>
<create role >
<drop role >
<GRANT>
<Revoke GRANT>
<CREATE POLICY>
<DROP POLICY>
<drop server>
<drop table>
<Import foreign schema>
<Import another Database>
<create database>
<use database>
<drop schema>
<set schema>
<create schema>
<create procedure> ( <ddl statement> )?
<create data wrapper>
<Drop data wrapper>
<drop procedure>

A data definition statement.

Example:

CREATE FOREIGN TABLE X (Y STRING)

12.6.141. 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 foo

12.6.142. create database ::=

CREATE DATABASE <identifier> ( VERSION <string> )? ( <options clause> )?

create a new database

Example:

CREATE DATABASE foo OPTIONS(x 'y')

12.6.143. use database ::=

USE DATABASE <identifier> ( VERSION <string> )?

database into working context

Example:

USE DATABASE foo

12.6.144. 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);

12.6.145. drop schema ::=

DROP ( VIRTUAL | FOREIGN )? SCHEMA <identifier>

drop a schema in database

Example:

DROP SCHEMA foo

12.6.146. set schema ::=

SET SCHEMA <identifier>

set the schema for subsequent ddl statements

Example:

SET SCHEMA foo

12.6.147. create a domain or type alias ::=

CREATE DOMAIN ( <identifier> | <basicNonReserved> ) ( AS )? <data type> ( NOT NULL )?

creates a named type with optional constraints

Example:

CREATE DOMAIN my_type AS INTEGER NOT NULL

12.6.148. create data wrapper ::=

CREATE FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <Unqualified identifier> ( ( TYPE | HANDLER ) <identifier> )? ( <options clause> )?

Defines a translator; use the options to override the translator properties.

Example:

CREATE FOREIGN DATA WRAPPER wrapper OPTIONS (x true)

12.6.149. Drop data wrapper ::=

DROP FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <identifier>

Deletes a translator

Example:

DROP FOREIGN DATA WRAPPER wrapper

12.6.150. create role ::=

CREATE ROLE <Unqualified identifier> ( WITH <with role> )?

Defines data role for the database

Example:

CREATE ROLE lowly WITH FOREIGN ROLE "role"

12.6.151. with role ::=

( ANY AUTHENTICATED )
( ( JAAS | FOREIGN ) ROLE <identifier list> )

12.6.152. drop role ::=

DROP ROLE <identifier>

Removes data role for the database

Example:

DROP ROLE <data-role>

12.6.153. CREATE POLICY ::=

CREATE POLICY <identifier> ON ( ( <identifier> ( FOR ( ALL | ( ( SELECT | INSERT | UPDATE | DELETE ) ( <comma> ( SELECT | INSERT | UPDATE | DELETE ) )* ) ) )? ) | ( PROCEDURE <identifier> ( FOR ALL )? ) ) TO <identifier> USING <lparen> <boolean primary> <rparen>

CREATE row level policy

Example:

CREATE POLICY pname ON tbl FOR SELECT,INSERT TO role USING col = user();

12.6.154. DROP POLICY ::=

DROP POLICY <identifier> ON ( <identifier> | ( PROCEDURE <identifier> ) ) TO <identifier>

DROP row level policy

Example:

----DROP POLICY pname ON tbl TO role
----

12.6.155. GRANT ::=

GRANT ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION ( ( NOT )? CONSTRAINT )? <string> )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION ( ( NOT )? 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 role

12.6.156. Revoke 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 role

12.6.157. create server ::=

CREATE SERVER <Unqualified identifier> ( TYPE <string> )? ( VERSION <string> )? FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <Unqualified identifier> ( <options clause> )?

Defines a connection to a source

Example:

CREATE SERVER "h2-connector" FOREIGN DATA WRAPPER h2 OPTIONS ("resource-name" 'java:/accounts-ds');

12.6.158. drop server ::=

DROP SERVER <identifier>

Defines dropping connection to foreign source

Example:

DROP SERVER server_name

12.6.159. 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 STRING

12.6.160. drop procedure ::=

DROP ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) <identifier>

Drops a table or view.

Example:

DROP FOREIGN TABLE table-name

12.6.161. procedure parameter ::=

( IN | OUT | INOUT | VARIADIC )? <identifier> <data type> ( NOT NULL )? ( RESULT )? ( DEFAULT <expression> )? ( <options clause> )?

A procedure or function parameter

Example:

OUT x INTEGER

12.6.162. procedure result column ::=

<identifier> <data type> ( NOT NULL )? ( <options clause> )?

A procedure result column.

Example:

x INTEGER

12.6.163. create table ::=

CREATE ( <create view> | <create foreign or global temporary table> )

Defines a table or view.

Example:

CREATE VIEW vw AS SELECT 1

12.6.164. create 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)

12.6.165. create view ::=

( VIRTUAL )? VIEW <Unqualified identifier> ( <create view body> | ( <options clause> )? ) AS <query expression>

Defines a view.

Example:

VIEW vw AS SELECT 1

12.6.166. drop table ::=

DROP ( ( FOREIGN TABLE ) | ( ( VIRTUAL )? VIEW ) | ( GLOBAL TEMPORARY TABLE ) ) <identifier>

Drops a table or view.

Example:

DROP VIEW name

12.6.167. create foreign temp table ::=

CREATE ( LOCAL )? FOREIGN TEMPORARY TABLE <Unqualified identifier> <create table body> ON <identifier>

Defines a foreign temp table

Example:

CREATE FOREIGN TEMPORARY TABLE t (x string) ON z

12.6.168. create table body ::=

<lparen> <table element> ( <comma> ( <table constraint> | <table element> ) )* <rparen> ( <options clause> )?

Defines a table.

Example:

(x string) OPTIONS (CARDINALITY 100)

12.6.169. create view body ::=

<lparen> <view element> ( <comma> ( <table constraint> | <view element> ) )* <rparen> ( <options clause> )?

Defines a view.

Example:

(x) OPTIONS (CARDINALITY 100)

12.6.170. 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)

12.6.171. 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)

12.6.172. primary key ::=

PRIMARY KEY <column list>

Defines the primary key.

Example:

PRIMARY KEY (a, b)

12.6.173. other constraints ::=

( ( UNIQUE | ACCESSPATTERN ) <column list> )
( INDEX <lparen> <expression list> <rparen> )

Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.

Example:

UNIQUE (a)

12.6.174. column list ::=

<lparen> <identifier> ( <comma> <identifier> )* <rparen>

A list of column names.

Example:

(a, b)

12.6.175. table element ::=

<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) <post create column>

Defines a table column.

Example:

x INTEGER NOT NULL

12.6.176. view element ::=

<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) )? <post create column>

Defines a view column with optional type.

Example:

x INTEGER NOT NULL

12.6.177. post create column ::=

( <inline constraint> )? ( DEFAULT <expression> )? ( <options clause> )?

Common options trailing a column

Example:

PRIMARY KEY

12.6.178. inline constraint ::=

Defines a constraint on a single column

Example:

x INTEGER PRIMARY KEY

12.6.179. options clause ::=

OPTIONS <lparen> <option pair> ( <comma> <option pair> )* <rparen>

A list of statement options.

Example:

OPTIONS ('x' 'y', 'a' 'b')

12.6.180. option pair ::=

<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )

An option key/value pair.

Example:

'key' 'value'

12.6.181. alter option pair ::=

<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )

Alter An option key/value pair.

Example:

'key' 'value'

12.6.182. alterStatement ::=

ALTER ( <ALTER TABLE > | <ALTER PROCEDURE > | <ALTER TRIGGER > | <ALTER SERVER > | <ALTER DATA WRAPPER > | <ALTER DATABASE > )

12.6.183. 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 COLUMN x xml

12.6.184. RENAME Table ::=

TO <identifier>

alters table name

Example:

ALTER TABLE foo RENAME TO BAR;

12.6.185. ADD constraint ::=

ADD <table constraint>

alters table and adds a constraint

Example:

ADD PRIMARY KEY (ID)

12.6.186. ADD column ::=

ADD COLUMN <table element>

alters table and adds a column

Example:

ADD COLUMN bar type OPTIONS (ADD updatable true)

12.6.187. DROP column ::=

DROP COLUMN <identifier>

alters table and adds a column

Example:

DROP COLUMN bar

12.6.188. 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)

12.6.189. rename column options ::=

<identifier> TO <identifier>

renames either a table column or procedure’s parameter name

Example:

RENAME COLUMN bar TO foo

12.6.190. ALTER 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 OPTIONS (ADD x y)

12.6.191. ALTER TRIGGER ::=

TRIGGER ON <identifier> INSTEAD OF ( INSERT | UPDATE | DELETE ) ( AS <for each row trigger action> | ENABLED | DISABLED )

alters options of table triggers

Example:

ALTER TRIGGER ON vw INSTEAD OF INSERT ENABLED

12.6.192. ALTER SERVER ::=

SERVER <identifier> <alter options list>

alters options of database

Example:

ALTER SERVER foo OPTIONS (ADD x y)

12.6.193. ALTER DATA WRAPPER ::=

( ( DATA WRAPPER ) | TRANSLATOR ) <identifier> <alter options list>

alters options of data wrapper

Example:

ALTER DATA WRAPPER foo OPTIONS (ADD x y)

12.6.194. ALTER DATABASE ::=

DATABASE <identifier> <alter options list>

alters options of database

Example:

ALTER DATABASE foo OPTIONS (ADD x y)

12.6.195. 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)

12.6.196. drop option ::=

DROP <identifier>

drop option

Example:

DROP updatable

12.6.197. add set option ::=

( ADD | SET ) <alter option pair>

add or set an option pair

Example:

ADD updatable true

12.6.198. alter 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)

12.6.199. drop option ::=

DROP <identifier>

drop option

Example:

DROP updatable

12.6.200. add set child option ::=

( ADD | SET ) <alter child option pair>

add or set an option pair

Example:

ADD updatable true

12.6.201. alter child option pair ::=

<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )

Alter An option key/value pair.

Example:

'key' 'value'

12.6.202. 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 FROM SERVER bar

12.6.203. Import another Database ::=

IMPORT DATABASE <identifier> VERSION <string> ( WITH ACCESS CONTROL )?

imports another database into current database

Example:

IMPORT DATABASE vdb VERSION '1.2.3' WITH ACCESS CONTROL]

12.6.204. identifier list ::=

<identifier> ( <comma> <identifier> )*