Repository: ariga/atlas
Pull request #722 (2022-04-18 20:00:27)

sql/postgres: support dropping generate expressions
Also, apply MySQL generated expressions validation also in the differ


Patch:
diff --git a/schema/schemaspec/extension.go b/schema/schemaspec/extension.go
index 61806243..35748a49 100644
--- a/schema/schemaspec/extension.go
+++ b/schema/schemaspec/extension.go
@@ -93,7 +93,7 @@ func (r *Resource) As(target interface{}) error {
 	for _, ft := range specFields(target) {
 		field := v.FieldByName(ft.Name)
 		switch {
-		case ft.isName():
+		case ft.isName() && !hasAttr(r, ft.tag):
 			if seenName {
 				return errors.New("schemaspec: extension must have only one isName field")
 			}
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index 5df942db..195f4b48 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -280,23 +280,6 @@ func linkForeignKeys(tbl *schema.Table, sch *schema.Schema, table *sqlspec.Table
 	return nil
 }
 
-// FromRealm converts a schema.Realm into []sqlspec.Schema and []sqlspec.Table.
-func FromRealm(r *schema.Realm, fn TableSpecFunc) ([]*sqlspec.Schema, []*sqlspec.Table, error) {
-	var (
-		schemas []*sqlspec.Schema
-		tables  []*sqlspec.Table
-	)
-	for _, sch := range r.Schemas {
-		schemaSpec, tableSpecs, err := FromSchema(sch, fn)
-		if err != nil {
-			return nil, nil, fmt.Errorf("specutil: cannot convert schema %q: %w", sch.Name, err)
-		}
-		schemas = append(schemas, schemaSpec)
-		tables = append(tables, tableSpecs...)
-	}
-	return schemas, tables, nil
-}
-
 // FromSchema converts a schema.Schema into sqlspec.Schema and []sqlspec.Table.
 func FromSchema(s *schema.Schema, fn TableSpecFunc) (*sqlspec.Schema, []*sqlspec.Table, error) {
 	spec := &sqlspec.Schema{
@@ -395,6 +378,49 @@ func FromColumn(col *schema.Column, columnTypeSpec ColumnTypeSpecFunc) (*sqlspec
 	return spec, nil
 }
 
+// FromGenExpr returns the spec for a generated expression.
+func FromGenExpr(x schema.GeneratedExpr, t func(string) string) *schemaspec.Resource {
+	return &schemaspec.Resource{
+		Type: "as",
+		Attrs: []*schemaspec.Attr{
+			StrAttr("expr", x.Expr),
+			VarAttr("type", t(x.Type)),
+		},
+	}
+}
+
+// ConvertGenExpr converts the "as" attribute or the block under the given resource.
+func ConvertGenExpr(r *schemaspec.Resource, c *schema.Column, t func(string) string) error {
+	asA, okA := r.Attr("as")
+	asR, okR := r.Resource("as")
+	switch {
+	case okA && okR:
+		return fmt.Errorf("multiple as definitions for column %q", c.Name)
+	case okA:
+		expr, err := asA.String()
+		if err != nil {
+			return err
+		}
+		c.Attrs = append(c.Attrs, &schema.GeneratedExpr{
+			Type: t(""), // default type.
+			Expr: expr,
+		})
+	case okR:
+		var spec struct {
+			Expr string `spec:"expr"`
+			Type string `spec:"type"`
+		}
+		if err := asR.As(&spec); err != nil {
+			return err
+		}
+		c.Attrs = append(c.Attrs, &schema.GeneratedExpr{
+			Expr: spec.Expr,
+			Type: t(spec.Type),
+		})
+	}
+	return nil
+}
+
 func toValue(expr schema.Expr) (schemaspec.Value, error) {
 	var (
 		v   string
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 6071b501..d75be297 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -91,14 +91,16 @@ func (d *diff) ColumnChange(from, to *schema.Column) (schema.ChangeKind, error)
 	if changed {
 		change |= schema.ChangeType
 	}
-	changed, err = d.defaultChanged(from, to)
-	if err != nil {
+	if changed, err = d.defaultChanged(from, to); err != nil {
 		return schema.NoChange, err
 	}
 	if changed {
 		change |= schema.ChangeDefault
 	}
-	if changed := d.generatedChanged(from, to); changed {
+	if changed, err = d.generatedChanged(from, to); err != nil {
+		return schema.NoChange, err
+	}
+	if changed {
 		change |= schema.ChangeGenerated
 	}
 	return change, nil
@@ -374,16 +376,15 @@ func (d *diff) defaultChanged(from, to *schema.Column) (bool, error) {
 }
 
 // generatedChanged reports if the generated expression of a column was changed.
-func (*diff) generatedChanged(from, to *schema.Column) bool {
-	var fromX, toX schema.GeneratedExpr
-	switch fromHas, toHas := sqlx.Has(from.Attrs, &fromX), sqlx.Has(to.Attrs, &toX); {
-	case fromHas != toHas:
-		return true
-	case fromHas && toHas:
-		return storedOrVirtual(fromX.Type) != storedOrVirtual(toX.Type) || fromX.Expr != toX.Expr
-	default: // !fromHas && !toHas.
-		return false
+func (*diff) generatedChanged(from, to *schema.Column) (bool, error) {
+	var (
+		fromX, toX     schema.GeneratedExpr
+		fromHas, toHas = sqlx.Has(from.Attrs, &fromX), sqlx.Has(to.Attrs, &toX)
+	)
+	if !fromHas && !toHas || fromHas && toHas && fromX.Expr == toX.Expr && storedOrVirtual(fromX.Type) == storedOrVirtual(toX.Type) {
+		return false, nil
 	}
+	return true, checkChangeGenerated(from, to)
 }
 
 // equalIntValues report if the 2 int default values are ~equal.
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 37a335b9..ebf37cf2 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -335,7 +335,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 				}
 				reverse = append(reverse, &schema.DropColumn{C: change.C})
 			case *schema.ModifyColumn:
-				if err := checkChangeGenerated(change); err != nil {
+				if err := checkChangeGenerated(change.From, change.To); err != nil {
 					return err
 				}
 				b.P("MODIFY COLUMN")
@@ -722,21 +722,17 @@ func supportsCharset(t schema.Type) bool {
 }
 
 // checkChangeGenerated checks if the change of a generated column is valid.
-func checkChangeGenerated(c *schema.ModifyColumn) error {
-	if !c.Change.Is(schema.ChangeGenerated) {
-		return nil
-	}
+func checkChangeGenerated(from, to *schema.Column) error {
 	var fromX, toX schema.GeneratedExpr
-	switch fromHas, toHas := sqlx.Has(c.From.Attrs, &fromX), sqlx.Has(c.To.Attrs, &toX); {
+	switch fromHas, toHas := sqlx.Has(from.Attrs, &fromX), sqlx.Has(to.Attrs, &toX); {
 	case !fromHas && toHas && storedOrVirtual(toX.Type) == virtual:
-		return fmt.Errorf("changing column %q to VIRTUAL generated column is not supported (drop and add is required)", c.From.Name)
+		return fmt.Errorf("changing column %q to VIRTUAL generated column is not supported (drop and add is required)", from.Name)
 	case fromHas && !toHas && storedOrVirtual(fromX.Type) == virtual:
-		return fmt.Errorf("changing VIRTUAL generated column %q to non-generated column is not supported (drop and add is required)", c.From.Name)
+		return fmt.Errorf("changing VIRTUAL generated column %q to non-generated column is not supported (drop and add is required)", from.Name)
 	case fromHas && toHas && storedOrVirtual(fromX.Type) != storedOrVirtual(toX.Type):
-		return fmt.Errorf("changing the store type of generated column %q from %q to %q is not supported", c.From.Name, storedOrVirtual(fromX.Type), storedOrVirtual(toX.Type))
-	default:
-		return nil
+		return fmt.Errorf("changing the store type of generated column %q from %q to %q is not supported", from.Name, storedOrVirtual(fromX.Type), storedOrVirtual(toX.Type))
 	}
+	return nil
 }
 
 func quote(s string) string {
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index da0e96d2..5471bb3d 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -176,7 +176,7 @@ func convertColumn(spec *sqlspec.Column, _ *schema.Table) (*schema.Column, error
 			c.AddAttrs(&AutoIncrement{})
 		}
 	}
-	if err := convertGenExpr(spec.Remain(), c); err != nil {
+	if err := specutil.ConvertGenExpr(spec.Remain(), c, storedOrVirtual); err != nil {
 		return nil, err
 	}
 	return c, err
@@ -261,53 +261,11 @@ func columnSpec(c *schema.Column, t *schema.Table) (*sqlspec.Column, error) {
 		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.BoolAttr("auto_increment", true))
 	}
 	if x := (schema.GeneratedExpr{}); sqlx.Has(c.Attrs, &x) {
-		spec.Extra.Children = append(spec.Extra.Children, fromGenExpr(x))
+		spec.Extra.Children = append(spec.Extra.Children, specutil.FromGenExpr(x, storedOrVirtual))
 	}
 	return spec, nil
 }
 
-// fromGenExpr returns the spec for a generated expression.
-func fromGenExpr(x schema.GeneratedExpr) *schemaspec.Resource {
-	return &schemaspec.Resource{
-		Type: "as",
-		Attrs: []*schemaspec.Attr{
-			specutil.StrAttr("expr", x.Expr),
-			specutil.VarAttr("type", storedOrVirtual(x.Type)),
-		},
-	}
-}
-
-func convertGenExpr(r *schemaspec.Resource, c *schema.Column) error {
-	asA, okA := r.Attr("as")
-	asR, okR := r.Resource("as")
-	switch {
-	case okA && okR:
-		return fmt.Errorf("multiple as definitions for column %q", c.Name)
-	case okA:
-		expr, err := asA.String()
-		if err != nil {
-			return err
-		}
-		c.Attrs = append(c.Attrs, &schema.GeneratedExpr{
-			Type: virtual,
-			Expr: expr,
-		})
-	case okR:
-		var spec struct {
-			Expr string `spec:"expr"`
-			Type string `spec:"type"`
-		}
-		if err := asR.As(&spec); err != nil {
-			return err
-		}
-		c.Attrs = append(c.Attrs, &schema.GeneratedExpr{
-			Expr: spec.Expr,
-			Type: storedOrVirtual(spec.Type),
-		})
-	}
-	return nil
-}
-
 // storedOrVirtual returns a STORED or VIRTUAL
 // generated type option based on the given string.
 func storedOrVirtual(s string) string {
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index 25a8dd30..bb793db7 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -49,8 +49,7 @@ func (d *diff) ColumnChange(from, to *schema.Column) (schema.ChangeKind, error)
 	if changed {
 		change |= schema.ChangeType
 	}
-	changed, err = d.defaultChanged(from, to)
-	if err != nil {
+	if changed, err = d.defaultChanged(from, to); err != nil {
 		return schema.NoChange, err
 	}
 	if changed {
@@ -59,11 +58,16 @@ func (d *diff) ColumnChange(from, to *schema.Column) (schema.ChangeKind, error)
 	if identityChanged(from.Attrs, to.Attrs) {
 		change |= schema.ChangeAttr
 	}
+	if changed, err = d.generatedChanged(from, to); err != nil {
+		return schema.NoChange, err
+	}
+	if changed {
+		change |= schema.ChangeGenerated
+	}
 	return change, nil
 }
 
-// defaultChanged reports if the a default value of a column
-// type was changed.
+// defaultChanged reports if the default value of a column was changed.
 func (d *diff) defaultChanged(from, to *schema.Column) (bool, error) {
 	d1, ok1 := sqlx.DefaultValue(from)
 	d2, ok2 := sqlx.DefaultValue(to)
@@ -81,6 +85,20 @@ func (d *diff) defaultChanged(from, to *schema.Column) (bool, error) {
 	return !equals, nil
 }
 
+// generatedChanged reports if the generated expression of a column was changed.
+func (*diff) generatedChanged(from, to *schema.Column) (bool, error) {
+	var fromX, toX schema.GeneratedExpr
+	switch fromHas, toHas := sqlx.Has(from.Attrs, &fromX), sqlx.Has(to.Attrs, &toX); {
+	case fromHas && toHas && fromX.Expr != toX.Expr:
+		return false, fmt.Errorf("changing the generation expression for a column %q is not supported", from.Name)
+	case !fromHas && toHas:
+		return false, fmt.Errorf("changing column %q to generated column is not supported (drop and add is required)", from.Name)
+	default:
+		// Only DROP EXPRESSION is supported.
+		return fromHas && !toHas, nil
+	}
+}
+
 // IsGeneratedIndexName reports if the index name was generated by the database.
 func (d *diff) IsGeneratedIndexName(t *schema.Table, idx *schema.Index) bool {
 	names := make([]string, len(idx.Parts))
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 2e16fdfb..19460ff8 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -387,16 +387,16 @@ func (i *inspect) addIndexes(s *schema.Schema, rows *sql.Rows) error {
 			})
 		}
 		switch {
-		case sqlx.ValidString(expr):
-			part.X = &schema.RawExpr{
-				X: expr.String,
-			}
 		case sqlx.ValidString(column):
 			part.C, ok = t.Column(column.String)
 			if !ok {
 				return fmt.Errorf("postgres: column %q was not found for index %q", column.String, idx.Name)
 			}
 			part.C.Indexes = append(part.C.Indexes, idx)
+		case sqlx.ValidString(expr):
+			part.X = &schema.RawExpr{
+				X: expr.String,
+			}
 		default:
 			return fmt.Errorf("postgres: invalid part for index %q", idx.Name)
 		}
@@ -777,25 +777,31 @@ SELECT
 	idx.indisunique AS unique,
 	c.contype AS constraint_type,
 	pg_get_expr(idx.indpred, idx.indrelid) AS predicate,
-	pg_get_expr(idx.indexprs, idx.indrelid) AS expression,
+	pg_get_indexdef(idx.indexrelid, idx.ord, false) AS expression,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'desc') AS desc,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'nulls_first') AS nulls_first,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'nulls_last') AS nulls_last,
 	obj_description(to_regclass($1 || i.relname)::oid) AS comment
 FROM
-	pg_index idx
+	(
+		select
+			*,
+			generate_series(1,array_length(i.indkey,1)) as ord,
+			unnest(i.indkey) AS key
+		from pg_index i
+	) idx
 	JOIN pg_class i ON i.oid = idx.indexrelid
 	JOIN pg_class t ON t.oid = idx.indrelid
 	JOIN pg_namespace n ON n.oid = t.relnamespace
 	LEFT JOIN pg_constraint c ON idx.indexrelid = c.conindid
-	LEFT JOIN pg_attribute a ON a.attrelid = idx.indexrelid
+	LEFT JOIN pg_attribute a ON (a.attrelid, a.attnum) = (idx.indrelid, idx.key)
 	JOIN pg_am am ON am.oid = i.relam
 WHERE
 	n.nspname = $1
 	AND t.relname IN (%s)
 	AND COALESCE(c.contype, '') <> 'f'
 ORDER BY
-	table_name, index_name, a.attnum
+	table_name, index_name, idx.ord
 `
 	fksQuery = `
 SELECT
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 1d851f3b..a1848919 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -306,10 +306,13 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 				if err := s.alterColumn(b, change.Change, change.To); err != nil {
 					return err
 				}
+				if change.Change.Is(schema.ChangeGenerated) {
+					reversible = false
+				}
 				reverse = append(reverse, &schema.ModifyColumn{
 					From:   change.To,
 					To:     change.From,
-					Change: change.Change,
+					Change: change.Change & ^schema.ChangeGenerated,
 				})
 			case *schema.AddForeignKey:
 				b.P("ADD")
@@ -624,6 +627,12 @@ func (s *state) alterColumn(b *sqlx.Builder, k schema.ChangeKind, c *schema.Colu
 			// https://www.postgresql.org/docs/current/sql-altersequence.html
 			b.P("SET GENERATED", id.Generation, "SET START WITH", strconv.FormatInt(id.Sequence.Start, 10), "SET INCREMENT BY", strconv.FormatInt(id.Sequence.Increment, 10), "RESTART")
 			k &= ^schema.ChangeAttr
+		case k.Is(schema.ChangeGenerated):
+			if sqlx.Has(c.Attrs, &schema.GeneratedExpr{}) {
+				return fmt.Errorf("unexpected generation expression change (expect DROP EXPRESSION): %v", c.Attrs)
+			}
+			b.P("DROP EXPRESSION")
+			k &= ^schema.ChangeGenerated
 		case k.Is(schema.ChangeComment):
 			// Handled separately on modifyTable.
 			k &= ^schema.ChangeComment
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index d6e87200..8d5fb52b 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -107,6 +107,7 @@ var (
 		schemahcl.WithTypes(TypeRegistry.Specs()),
 		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeGIN, IndexTypeGiST),
 		schemahcl.WithScopedEnums("table.column.identity.generated", GeneratedTypeAlways, GeneratedTypeByDefault),
+		schemahcl.WithScopedEnums("table.column.as.type", "STORED"),
 		schemahcl.WithScopedEnums("table.foreign_key.on_update", specutil.ReferenceVars...),
 		schemahcl.WithScopedEnums("table.foreign_key.on_delete", specutil.ReferenceVars...),
 	)
@@ -143,6 +144,9 @@ func convertColumn(spec *sqlspec.Column, _ *schema.Table) (*schema.Column, error
 		}
 		c.Attrs = append(c.Attrs, id)
 	}
+	if err := specutil.ConvertGenExpr(spec.Remain(), c, generatedType); err != nil {
+		return nil, err
+	}
 	return c, nil
 }
 
@@ -345,6 +349,9 @@ func columnSpec(c *schema.Column, _ *schema.Table) (*sqlspec.Column, error) {
 	if i := (&Identity{}); sqlx.Has(c.Attrs, i) {
 		s.Extra.Children = append(s.Extra.Children, fromIdentity(i))
 	}
+	if x := (schema.GeneratedExpr{}); sqlx.Has(c.Attrs, &x) {
+		s.Extra.Children = append(s.Extra.Children, specutil.FromGenExpr(x, generatedType))
+	}
 	return s, nil
 }
 
@@ -488,3 +495,6 @@ func formatTime() schemahcl.TypeSpecOption {
 		return FormatType(&schema.TimeType{T: t.T, Precision: &p})
 	})
 }
+
+// generatedType returns the default and only type for a generated column.
+func generatedType(string) string { return "STORED" }


Test patch:
diff --git a/internal/integration/testdata/mysql/column-generated.txt b/internal/integration/testdata/mysql/column-generated.txt
index 9cdceda8..bbed1503 100644
--- a/internal/integration/testdata/mysql/column-generated.txt
+++ b/internal/integration/testdata/mysql/column-generated.txt
@@ -4,9 +4,9 @@
 apply 1.hcl
 cmpshow users 1.sql
 
-! apply 2.fail1.hcl 'alter table "users": changing VIRTUAL generated column "b" to non-generated column is not supported (drop and add is required)'
-! apply 2.fail2.hcl 'alter table "users": changing column "a" to VIRTUAL generated column is not supported (drop and add is required)'
-! apply 2.fail3.hcl 'alter table "users": changing the store type of generated column "c" from "STORED" to "VIRTUAL" is not supported'
+! apply 2.fail1.hcl 'changing VIRTUAL generated column "b" to non-generated column is not supported (drop and add is required)'
+! apply 2.fail2.hcl 'changing column "a" to VIRTUAL generated column is not supported (drop and add is required)'
+! apply 2.fail3.hcl 'changing the store type of generated column "c" from "STORED" to "VIRTUAL" is not supported'
 
 apply 3.hcl
 cmpshow users 3.sql
diff --git a/schema/schemaspec/extension_test.go b/schema/schemaspec/extension_test.go
index 48385648..9f4daa8d 100644
--- a/schema/schemaspec/extension_test.go
+++ b/schema/schemaspec/extension_test.go
@@ -194,3 +194,20 @@ func TestListRef(t *testing.T) {
 	require.NoError(t, err)
 	require.EqualValues(t, resource, scan)
 }
+
+func TestNameAttr(t *testing.T) {
+	type Named struct {
+		Name string `spec:"name,name"`
+	}
+	schemaspec.Register("named", &Named{})
+	resource := &schemaspec.Resource{
+		Name: "id",
+		Attrs: []*schemaspec.Attr{
+			schemautil.StrLitAttr("name", "atlas"),
+		},
+	}
+	var tgt Named
+	err := resource.As(&tgt)
+	require.NoError(t, err)
+	require.EqualValues(t, "atlas", tgt.Name)
+}
diff --git a/schema/schemaspec/schemahcl/hcl_test.go b/schema/schemaspec/schemahcl/hcl_test.go
index 58e8067e..96ba4032 100644
--- a/schema/schemaspec/schemahcl/hcl_test.go
+++ b/schema/schemaspec/schemahcl/hcl_test.go
@@ -249,3 +249,25 @@ func TestQualified(t *testing.T) {
 	require.NoError(t, err)
 	require.EqualValues(t, h, string(out))
 }
+
+func TestNameAttr(t *testing.T) {
+	h := `
+named "block_id" {
+  name = "atlas"
+}
+ref = named.block_id.name
+`
+	type Named struct {
+		Name string `spec:"name,name"`
+	}
+	var test struct {
+		Named *Named `spec:"named"`
+		Ref   string `spec:"ref"`
+	}
+	err := Unmarshal([]byte(h), &test)
+	require.NoError(t, err)
+	require.EqualValues(t, &Named{
+		Name: "atlas",
+	}, test.Named)
+	require.EqualValues(t, "atlas", test.Ref)
+}
diff --git a/sql/mysql/diff_test.go b/sql/mysql/diff_test.go
index 2e23e822..65d3e491 100644
--- a/sql/mysql/diff_test.go
+++ b/sql/mysql/diff_test.go
@@ -237,7 +237,7 @@ func TestDiff_TableDiff(t *testing.T) {
 					AddColumns(
 						schema.NewIntColumn("c1", "int"),
 						schema.NewIntColumn("c2", "int").
-							SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1"}),
+							SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1", Type: "STORED"}),
 						schema.NewIntColumn("c3", "int").
 							SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1"}),
 						schema.NewIntColumn("c4", "int").
@@ -250,7 +250,7 @@ func TestDiff_TableDiff(t *testing.T) {
 					AddColumns(
 						// Add generated expression.
 						schema.NewIntColumn("c1", "int").
-							SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1"}),
+							SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1", Type: "STORED"}),
 						// Drop generated expression.
 						schema.NewIntColumn("c2", "int"),
 						// Modify generated expression.
@@ -383,7 +383,7 @@ func TestDiff_TableDiff(t *testing.T) {
 		require.NoError(t, err)
 		t.Run(tt.name, func(t *testing.T) {
 			changes, err := drv.TableDiff(tt.from, tt.to)
-			require.Equal(t, tt.wantErr, err != nil)
+			require.Equalf(t, tt.wantErr, err != nil, "error: %q", err)
 			require.EqualValues(t, tt.wantChanges, changes)
 		})
 	}
diff --git a/sql/postgres/diff_test.go b/sql/postgres/diff_test.go
index 06f04092..c7909a3b 100644
--- a/sql/postgres/diff_test.go
+++ b/sql/postgres/diff_test.go
@@ -121,6 +121,46 @@ func TestDiff_TableDiff(t *testing.T) {
 				},
 			},
 		},
+		func() testcase {
+			var (
+				s    = schema.New("public")
+				from = schema.NewTable("t1").
+					SetSchema(s).
+					AddColumns(
+						schema.NewIntColumn("c1", "int").
+							SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1", Type: "STORED"}),
+					)
+				to = schema.NewTable("t1").
+					SetSchema(s).
+					AddColumns(
+						schema.NewIntColumn("c1", "int"),
+					)
+			)
+			return testcase{
+				name: "drop generation expression",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.ModifyColumn{From: from.Columns[0], To: to.Columns[0], Change: schema.ChangeGenerated},
+				},
+			}
+		}(),
+		{
+			name: "change generation expression",
+			from: schema.NewTable("t1").
+				SetSchema(schema.New("public")).
+				AddColumns(
+					schema.NewIntColumn("c1", "int").
+						SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1", Type: "STORED"}),
+				),
+			to: schema.NewTable("t1").
+				SetSchema(schema.New("public")).
+				AddColumns(
+					schema.NewIntColumn("c1", "int").
+						SetGeneratedExpr(&schema.GeneratedExpr{Expr: "2", Type: "STORED"}),
+				),
+			wantErr: true,
+		},
 		func() testcase {
 			var (
 				from = &schema.Table{
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 7b36d39d..b6769424 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -38,7 +38,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid  
+ table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid
 -------------+--------------+-----------------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+-------------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
  users       |  id          | bigint                      | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8        | YES         |      100       |          1         |    BY DEFAULT       |                       |         | b       |    20
  users       |  rank        | integer                     | YES         |                                 |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                     |                       | rank    | b       |    23
@@ -132,18 +132,21 @@ table_name | column_name |      data_type      | is_nullable |         column_de
 -----------+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
 users      | id          | bigint              | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |                       |         | b       |    20
 users      | c1          | smallint            | NO          |                                 |                          |                16 |                    |             0 |                    |                | int2     | NO          |                |                    |                     |                       |         | b       |    21
+users      | parent_id   | bigint              | YES         |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |                       |         | b       |    22
 `))
 				m.ExpectQuery(queryIndexes).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
    table_name   |    index_name   | index_type  | column_name | primary | unique | constraint_type | predicate             |   expression              | desc | nulls_first | nulls_last | comment
 ----------------+-----------------+-------------+-------------+---------+--------+-----------------+-----------------------+---------------------------+------+-------------+------------+-----------
-users           | idx             | hash        | left        | f       | f      |                 |                       | "left"((c11)::text, 100)  | t    | t           | f          | boring
-users           | idx1            | btree       | left        | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | t    | t           | f          |
-users           | t1_c1_key       | btree       | c1          | f       | t      | u               |                       |                           | t    | t           | f          |
-users           | t1_pkey         | btree       | id          | t       | t      | p               |                       |                           | t    | f           | f          |
-users           | idx4            | btree       | c1          | f       | t      |                 |                       |                           | f    | f           | f          |
-users           | idx4            | btree       | id          | f       | t      |                 |                       |                           | f    | f           | t          |	
+users           | idx             | hash        |             | f       | f      |                 |                       | "left"((c11)::text, 100)  | t    | t           | f          | boring
+users           | idx1            | btree       |             | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | t    | t           | f          |
+users           | t1_c1_key       | btree       | c1          | f       | t      | u               |                       | c1                        | t    | t           | f          |
+users           | t1_pkey         | btree       | id          | t       | t      | p               |                       | id                        | t    | f           | f          |
+users           | idx4            | btree       | c1          | f       | t      |                 |                       | c1                        | f    | f           | f          |
+users           | idx4            | btree       | id          | f       | t      |                 |                       | id                        | f    | f           | t          |
+users           | idx5            | btree       | c1          | f       | t      |                 |                       | c1                        | f    | f           | f          |
+users           | idx5            | btree       |             | f       | t      |                 |                       | coalesce(parent_id, 0)    | f    | f           | f          |
 `))
 				m.noFKs()
 				m.noChecks()
@@ -154,12 +157,14 @@ users           | idx4            | btree       | id          | f       | t
 				columns := []*schema.Column{
 					{Name: "id", Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}}},
 					{Name: "c1", Type: &schema.ColumnType{Raw: "smallint", Type: &schema.IntegerType{T: "smallint"}}},
+					{Name: "parent_id", Type: &schema.ColumnType{Raw: "bigint", Null: true, Type: &schema.IntegerType{T: "bigint"}}},
 				}
 				indexes := []*schema.Index{
 					{Name: "idx", Table: t, Attrs: []schema.Attr{&IndexType{T: "hash"}, &schema.Comment{Text: "boring"}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
 					{Name: "idx1", Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &IndexPredicate{P: `(id <> NULL::integer)`}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
 					{Name: "t1_c1_key", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &ConType{T: "u"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1], Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
 					{Name: "idx4", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1]}, {SeqNo: 2, C: columns[0], Attrs: []schema.Attr{&IndexColumnProperty{NullsLast: true}}}}},
+					{Name: "idx5", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1]}, {SeqNo: 2, X: &schema.RawExpr{X: `coalesce(parent_id, 0)`}}}},
 				}
 				pk := &schema.Index{
 					Name:   "t1_pkey",
@@ -499,7 +504,7 @@ type mock struct {
 func (m mock) version(version string) {
 	m.ExpectQuery(sqltest.Escape(paramsQuery)).
 		WillReturnRows(sqltest.Rows(`
-  setting   
+  setting
 ------------
  en_US.utf8
  en_US.utf8
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 349ca137..61e2433c 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -217,6 +217,34 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		{
+			changes: []schema.Change{
+				&schema.ModifyTable{
+					T: schema.NewTable("posts").
+						AddColumns(
+							schema.NewIntColumn("c1", "int").
+								SetGeneratedExpr(&schema.GeneratedExpr{Expr: "id+1"}),
+						),
+					Changes: []schema.Change{
+						&schema.ModifyColumn{
+							Change: schema.ChangeGenerated,
+							From: schema.NewIntColumn("c1", "int").
+								SetGeneratedExpr(&schema.GeneratedExpr{Expr: "id+1"}),
+							To: schema.NewIntColumn("c1", "int"),
+						},
+					},
+				},
+			},
+			plan: &migrate.Plan{
+				Reversible:    false,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd: `ALTER TABLE "posts" ALTER COLUMN "c1" DROP EXPRESSION`,
+					},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				&schema.AddTable{
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index e84a2084..a85686ce 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -368,6 +368,85 @@ table "t" {
 	})
 }
 
+func TestMarshalSpec_GeneratedColumn(t *testing.T) {
+	s := schema.New("test").
+		AddTables(
+			schema.NewTable("users").
+				AddColumns(
+					schema.NewIntColumn("c1", "int").
+						SetGeneratedExpr(&schema.GeneratedExpr{Expr: "c1 * 2"}),
+					schema.NewIntColumn("c2", "int").
+						SetGeneratedExpr(&schema.GeneratedExpr{Expr: "c3 * c4", Type: "STORED"}),
+				),
+		)
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "users" {
+  schema = schema.test
+  column "c1" {
+    null = false
+    type = int
+    as {
+      expr = "c1 * 2"
+      type = STORED
+    }
+  }
+  column "c2" {
+    null = false
+    type = int
+    as {
+      expr = "c3 * c4"
+      type = STORED
+    }
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
+func TestUnmarshalSpec_GeneratedColumns(t *testing.T) {
+	var (
+		s schema.Schema
+		f = `
+schema "test" {}
+table "users" {
+	schema = schema.test
+	column "c1" {
+		type = int
+		as = "1"
+	}
+	column "c2" {
+		type = int
+		as {
+			expr = "2"
+		}
+	}
+	column "c3" {
+		type = int
+		as {
+			expr = "3"
+			type = STORED
+		}
+	}
+}
+`
+	)
+	err := UnmarshalHCL([]byte(f), &s)
+	require.NoError(t, err)
+	exp := schema.New("test").
+		AddTables(
+			schema.NewTable("users").
+				AddColumns(
+					schema.NewIntColumn("c1", "int").SetGeneratedExpr(&schema.GeneratedExpr{Expr: "1", Type: "STORED"}),
+					schema.NewIntColumn("c2", "int").SetGeneratedExpr(&schema.GeneratedExpr{Expr: "2", Type: "STORED"}),
+					schema.NewIntColumn("c3", "int").SetGeneratedExpr(&schema.GeneratedExpr{Expr: "3", Type: "STORED"}),
+				),
+		)
+	require.EqualValues(t, exp, &s)
+}
+
 func TestMarshalSpec_Enum(t *testing.T) {
 	s := schema.New("test").
 		AddTables(