← mskql

mskql

SQL Grammar Reference

← Back to documentation  ·  github.com/martinsk/mskql

Every production rule accepted by mskql’s recursive-descent parser. Unsupported features are noted inline.

1. Top-Level Statements

statement ::=
   select_stmt
  | insert_stmt
  | update_stmt
  | delete_stmt
  | create_table_stmt
  | drop_table_stmt
  | create_index_stmt
  | drop_index_stmt
  | create_type_stmt
  | drop_type_stmt
  | create_sequence_stmt
  | drop_sequence_stmt
  | create_view_stmt
  | drop_view_stmt
  | alter_table_stmt
  | truncate_stmt
  | copy_stmt
  | transaction_stmt

2. SELECT

select_stmt ::=
   [ with_clause ]
   SELECT [ DISTINCT [ ON ( column_list ) ] ]
   select_list
   [ FROM from_clause ]
   [ join_clause ... ]
   [ WHERE condition ]
   [ GROUP BY group_by_clause ]
   [ HAVING condition ]
   [ set_operation ]
   [ ORDER BY order_list ]
   [ LIMIT expr ]
   [ OFFSET expr ]

WITH clause (CTEs)

with_clause ::= WITH [ RECURSIVE ] cte_def { , cte_def }

cte_def ::= name [ ( column_list ) ] AS ( select_stmt )

SELECT list

select_list ::= * | select_item { , select_item }

select_item ::=
   expr [ [ AS ] alias ]
  | agg_func ( [ DISTINCT ] expr ) [ [ AS ] alias ]
  | window_func ( args ) OVER ( window_spec ) [ [ AS ] alias ]
  | ( select_stmt ) [ [ AS ] alias ]  -- scalar subquery

FROM clause

from_clause ::=
   table_name [ [ AS ] alias ]
  | ( select_stmt ) [ AS ] alias  -- derived table
  | generate_series ( start , stop [ , step ] ) [ AS alias ( col_alias ) ] IMPLEMENTED
  -- integer series: generate_series(1, 100)
  -- integer with step: generate_series(0, 100, 10)
  -- timestamp series: generate_series('2024-01-01'::date, '2024-12-31'::date, '1 month')

JOIN clause

join_clause ::=
   [ INNER ] JOIN table_ref ON condition
  | LEFT [ OUTER ] JOIN table_ref ON condition
  | RIGHT [ OUTER ] JOIN table_ref ON condition
  | FULL [ OUTER ] JOIN table_ref ON condition
  | CROSS JOIN table_ref
  | NATURAL [ LEFT | RIGHT | FULL ] JOIN table_ref
  | ... USING ( column_list )
  | LATERAL ( select_stmt ) [ AS ] alias
  | , table_ref  -- implicit cross join

GROUP BY clause

group_by_clause ::=
   column_ref { , column_ref }
  | ROLLUP ( column_ref { , column_ref } )
  | CUBE ( column_ref { , column_ref } )

Set operations

set_operation ::=
   UNION [ ALL ] select_stmt
  | INTERSECT select_stmt
  | EXCEPT select_stmt

3. Aggregate & Window Functions

Aggregate functions

agg_func ::= COUNT | SUM | AVG | MIN | MAX | STRING_AGG | ARRAY_AGG

-- COUNT(DISTINCT col) is supported
-- COUNT(*) is supported
-- STRING_AGG(expr, delimiter) is supported
-- ARRAY_AGG(expr) is supported

Window functions

window_func ::=
   ROW_NUMBER | RANK | DENSE_RANK
  | NTILE | PERCENT_RANK | CUME_DIST
  | LAG | LEAD
  | FIRST_VALUE | LAST_VALUE | NTH_VALUE
  | SUM | COUNT | AVG

window_spec ::=
   [ PARTITION BY column_ref ]
   [ ORDER BY column_ref [ ASC | DESC ] ]
   [ frame_clause ]

frame_clause ::= ROWS BETWEEN frame_bound AND frame_bound

frame_bound ::=
   UNBOUNDED PRECEDING
  | n PRECEDING
  | CURRENT ROW
  | n FOLLOWING
  | UNBOUNDED FOLLOWING

4. INSERT

insert_stmt ::=
   INSERT INTO table_name [ ( column_list ) ]
   VALUES value_tuple { , value_tuple }
   [ conflict_clause ]
   [ RETURNING select_list ]

  | [ with_clause ] INSERT INTO table_name [ ( column_list ) ]
    select_stmt  -- INSERT...SELECT, with optional CTE
   [ conflict_clause ]
   [ RETURNING select_list ]

conflict_clause ::=
   ON CONFLICT [ ( column_ref ) ] DO NOTHING
  | ON CONFLICT ( column_ref ) DO UPDATE SET set_list

