Postgres dialect: parse error for "on delete" in column constraint

[grschafer]

Expected Behaviour

Sqlfluff parses column constraints containing "on delete" clause.

To see Postgres's column_constraint structure, search for "on delete" on this page: https://www.postgresql.org/docs/current/sql-createtable.html

Observed Behaviour

Sqlfluff throws parse error if "on delete" clause of a column constraint exists.

== [test.sql] FAIL
L:   4 | P:   1 |  PRS | Line 4, Position 1: Found unparsable section: 'create
                       | table users (\n user_id integer...'

Looks like match / on delete / on update clauses are recognized in a table constraint:

sqlfluff/src/sqlfluff/dialects/dialect_postgres.py

Line 1136 in 304a197

"REFERENCES",

But not in a column constraint:

sqlfluff/src/sqlfluff/dialects/dialect_postgres.py

Line 1028 in 304a197

"REFERENCES",

Steps to Reproduce

Test sql file follows:

create table groups (
    group_id integer primary key generated by default as identity
);
create table users (
    user_id integer primary key generated by default as identity,
    group_id integer references groups (group_id) on delete cascade
);

Try to parse that file:

[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    statement:
[L:  1, P:  1]      |        create_table_statement:
[L:  1, P:  1]      |            keyword:                                          'create'
[L:  1, P:  7]      |            whitespace:                                       ' '
[L:  1, P:  8]      |            keyword:                                          'table'
[L:  1, P: 13]      |            whitespace:                                       ' '
[L:  1, P: 14]      |            table_reference:
[L:  1, P: 14]      |                identifier:                                   'groups'
[L:  1, P: 20]      |            whitespace:                                       ' '
[L:  1, P: 21]      |            bracketed:
[L:  1, P: 21]      |                start_bracket:                                '('
[L:  1, P: 22]      |                [META] indent:
[L:  1, P: 22]      |                newline:                                      '\n'
[L:  2, P:  1]      |                whitespace:                                   '    '
[L:  2, P:  5]      |                column_reference:
[L:  2, P:  5]      |                    identifier:                               'group_id'
[L:  2, P: 13]      |                whitespace:                                   ' '
[L:  2, P: 14]      |                data_type:
[L:  2, P: 14]      |                    data_type_identifier:                     'integer'
[L:  2, P: 21]      |                whitespace:                                   ' '
[L:  2, P: 22]      |                column_constraint_segment:
[L:  2, P: 22]      |                    keyword:                                  'primary'
[L:  2, P: 29]      |                    whitespace:                               ' '
[L:  2, P: 30]      |                    keyword:                                  'key'
[L:  2, P: 33]      |                whitespace:                                   ' '
[L:  2, P: 34]      |                column_constraint_segment:
[L:  2, P: 34]      |                    keyword:                                  'generated'
[L:  2, P: 43]      |                    whitespace:                               ' '
[L:  2, P: 44]      |                    keyword:                                  'by'
[L:  2, P: 46]      |                    whitespace:                               ' '
[L:  2, P: 47]      |                    keyword:                                  'default'
[L:  2, P: 54]      |                    whitespace:                               ' '
[L:  2, P: 55]      |                    keyword:                                  'as'
[L:  2, P: 57]      |                    whitespace:                               ' '
[L:  2, P: 58]      |                    keyword:                                  'identity'
[L:  2, P: 66]      |                newline:                                      '\n'
[L:  3, P:  1]      |                [META] dedent:
[L:  3, P:  1]      |                end_bracket:                                  ')'
[L:  3, P:  2]      |    statement_terminator:                                     ';'
[L:  3, P:  3]      |    newline:                                                  '\n'
[L:  4, P:  1]      |    statement:
[L:  4, P:  1]      |        unparsable:                                           !! Expected: 'StatementSegment'
[L:  4, P:  1]      |            raw:                                              'create'
[L:  4, P:  7]      |            whitespace:                                       ' '
[L:  4, P:  8]      |            raw:                                              'table'
[L:  4, P: 13]      |            whitespace:                                       ' '
[L:  4, P: 14]      |            raw:                                              'users'
[L:  4, P: 19]      |            whitespace:                                       ' '
[L:  4, P: 20]      |            bracketed:
[L:  4, P: 20]      |                start_bracket:                                '('
[L:  4, P: 21]      |                newline:                                      '\n'
[L:  5, P:  1]      |                whitespace:                                   '    '
[L:  5, P:  5]      |                raw:                                          'user_id'
[L:  5, P: 12]      |                whitespace:                                   ' '
[L:  5, P: 13]      |                raw:                                          'integer'
[L:  5, P: 20]      |                whitespace:                                   ' '
[L:  5, P: 21]      |                raw:                                          'primary'
[L:  5, P: 28]      |                whitespace:                                   ' '
[L:  5, P: 29]      |                raw:                                          'key'
[L:  5, P: 32]      |                whitespace:                                   ' '
[L:  5, P: 33]      |                raw:                                          'generated'
[L:  5, P: 42]      |                whitespace:                                   ' '
[L:  5, P: 43]      |                raw:                                          'by'
[L:  5, P: 45]      |                whitespace:                                   ' '
[L:  5, P: 46]      |                raw:                                          'default'
[L:  5, P: 53]      |                whitespace:                                   ' '
[L:  5, P: 54]      |                raw:                                          'as'
[L:  5, P: 56]      |                whitespace:                                   ' '
[L:  5, P: 57]      |                raw:                                          'identity'
[L:  5, P: 65]      |                comma:                                        ','
[L:  5, P: 66]      |                newline:                                      '\n'
[L:  6, P:  1]      |                whitespace:                                   '    '
[L:  6, P:  5]      |                raw:                                          'group_id'
[L:  6, P: 13]      |                whitespace:                                   ' '
[L:  6, P: 14]      |                raw:                                          'integer'
[L:  6, P: 21]      |                whitespace:                                   ' '
[L:  6, P: 22]      |                raw:                                          'references'
[L:  6, P: 32]      |                whitespace:                                   ' '
[L:  6, P: 33]      |                raw:                                          'groups'
[L:  6, P: 39]      |                whitespace:                                   ' '
[L:  6, P: 40]      |                bracketed:
[L:  6, P: 40]      |                    start_bracket:                            '('
[L:  6, P: 41]      |                    [META] indent:
[L:  6, P: 41]      |                    column_reference:
[L:  6, P: 41]      |                        identifier:                           'group_id'
[L:  6, P: 49]      |                    [META] dedent:
[L:  6, P: 49]      |                    end_bracket:                              ')'
[L:  6, P: 50]      |                whitespace:                                   ' '
[L:  6, P: 51]      |                raw:                                          'on'
[L:  6, P: 53]      |                whitespace:                                   ' '
[L:  6, P: 54]      |                raw:                                          'delete'
[L:  6, P: 60]      |                whitespace:                                   ' '
[L:  6, P: 61]      |                raw:                                          'cascade'
[L:  6, P: 68]      |                newline:                                      '\n'
[L:  7, P:  1]      |                end_bracket:                                  ')'
[L:  7, P:  2]      |    statement_terminator:                                     ';'
[L:  7, P:  3]      |    newline:                                                  '\n'

==== parsing violations ====
L:   4 | P:   1 |  PRS | Line 4, Position 1: Found unparsable section: 'create table users
                       | (\n user_id integer...'

Dialect

Postgres

Version

Python 3.9.2
sqlfluff, version 0.6.8

Configuration

[sqlfluff]
dialect = postgres

---

Thanks for making an SQL linter! Happy to see tools improving in this area. 😀