Repository: tobymao/sqlglot

Issue:
ParseError `Expecting )` for CREATE TABLE statement PostgreSQL
**Fully reproducible code snippet**

A test file:
```py
from sqlglot import parse_one

query = """
CREATE TABLE IF NOT EXISTS public.rental
(
    rental_id integer NOT NULL DEFAULT nextval('rental_rental_id_seq'::regclass),
    rental_date timestamp without time zone NOT NULL,
    inventory_id integer NOT NULL,
    customer_id integer NOT NULL,
    return_date timestamp without time zone,
    staff_id integer NOT NULL,
    last_update timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
    CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id)
        REFERENCES public.customer (customer_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id)
        REFERENCES public.inventory (inventory_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT rental_staff_id_fkey FOREIGN KEY (staff_id)
        REFERENCES public.staff (staff_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)
"""
dialect = "postgres"


print(parse_one(query, read=dialect))
```

An exception:
```py
Traceback (most recent call last):
  File "/Users/user/folder/api/test.py", line 31, in <module>
    print(parse_one(query, read=dialect))
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/__init__.py", line 136, in parse_one
    result = dialect.parse(sql, **opts)
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/dialects/dialect.py", line 695, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1300, in parse
    return self._parse(
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1369, in _parse
    expressions.append(parse_method(self))
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1601, in _parse_statement
    return self.STATEMENT_PARSERS[self._prev.token_type](self)
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 709, in <lambda>
    TokenType.CREATE: lambda self: self._parse_create(),
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1743, in _parse_create
    this = self._parse_schema(this=table_parts)
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 4986, in _parse_schema
    self._match_r_paren()
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 6706, in _match_r_paren
    self.raise_error("Expecting )")
  File "/Users/user/Library/Caches/pypoetry/virtualenvs/folder-venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1413, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 13, Col: 47.
   rental_customer_id_fkey FOREIGN KEY (customer_id)
        REFERENCES public.customer (customer_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT rental_inventory_id_fke
```

**Official Documentation**
[PostgreSQL 16 CREATE TABLE docs](https://www.postgresql.org/docs/16/sql-createtable.html) (please look at `and table_constraint is` section)


Discussion:

Patch:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 3a611e86..99ac0f8e 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1149,6 +1149,17 @@ class Parser(metaclass=_Parser):
         **dict.fromkeys(("BINDING", "COMPENSATION", "EVOLUTION"), tuple()),
     }
 
+    KEY_CONSTRAINT_OPTIONS: OPTIONS_TYPE = {
+        "NOT": ("ENFORCED",),
+        "MATCH": (
+            "FULL",
+            "PARTIAL",
+            "SIMPLE",
+        ),
+        "INITIALLY": ("DEFERRED", "IMMEDIATE"),
+        **dict.fromkeys(("DEFERRABLE", "NORELY"), tuple()),
+    }
+
     INSERT_ALTERNATIVES = {"ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"}
 
     CLONE_KEYWORDS = {"CLONE", "COPY"}
@@ -5282,18 +5293,13 @@ class Parser(metaclass=_Parser):
                     self.raise_error("Invalid key constraint")
 
                 options.append(f"ON {on} {action}")
-            elif self._match_text_seq("NOT", "ENFORCED"):
-                options.append("NOT ENFORCED")
-            elif self._match_text_seq("DEFERRABLE"):
-                options.append("DEFERRABLE")
-            elif self._match_text_seq("INITIALLY", "DEFERRED"):
-                options.append("INITIALLY DEFERRED")
-            elif self._match_text_seq("NORELY"):
-                options.append("NORELY")
-            elif self._match_text_seq("MATCH", "FULL"):
-                options.append("MATCH FULL")
             else:
-                break
+                var = self._parse_var_from_options(
+                    self.KEY_CONSTRAINT_OPTIONS, raise_unmatched=False
+                )
+                if not var:
+                    break
+                options.append(var.name)
 
         return options
 


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 92e8443d..c7688f59 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1003,6 +1003,29 @@ class TestPostgres(Validator):
             "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_table_id ON tbl USING btree(id)"
         )
 
+        self.validate_identity(
+            """
+        CREATE TABLE IF NOT EXISTS public.rental
+        (
+            inventory_id INT NOT NULL,
+            CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id)
+                REFERENCES public.customer (customer_id) MATCH FULL
+                ON UPDATE CASCADE
+                ON DELETE RESTRICT,
+            CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id)
+                REFERENCES public.inventory (inventory_id) MATCH PARTIAL
+                ON UPDATE CASCADE
+                ON DELETE RESTRICT,
+            CONSTRAINT rental_staff_id_fkey FOREIGN KEY (staff_id)
+                REFERENCES public.staff (staff_id) MATCH SIMPLE
+                ON UPDATE CASCADE
+                ON DELETE RESTRICT,
+            INITIALLY IMMEDIATE
+        )
+        """,
+            "CREATE TABLE IF NOT EXISTS public.rental (inventory_id INT NOT NULL, CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES public.inventory (inventory_id) MATCH PARTIAL ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rental_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, INITIALLY IMMEDIATE)",
+        )
+
         with self.assertRaises(ParseError):
             transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres")
         with self.assertRaises(ParseError):