5. UPDATE

update_stmt ::=
   UPDATE table_name [ [ AS ] alias ]
   SET set_list
   [ FROM table_name [ [ AS ] alias ] ]
   [ WHERE condition ]
   [ RETURNING select_list ]

set_list ::= set_item { , set_item }

set_item ::= column_name = expr

6. DELETE

delete_stmt ::=
   DELETE FROM table_name
   [ WHERE condition ]
   [ RETURNING select_list ]

7. Data Definition (DDL)

CREATE TABLE

create_table_stmt ::=
   CREATE TABLE table_name ( column_def { , column_def } )

column_def ::=
   column_name data_type { column_constraint }

column_constraint ::=
   NOT NULL
  | UNIQUE
  | PRIMARY KEY
  | DEFAULT expr
  | CHECK ( condition )
  | REFERENCES table_name ( column_name ) [ ON DELETE CASCADE ] [ ON UPDATE CASCADE ]

ALTER TABLE

alter_table_stmt ::=
   ALTER TABLE table_name ADD COLUMN column_def
  | ALTER TABLE table_name DROP COLUMN column_name
  | ALTER TABLE table_name RENAME COLUMN old TO new
  | ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type

Other DDL

drop_table_stmt ::= DROP TABLE table_name

create_index_stmt ::= CREATE INDEX name ON table_name ( column_name )

drop_index_stmt ::= DROP INDEX name

create_type_stmt ::= CREATE TYPE name AS ENUM ( string_literal { , string_literal } )

drop_type_stmt ::= DROP TYPE name

create_sequence_stmt ::= CREATE SEQUENCE name [ START WITH n ] [ INCREMENT BY n ]

drop_sequence_stmt ::= DROP SEQUENCE name

create_view_stmt ::= CREATE VIEW name AS select_stmt

drop_view_stmt ::= DROP VIEW name

truncate_stmt ::= TRUNCATE [ TABLE ] table_name IMPLEMENTED

COPY

copy_stmt ::=
   COPY table_name TO STDOUT [ [ WITH ] CSV [ HEADER ] ]
  | COPY table_name FROM STDIN [ [ WITH ] CSV [ HEADER ] ]

-- COPY TO STDOUT: tab-delimited (default), CSV, CSV HEADER
-- COPY FROM STDIN: tab-delimited (default), CSV
-- NULL values represented as \N

8. Data Types

data_type ::=
   INT | INTEGER IMPLEMENTED
  | BIGINT IMPLEMENTED
  | FLOAT | DOUBLE PRECISION | REAL IMPLEMENTED
  | NUMERIC | DECIMAL IMPLEMENTED
  | TEXT IMPLEMENTED
  | VARCHAR ( n ) IMPLEMENTED
  | CHAR ( n ) (not yet supported)
  | BOOLEAN IMPLEMENTED
  | DATE IMPLEMENTED
  | TIME IMPLEMENTED
  | TIMESTAMP IMPLEMENTED
  | TIMESTAMPTZ IMPLEMENTED
  | INTERVAL IMPLEMENTED
  | UUID IMPLEMENTED
  | SERIAL IMPLEMENTED
  | BIGSERIAL IMPLEMENTED
  | SMALLINT (not yet supported)
  | BYTEA (not yet supported)
  | JSON | JSONB (not yet supported)
  | ARRAY (not yet supported)
  | enum_type_name IMPLEMENTED

9. Expressions

expr ::=
   literal IMPLEMENTED
  | column_ref IMPLEMENTED
  | expr binary_op expr IMPLEMENTED
  | unary_op expr IMPLEMENTED
  | func_call IMPLEMENTED
  | CASE WHEN condition THEN expr { WHEN ... } [ ELSE expr ] END IMPLEMENTED
  | ( select_stmt )  -- scalar subquery IMPLEMENTED
  | expr :: data_type  -- postfix cast IMPLEMENTED
  | CAST ( expr AS data_type ) IMPLEMENTED
  | expr IS [ NOT ] NULL  -- postfix null test (expression-level) IMPLEMENTED

binary_op ::= + | - | * | / | % | ||

unary_op ::= -

literal ::= integer | float | string | TRUE | FALSE | NULL

Built-in functions

