Repository: tobymao/sqlglot

Issue:
Postgres: Unable to parse STORE Generated Column on create table
# About
[Here's the Postgres docs on this syntax](https://www.postgresql.org/docs/current/ddl-generated-columns.html). It seems I can't parse stored column syntax, I noticed I had a similar issue for syntax for adding columns.

> Note I have tried this with the latest release, but also thank you for creating this incredible library 



### Example syntax

```sql
CREATE TABLE IF NOT EXISTS example_2 (
  col_a INT NOT NULL,
  col_b INT NOT NULL,
  col_c INT GENERATED ALWAYS AS (col_a + col_b), -- not supported in postgres
  col_d INT GENERATED ALWAYS AS (col_a + col_b) VIRTUAL, -- not supported in postgres
  col_e INT GENERATED ALWAYS AS (col_a + col_b) STORED
);
```

<img width="1005" alt="image" src="https://github.com/user-attachments/assets/0983ea6d-70d4-434c-9c45-40e97e8dd5bc">


## Virtual Keyword

As mentioned [here](https://github.com/tobymao/sqlglot/pull/4465#pullrequestreview-2472079771) it likely makes sense to also add support for the `VIRTUAL` keyword here, it is an explicit version of the absence of the default behaviour, it's also mutually exclusive to the `STORED` keyword. 

## Dialects 

- [MySQL](https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html) , support for both `stored` and `virtual` keyword
- [SQLite](https://www.sqlite.org/syntax/column-constraint.html) , support for both `stored` and `virtual` keyword
- [Postgres](https://www.postgresql.org/docs/current/ddl-generated-columns.html), support for `stored` but no support for `virtual` columns in tables
  - They said _"mate, use a view..."_, to use generated columns it seems you need to use the `stored` keyword
  - The postgresql docs say:
    > _Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). **PostgreSQL currently implements only stored generated columns**._
- [BigQuery](https://cloud.google.com/spanner/docs/generated-column/how-to), support for `stored` but no `virtual` explicit keyword 
  - Confusingly they suggest postgres supports this syntax in their docs (maybe they use to?)

### Dialects without support

- [T-SQL](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16#full-syntax)
- [Spark/DB](https://docs.databricks.com/en/delta/generated-columns.html#create-a-table-with-generated-columns)
- [Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html)
- [Clickhouse](https://clickhouse.com/docs/en/sql-reference/statements/alter/column#materialize-column)

# The current behaviour

## Reproducible example

```python
from sqlglot import parse

parse("""
  CREATE TABLE IF NOT EXISTS example (
    col_a INT NOT NULL,
    col_b INT NOT NULL,
    col_c INT GENERATED ALWAYS AS (col_a + col_b) STORED
  );
""", read='postgres')
```

## Example stacktrace

```
Traceback (most recent call last):
  ...
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/__init__.py", line 102, in parse
    return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/dialects/dialect.py", line 919, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 1398, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 1467, in _parse
    expressions.append(parse_method(self))
                       ^^^^^^^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 1699, in _parse_statement
    return self.STATEMENT_PARSERS[self._prev.token_type](self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 779, in <lambda>
    TokenType.CREATE: lambda self: self._parse_create(),
                                   ^^^^^^^^^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 1854, in _parse_create
    this = self._parse_schema(this=table_parts)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 5379, in _parse_schema
    self._match_r_paren()
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 7172, in _match_r_paren
    self.raise_error("Expecting )")
  File "/Users/angus/code/jupyter/env/lib/python3.12/site-packages/sqlglot/parser.py", line 1511, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 4, Col: 53.
  ample (
  col_a INT NOT NULL,
  col_b INT NOT NULL,
  col_c INT GENERATED ALWAYS AS (col_a + col_b) STORED
);
```


Discussion:

Patch:
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index ce2daf23..e0f3c1b2 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -476,6 +476,20 @@ class Postgres(Dialect):
                 and self.dialect.to_json_path(self._parse_bitwise()),
             )
 
+        def _parse_generated_as_identity(
+            self,
+        ) -> (
+            exp.GeneratedAsIdentityColumnConstraint
+            | exp.ComputedColumnConstraint
+            | exp.GeneratedAsRowColumnConstraint
+        ):
+            this = super()._parse_generated_as_identity()
+
+            if self._match_text_seq("STORED"):
+                this = self.expression(exp.ComputedColumnConstraint, this=this.expression)
+
+            return this
+
     class Generator(generator.Generator):
         SINGLE_STRING_INTERVAL = True
         RENAME_TABLE_WITH_DB = False
@@ -691,3 +705,6 @@ class Postgres(Dialect):
                 if isinstance(seq_get(exprs, 0), exp.Select)
                 else f"{self.normalize_func('ARRAY')}[{self.expressions(expression, flat=True)}]"
             )
+
+        def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
+            return f"GENERATED ALWAYS AS ({self.sql(expression, 'this')}) STORED"


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 8f84d9ff..66ded239 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1047,6 +1047,7 @@ class TestPostgres(Validator):
         self.validate_identity("CREATE TABLE tbl (col INT UNIQUE NULLS NOT DISTINCT DEFAULT 9.99)")
         self.validate_identity("CREATE TABLE tbl (col UUID UNIQUE DEFAULT GEN_RANDOM_UUID())")
         self.validate_identity("CREATE TABLE tbl (col UUID, UNIQUE NULLS NOT DISTINCT (col))")
+        self.validate_identity("CREATE TABLE tbl (col_a INT GENERATED ALWAYS AS (1 + 2) STORED)")
 
         self.validate_identity("CREATE INDEX CONCURRENTLY ix_table_id ON tbl USING btree(id)")
         self.validate_identity(