Repository: tobymao/sqlglot

Issue:
ParseError `Expecting )` for collation when using `CREATE TABLE` statement PostgreSQL
**Fully reproducible code snippet**

A test file is:
```py
from sqlglot import parse_one

query = """
CREATE TABLE IF NOT EXISTS public.language
(
    language_id integer NOT NULL DEFAULT nextval('language_language_id_seq'::regclass),
    name character(20) COLLATE pg_catalog."default" NOT NULL,
    last_update timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT language_pkey PRIMARY KEY (language_id)
)
"""
dialect = "postgres"


print(parse_one(query, read=dialect))
```

A traceback is:
```py
Traceback (most recent call last):
  File "/Users/user/folder/api/test.py", line 15, 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 5, Col: 39.
  ULL DEFAULT nextval('language_language_id_seq'::regclass),
    name character(20) COLLATE pg_catalog."default" NOT NULL,
    last_update timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAI
```

**Official Documentation**
[PostgreSQL 16 CREATE TABLE docs](https://www.postgresql.org/docs/16/sql-createtable.html)
[PostgreSQL 16 Collation Support docs](https://www.postgresql.org/docs/16/collation.html)


Discussion:

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index e9cf487d..4a425d29 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -614,7 +614,7 @@ class Snowflake(Dialect):
             # can be joined in a query with a comma separator, as well as closing paren
             # in case of subqueries
             while self._is_connected() and not self._match_set(
-                (TokenType.COMMA, TokenType.R_PAREN), advance=False
+                (TokenType.COMMA, TokenType.L_PAREN, TokenType.R_PAREN), advance=False
             ):
                 parts.append(self._advance_any(ignore_reserved=True))
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 3a611e86..7978474a 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -929,7 +929,8 @@ class Parser(metaclass=_Parser):
             enforced=self._match_text_seq("ENFORCED"),
         ),
         "COLLATE": lambda self: self.expression(
-            exp.CollateColumnConstraint, this=self._parse_var(any_token=True)
+            exp.CollateColumnConstraint,
+            this=self._parse_identifier() or self._parse_column(),
         ),
         "COMMENT": lambda self: self.expression(
             exp.CommentColumnConstraint, this=self._parse_string()
@@ -1149,6 +1150,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 +5294,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..21c7e2bc 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -17,9 +17,6 @@ class TestPostgres(Validator):
         )
 
         self.validate_identity("SHA384(x)")
-        self.validate_identity(
-            'CREATE TABLE x (a TEXT COLLATE "de_DE")', "CREATE TABLE x (a TEXT COLLATE de_DE)"
-        )
         self.validate_identity("1.x", "1. AS x")
         self.validate_identity("|/ x", "SQRT(x)")
         self.validate_identity("||/ x", "CBRT(x)")
@@ -792,6 +789,8 @@ class TestPostgres(Validator):
         cdef.args["kind"].assert_is(exp.DataType)
         self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)")
 
+        self.validate_identity('CREATE TABLE x (a TEXT COLLATE "de_DE")')
+        self.validate_identity('CREATE TABLE x (a TEXT COLLATE pg_catalog."default")')
         self.validate_identity("CREATE TABLE t (col INT[3][5])")
         self.validate_identity("CREATE TABLE t (col INT[3])")
         self.validate_identity("CREATE INDEX IF NOT EXISTS ON t(c)")
@@ -1003,6 +1002,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):
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index ce8a8053..3686de5a 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -919,6 +919,11 @@ WHERE
             "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla",
         )
 
+        self.validate_identity(
+            "SELECT * FROM @test.public.thing/location/somefile.csv( FILE_FORMAT => 'fmt' )",
+            "SELECT * FROM @test.public.thing/location/somefile.csv (FILE_FORMAT => 'fmt')",
+        )
+
     def test_sample(self):
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)")
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE SYSTEM (3) SEED (82)")