func_call ::=
   COALESCE ( expr { , expr } ) IMPLEMENTED
  | NULLIF ( expr , expr ) IMPLEMENTED
  | GREATEST ( expr { , expr } ) IMPLEMENTED
  | LEAST ( expr { , expr } ) IMPLEMENTED
  | UPPER ( expr ) IMPLEMENTED
  | LOWER ( expr ) IMPLEMENTED
  | LENGTH ( expr ) IMPLEMENTED
  | TRIM ( expr ) IMPLEMENTED
  | CONCAT ( expr { , expr } ) IMPLEMENTED
  | SUBSTRING ( expr FROM n [ FOR n ] ) IMPLEMENTED
  | NEXTVAL ( string ) IMPLEMENTED
  | CURRVAL ( string ) IMPLEMENTED
  | GEN_RANDOM_UUID () IMPLEMENTED
  | NOW () IMPLEMENTED
  | CURRENT_TIMESTAMP IMPLEMENTED
  | CURRENT_DATE IMPLEMENTED
  | EXTRACT ( field FROM expr ) IMPLEMENTED
  | DATE_PART ( string , expr ) IMPLEMENTED
  | DATE_TRUNC ( string , expr ) IMPLEMENTED
  | AGE ( expr [ , expr ] ) IMPLEMENTED
  | TO_CHAR ( expr , string ) IMPLEMENTED
  | REPLACE ( expr , expr , expr ) IMPLEMENTED
  | LEFT ( expr , n ) / RIGHT ( expr , n ) IMPLEMENTED
  | LPAD ( expr , n [ , fill ] ) / RPAD ( ... ) IMPLEMENTED
  | CONCAT_WS ( sep , expr { , expr } ) IMPLEMENTED
  | POSITION ( expr IN expr ) IMPLEMENTED
  | SPLIT_PART ( expr , delim , n ) IMPLEMENTED
  | REPEAT ( expr , n ) IMPLEMENTED
  | REVERSE ( expr ) IMPLEMENTED
  | INITCAP ( expr ) IMPLEMENTED
  | ABS ( expr ) IMPLEMENTED
  | CEIL ( expr ) / FLOOR ( expr ) IMPLEMENTED
  | ROUND ( expr [ , n ] ) IMPLEMENTED
  | POWER ( expr , expr ) / SQRT ( expr ) IMPLEMENTED
  | MOD ( expr , expr ) IMPLEMENTED
  | SIGN ( expr ) IMPLEMENTED
  | RANDOM () IMPLEMENTED
  | STRING_AGG ( expr , delimiter ) IMPLEMENTED
  | ARRAY_AGG ( expr ) IMPLEMENTED

10. Conditions (WHERE / ON / HAVING)

condition ::=
   condition AND condition IMPLEMENTED
  | condition OR condition IMPLEMENTED
  | NOT condition IMPLEMENTED
  | expr cmp_op expr IMPLEMENTED
  | expr BETWEEN expr AND expr IMPLEMENTED
  | expr [ NOT ] IN ( value_list | select_stmt ) IMPLEMENTED
  | ( expr_list ) IN ( select_stmt )  -- multi-column IN IMPLEMENTED
  | expr cmp_op ANY ( value_list | select_stmt ) IMPLEMENTED
  | expr cmp_op ALL ( value_list | select_stmt ) IMPLEMENTED
  | expr [ NOT ] LIKE pattern IMPLEMENTED
  | expr ILIKE pattern IMPLEMENTED
  | expr IS [ NOT ] NULL IMPLEMENTED
  | expr IS [ NOT ] DISTINCT FROM expr IMPLEMENTED
  | EXISTS ( select_stmt ) IMPLEMENTED
  | NOT EXISTS ( select_stmt ) IMPLEMENTED
  | expr SIMILAR TO pattern (not yet supported)
  | expr ~ pattern  -- POSIX regex (not yet supported)

cmp_op ::= = | != | <> | < | > | <= | >=

11. Transactions

transaction_stmt ::=
   BEGIN IMPLEMENTED
  | COMMIT IMPLEMENTED
  | ROLLBACK IMPLEMENTED
  | SAVEPOINT name (not yet supported)
  | RELEASE SAVEPOINT name (not yet supported)
  | ROLLBACK TO SAVEPOINT name (not yet supported)

12. Not Yet Supported

Types: CHAR(n), SMALLINT, BYTEA, JSON/JSONB, ARRAY, composite types, range types. Expressions: SIMILAR TO, POSIX regex (~), ARRAY[...] constructor. DDL: CREATE TABLE ... AS SELECT, CREATE MATERIALIZED VIEW, table-level constraints, CASCADE/RESTRICT on DROP. Transactions: SAVEPOINT, isolation levels. Window: RANGE BETWEEN, GROUPS BETWEEN, named window definitions. Aggregation: GROUPING SETS, FILTER (WHERE ...), ordered-set aggregates. Other: SQL-level PREPARE/EXECUTE (wire-level Extended Query Protocol is supported), LISTEN/NOTIFY, GRANT/REVOKE, stored procedures, triggers.