Repository: ariga/atlas
Pull request #1133 (2022-09-07 12:42:16)

sql/postgres: support index INCLUDEd columns
https://github.com/ent/ent/issues/2905


Patch:
diff --git a/doc/md/atlas-schema/sql.mdx b/doc/md/atlas-schema/sql.mdx
index 29080227..dc2a0829 100644
--- a/doc/md/atlas-schema/sql.mdx
+++ b/doc/md/atlas-schema/sql.mdx
@@ -404,6 +404,11 @@ index "idx_range" {
   columns = [column.range]
   page_per_range = 128
 }
+
+index "idx_include" {
+  columns = [column.range]
+  include = [column.version]
+}
 ```
 
 #### Properties
diff --git a/schemahcl/spec.go b/schemahcl/spec.go
index 1652ffa2..f16f4df9 100644
--- a/schemahcl/spec.go
+++ b/schemahcl/spec.go
@@ -158,15 +158,32 @@ func (a *Attr) Ref() (string, error) {
 	return ref.V, nil
 }
 
+// Refs returns a slice of references.
+func (a *Attr) Refs() ([]*Ref, error) {
+	l, ok := a.V.(*ListValue)
+	if !ok {
+		return nil, fmt.Errorf("schema: attribute %q is not a list", a.K)
+	}
+	refs := make([]*Ref, 0, len(l.V))
+	for _, v := range l.V {
+		r, ok := v.(*Ref)
+		if !ok {
+			return nil, fmt.Errorf("schemahcl: expected %T to be Ref", v)
+		}
+		refs = append(refs, r)
+	}
+	return refs, nil
+}
+
 // Strings returns a slice of strings from the Value of the Attr. If The value is not a ListValue or its
 // values cannot be converted to strings an error is returned.
 func (a *Attr) Strings() ([]string, error) {
-	lst, ok := a.V.(*ListValue)
+	l, ok := a.V.(*ListValue)
 	if !ok {
 		return nil, fmt.Errorf("schema: attribute %q is not a list", a.K)
 	}
-	out := make([]string, 0, len(lst.V))
-	for _, item := range lst.V {
+	out := make([]string, 0, len(l.V))
+	for _, item := range l.V {
 		sv, err := StrVal(item)
 		if err != nil {
 			return nil, fmt.Errorf("schemahcl: failed parsing item %q to string: %w", item, err)
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index c1b850bb..422e513c 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -164,6 +164,9 @@ func (*diff) IndexAttrChanged(from, to []schema.Attr) bool {
 	if sqlx.Has(from, &p1) != sqlx.Has(to, &p2) || (p1.P != p2.P && p1.P != sqlx.MayWrap(p2.P)) {
 		return true
 	}
+	if indexIncludeChanged(from, to) {
+		return true
+	}
 	s1, ok1 := indexStorageParams(from)
 	s2, ok2 := indexStorageParams(to)
 	return ok1 != ok2 || ok1 && *s1 != *s2
@@ -352,6 +355,20 @@ func indexStorageParams(attrs []schema.Attr) (*IndexStorageParams, bool) {
 	return s, true
 }
 
+// indexIncludeChanged reports if the INCLUDE attribute clause was changed.
+func indexIncludeChanged(from, to []schema.Attr) bool {
+	var fromI, toI IndexInclude
+	if sqlx.Has(from, &fromI) != sqlx.Has(to, &toI) || len(fromI.Columns) != len(toI.Columns) {
+		return true
+	}
+	for i := range fromI.Columns {
+		if fromI.Columns[i].Name != toI.Columns[i].Name {
+			return true
+		}
+	}
+	return false
+}
+
 func trimCast(s string) string {
 	i := strings.LastIndex(s, "::")
 	if i == -1 {
diff --git a/sql/postgres/driver.go b/sql/postgres/driver.go
index c9117688..2343e377 100644
--- a/sql/postgres/driver.go
+++ b/sql/postgres/driver.go
@@ -11,13 +11,13 @@ import (
 	"fmt"
 	"hash/fnv"
 	"net/url"
+	"strconv"
 	"time"
 
 	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlclient"
-	"golang.org/x/mod/semver"
 )
 
 type (
@@ -37,7 +37,7 @@ type (
 		// System variables that are set on `Open`.
 		collate string
 		ctype   string
-		version string
+		version int
 		crdb    bool
 	}
 )
@@ -92,13 +92,12 @@ func Open(db schema.ExecQuerier) (migrate.Driver, error) {
 	if len(params) != 3 && len(params) != 4 {
 		return nil, fmt.Errorf("postgres: unexpected number of rows: %d", len(params))
 	}
-	c.version, c.ctype, c.collate = params[0], params[1], params[2]
-	if len(c.version) != 6 {
-		return nil, fmt.Errorf("postgres: malformed version: %s", c.version)
+	c.ctype, c.collate = params[1], params[2]
+	if c.version, err = strconv.Atoi(params[0]); err != nil {
+		return nil, fmt.Errorf("postgres: malformed version: %s: %w", params[0], err)
 	}
-	c.version = fmt.Sprintf("%s.%s.%s", c.version[:2], c.version[2:4], c.version[4:])
-	if semver.Compare("v"+c.version, "v10.0.0") != -1 {
-		return nil, fmt.Errorf("postgres: unsupported postgres version: %s", c.version)
+	if c.version < 10_00_00 {
+		return nil, fmt.Errorf("postgres: unsupported postgres version: %d", c.version)
 	}
 	// Means we are connected to CockroachDB because we have a result for name='crdb_version'. see `paramsQuery`.
 	if c.crdb = len(params) == 4; c.crdb {
@@ -286,6 +285,11 @@ func acquire(ctx context.Context, conn schema.ExecQuerier, id uint32, timeout ti
 	}
 }
 
+// supportsIndexInclude reports if the server supports the INCLUDE clause.
+func (c *conn) supportsIndexInclude() bool {
+	return c.version >= 11_00_00
+}
+
 type parser struct{}
 
 // ParseURL implements the sqlclient.URLParser interface.
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 94c83421..4585ecfb 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -295,10 +295,14 @@ func (i *inspect) enumValues(ctx context.Context, s *schema.Schema) error {
 
 // indexes queries and appends the indexes of the given table.
 func (i *inspect) indexes(ctx context.Context, s *schema.Schema) error {
-	if i.conn.crdb {
+	query := indexesQuery
+	switch {
+	case i.conn.crdb:
 		return i.crdbIndexes(ctx, s)
+	case !i.conn.supportsIndexInclude():
+		query = indexesQueryNoInclude
 	}
-	rows, err := i.querySchema(ctx, indexesQuery, s)
+	rows, err := i.querySchema(ctx, query, s)
 	if err != nil {
 		return fmt.Errorf("postgres: querying schema %q indexes: %w", s.Name, err)
 	}
@@ -314,12 +318,12 @@ func (i *inspect) addIndexes(s *schema.Schema, rows *sql.Rows) error {
 	names := make(map[string]*schema.Index)
 	for rows.Next() {
 		var (
-			uniq, primary                                 bool
+			uniq, primary, included                       bool
 			table, name, typ                              string
 			desc, nullsfirst, nullslast                   sql.NullBool
 			column, contype, pred, expr, comment, options sql.NullString
 		)
-		if err := rows.Scan(&table, &name, &typ, &column, &primary, &uniq, &contype, &pred, &expr, &desc, &nullsfirst, &nullslast, &comment, &options); err != nil {
+		if err := rows.Scan(&table, &name, &typ, &column, &included, &primary, &uniq, &contype, &pred, &expr, &desc, &nullsfirst, &nullslast, &comment, &options); err != nil {
 			return fmt.Errorf("postgres: scanning indexes for schema %q: %w", s.Name, err)
 		}
 		t, ok := s.Table(table)
@@ -367,20 +371,30 @@ func (i *inspect) addIndexes(s *schema.Schema, rows *sql.Rows) error {
 			})
 		}
 		switch {
+		case included:
+			c, ok := t.Column(column.String)
+			if !ok {
+				return fmt.Errorf("postgres: INCLUDE column %q was not found for index %q", column.String, idx.Name)
+			}
+			var include IndexInclude
+			sqlx.Has(idx.Attrs, &include)
+			include.Columns = append(include.Columns, c)
+			schema.ReplaceOrAppend(&idx.Attrs, &include)
 		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)
+			idx.Parts = append(idx.Parts, part)
 		case sqlx.ValidString(expr):
 			part.X = &schema.RawExpr{
 				X: expr.String,
 			}
+			idx.Parts = append(idx.Parts, part)
 		default:
 			return fmt.Errorf("postgres: invalid part for index %q", idx.Name)
 		}
-		idx.Parts = append(idx.Parts, part)
 	}
 	return nil
 }
@@ -750,6 +764,14 @@ type (
 		PagesPerRange int64
 	}
 
+	// IndexInclude describes the INCLUDE clause allows specifying
+	// a list of column which added to the index as non-key columns.
+	// https://www.postgresql.org/docs/current/sql-createindex.html
+	IndexInclude struct {
+		schema.Attr
+		Columns []*schema.Column
+	}
+
 	// NoInherit attribute defines the NO INHERIT flag for CHECK constraint.
 	// https://postgresql.org/docs/current/catalog-pg-constraint.html
 	NoInherit struct {
@@ -963,45 +985,6 @@ WHERE
 ORDER BY
 	t1.table_name, t1.ordinal_position
 `
-
-	// Query to list table indexes.
-	indexesQuery = `
-SELECT
-	t.relname AS table_name,
-	i.relname AS index_name,
-	am.amname AS index_type,
-	a.attname AS column_name,
-	idx.indisprimary AS primary,
-	idx.indisunique AS unique,
-	c.contype AS constraint_type,
-	pg_get_expr(idx.indpred, idx.indrelid) AS predicate,
-	pg_get_indexdef(idx.indexrelid, idx.ord, false) AS expression,
-	pg_index_column_has_property(idx.indexrelid, idx.ord, 'desc') AS desc,
-	pg_index_column_has_property(idx.indexrelid, idx.ord, 'nulls_first') AS nulls_first,
-	pg_index_column_has_property(idx.indexrelid, idx.ord, 'nulls_last') AS nulls_last,
-	obj_description(i.oid, 'pg_class') AS comment,
-	i.reloptions AS options
-FROM
-	(
-		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, 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, idx.ord
-`
 	fksQuery = `
 SELECT
     t1.constraint_name,
@@ -1059,3 +1042,46 @@ ORDER BY
 	t1.conname, array_position(t1.conkey, t2.attnum)
 `
 )
+
+var (
+	indexesQuery          = fmt.Sprintf(indexesQueryTmpl, "(a.attname <> '' AND idx.indnatts > idx.indnkeyatts AND idx.ord > idx.indnkeyatts)", "%s")
+	indexesQueryNoInclude = fmt.Sprintf(indexesQueryTmpl, "false", "%s")
+	indexesQueryTmpl      = `
+SELECT
+	t.relname AS table_name,
+	i.relname AS index_name,
+	am.amname AS index_type,
+	a.attname AS column_name,
+	%s AS included,
+	idx.indisprimary AS primary,
+	idx.indisunique AS unique,
+	c.contype AS constraint_type,
+	pg_get_expr(idx.indpred, idx.indrelid) AS predicate,
+	pg_get_indexdef(idx.indexrelid, idx.ord, false) AS expression,
+	pg_index_column_has_property(idx.indexrelid, idx.ord, 'desc') AS desc,
+	pg_index_column_has_property(idx.indexrelid, idx.ord, 'nulls_first') AS nulls_first,
+	pg_index_column_has_property(idx.indexrelid, idx.ord, 'nulls_last') AS nulls_last,
+	obj_description(i.oid, 'pg_class') AS comment,
+	i.reloptions AS options
+FROM
+	(
+		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, 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, idx.ord
+`
+)
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index e22c26cc..8739a68f 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -923,8 +923,13 @@ func (s *state) index(b *sqlx.Builder, idx *schema.Index) {
 		b.P("USING", t.T)
 	}
 	s.indexParts(b, idx.Parts)
-	if p := (IndexPredicate{}); sqlx.Has(idx.Attrs, &p) {
-		b.P("WHERE").P(p.P)
+	if c := (IndexInclude{}); sqlx.Has(idx.Attrs, &c) {
+		b.P("INCLUDE")
+		b.Wrap(func(b *sqlx.Builder) {
+			b.MapComma(c.Columns, func(i int, b *sqlx.Builder) {
+				b.Ident(c.Columns[i].Name)
+			})
+		})
 	}
 	if p, ok := indexStorageParams(idx.Attrs); ok {
 		b.P("WITH")
@@ -939,9 +944,12 @@ func (s *state) index(b *sqlx.Builder, idx *schema.Index) {
 			b.WriteString(strings.Join(parts, ", "))
 		})
 	}
+	if p := (IndexPredicate{}); sqlx.Has(idx.Attrs, &p) {
+		b.P("WHERE").P(p.P)
+	}
 	for _, attr := range idx.Attrs {
 		switch attr.(type) {
-		case *schema.Comment, *ConType, *IndexType, *IndexPredicate, *IndexStorageParams:
+		case *schema.Comment, *ConType, *IndexType, *IndexInclude, *IndexPredicate, *IndexStorageParams:
 		default:
 			panic(fmt.Sprintf("unexpected index attribute: %T", attr))
 		}
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 37e04fec..1dff4086 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -318,6 +318,22 @@ func convertIndex(spec *sqlspec.Index, t *schema.Table) (*schema.Index, error) {
 		}
 		idx.Attrs = append(idx.Attrs, &IndexStorageParams{PagesPerRange: p})
 	}
+	if attr, ok := spec.Attr("include"); ok {
+		refs, err := attr.Refs()
+		if err != nil {
+			return nil, err
+		}
+		if len(refs) == 0 {
+			return nil, fmt.Errorf("unexpected empty INCLUDE in index %q definition", spec.Name)
+		}
+		include := make([]*schema.Column, len(refs))
+		for i, r := range refs {
+			if include[i], err = specutil.ColumnByRef(t, r); err != nil {
+				return nil, err
+			}
+		}
+		idx.Attrs = append(idx.Attrs, &IndexInclude{Columns: include})
+	}
 	return idx, nil
 }
 
@@ -482,6 +498,16 @@ func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) && i.T != IndexTypeBTree {
 		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
 	}
+	if i := (IndexInclude{}); sqlx.Has(idx.Attrs, &i) && len(i.Columns) > 0 {
+		attr := &schemahcl.ListValue{}
+		for _, c := range i.Columns {
+			attr.V = append(attr.V, specutil.ColumnRef(c.Name))
+		}
+		spec.Extra.Attrs = append(spec.Extra.Attrs, &schemahcl.Attr{
+			K: "include",
+			V: attr,
+		})
+	}
 	if i := (IndexPredicate{}); sqlx.Has(idx.Attrs, &i) && i.P != "" {
 		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("where", strconv.Quote(i.P)))
 	}


Test patch:
diff --git a/internal/integration/testdata/postgres/index-include.txt b/internal/integration/testdata/postgres/index-include.txt
new file mode 100644
index 00000000..f3ac9c00
--- /dev/null
+++ b/internal/integration/testdata/postgres/index-include.txt
@@ -0,0 +1,148 @@
+! only postgres10
+
+# Create with INCLUDE.
+apply 1.hcl
+cmpshow users 1.sql
+
+# Append one column to INCLUDE.
+apply 2.hcl
+cmpshow users 2.sql
+
+# Remove one column from INCLUDE.
+apply 3.hcl
+cmpshow users 3.sql
+
+# Remove INCLUDE.
+apply 4.hcl
+cmpshow users 4.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    null = false
+    type = text
+  }
+  column "active" {
+    null = true
+    type = boolean
+  }
+  index "users_name" {
+    columns = [column.name]
+    where = "active"
+    include = [column.active]
+  }
+}
+
+-- 1.sql --
+        Table "script_index_include.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ name   | text    |           | not null |
+ active | boolean |           |          |
+Indexes:
+    "users_name" btree (name) INCLUDE (active) WHERE active
+
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    null = false
+    type = text
+  }
+  column "active" {
+    null = true
+    type = boolean
+  }
+  column "version" {
+    null = true
+    type = text
+  }
+  index "users_name" {
+    columns = [column.name]
+    where = "active"
+    include = [column.active, column.version]
+  }
+}
+
+-- 2.sql --
+      Table "script_index_include.users"
+ Column  |  Type   | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ name    | text    |           | not null |
+ active  | boolean |           |          |
+ version | text    |           |          |
+Indexes:
+    "users_name" btree (name) INCLUDE (active, version) WHERE active
+
+-- 3.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    null = false
+    type = text
+  }
+  column "active" {
+    null = true
+    type = boolean
+  }
+  column "version" {
+    null = true
+    type = text
+  }
+  index "users_name" {
+    columns = [column.name]
+    where = "active"
+    include = [column.version]
+  }
+}
+
+-- 3.sql --
+      Table "script_index_include.users"
+ Column  |  Type   | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ name    | text    |           | not null |
+ active  | boolean |           |          |
+ version | text    |           |          |
+Indexes:
+    "users_name" btree (name) INCLUDE (version) WHERE active
+
+-- 4.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    null = false
+    type = text
+  }
+  column "active" {
+    null = true
+    type = boolean
+  }
+  column "version" {
+    null = true
+    type = text
+  }
+  index "users_name" {
+    columns = [column.name]
+    where = "active"
+  }
+}
+
+-- 4.sql --
+      Table "script_index_include.users"
+ Column  |  Type   | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ name    | text    |           | not null |
+ active  | boolean |           |          |
+ version | text    |           |          |
+Indexes:
+    "users_name" btree (name) WHERE active
diff --git a/sql/postgres/diff_test.go b/sql/postgres/diff_test.go
index ed530d2c..e687a9e3 100644
--- a/sql/postgres/diff_test.go
+++ b/sql/postgres/diff_test.go
@@ -346,6 +346,9 @@ func TestDiff_TableDiff(t *testing.T) {
 				{Name: "c3_predicate", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
 				{Name: "c4_predicate", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexPredicate{P: "(c4 <> NULL)"}}},
 				{Name: "c4_storage_params", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexStorageParams{PagesPerRange: 4}}},
+				{Name: "c5_include_no_change", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexInclude{Columns: from.Columns[:1]}}},
+				{Name: "c5_include_added", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
+				{Name: "c5_include_dropped", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexInclude{Columns: from.Columns[:1]}}},
 			}
 			to.Indexes = []*schema.Index{
 				{Name: "c1_index", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[0]}}},
@@ -353,6 +356,9 @@ func TestDiff_TableDiff(t *testing.T) {
 				{Name: "c3_predicate", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexPredicate{P: "c3 <> NULL"}}},
 				{Name: "c4_predicate", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexPredicate{P: "c4 <> NULL"}}},
 				{Name: "c4_storage_params", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexStorageParams{PagesPerRange: 2}}},
+				{Name: "c5_include_no_change", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexInclude{Columns: from.Columns[:1]}}},
+				{Name: "c5_include_added", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}, Attrs: []schema.Attr{&IndexInclude{Columns: from.Columns[:1]}}},
+				{Name: "c5_include_dropped", Table: from, Parts: []*schema.IndexPart{{SeqNo: 1, C: from.Columns[1]}}},
 			}
 			return testcase{
 				name: "indexes",
@@ -363,6 +369,8 @@ func TestDiff_TableDiff(t *testing.T) {
 					&schema.DropIndex{I: from.Indexes[1]},
 					&schema.ModifyIndex{From: from.Indexes[2], To: to.Indexes[2], Change: schema.ChangeAttr},
 					&schema.ModifyIndex{From: from.Indexes[4], To: to.Indexes[4], Change: schema.ChangeAttr},
+					&schema.ModifyIndex{From: from.Indexes[6], To: to.Indexes[6], Change: schema.ChangeAttr},
+					&schema.ModifyIndex{From: from.Indexes[7], To: to.Indexes[7], Change: schema.ChangeAttr},
 					&schema.AddIndex{I: to.Indexes[1]},
 				},
 			}
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index cc70c396..720ea92d 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -153,17 +153,22 @@ users      | parent_id   | bigint              | int8      |  YES         |
 				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   | options
-----------------+-----------------+-------------+-------------+---------+--------+-----------------+-----------------------+---------------------------+------+-------------+------------+-----------+-----------
-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          |           |
-users           | idx6            | brin        | c1          | f       | t      |                 |                       |                           | f    | f           | f          |           | {autosummarize=true,pages_per_range=2}
+   table_name   |    index_name   | index_type  | column_name | included | primary | unique | constraint_type | predicate             |   expression              | desc | nulls_first | nulls_last | comment   | options
+----------------+-----------------+-------------+-------------+----------+---------+--------+-----------------+-----------------------+---------------------------+------+-------------+------------+-----------+-----------
+users           | idx             | hash        |             | f        | f       | f      |                 |                       | "left"((c11)::text, 100)  | t    | t           | f          | boring    |
+users           | idx1            | btree       |             | f        | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | t    | t           | f          |           |
+users           | t1_c1_key       | btree       | c1          | f        | f       | t      | u               |                       | c1                        | t    | t           | f          |           |
+users           | t1_pkey         | btree       | id          | f        | t       | t      | p               |                       | id                        | t    | f           | f          |           |
+users           | idx4            | btree       | c1          | f        | f       | t      |                 |                       | c1                        | f    | f           | f          |           |
+users           | idx4            | btree       | id          | f        | f       | t      |                 |                       | id                        | f    | f           | t          |           |
+users           | idx5            | btree       | c1          | f        | f       | t      |                 |                       | c1                        | f    | f           | f          |           |
+users           | idx5            | btree       |             | f        | f       | t      |                 |                       | coalesce(parent_id, 0)    | f    | f           | f          |           |
+users           | idx6            | brin        | c1          | f        | f       | t      |                 |                       |                           | f    | f           | f          |           | {autosummarize=true,pages_per_range=2}
+users           | idx2            | btree       |             | f        | f       | f      |                 |                       | ((c * 2))                 | f    | f           | t          |           | 
+users           | idx2            | btree       | c1          | f        | f       | f      |                 |                       | c                         | f    | f           | t          |           | 
+users           | idx2            | btree       | id          | f        | f       | f      |                 |                       | d                         | f    | f           | t          |           | 
+users           | idx2            | btree       | c1          | t        | f       | f      |                 |                       | c                         |      |             |            |           | 
+users           | idx2            | btree       | parent_id   | t        | f       | f      |                 |                       | d                         |      |             |            |           |
 `))
 				m.noFKs()
 				m.noChecks()
@@ -183,6 +188,7 @@ users           | idx6            | brin        | c1          | f       | t
 					{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)`}}}},
 					{Name: "idx6", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "brin"}, &IndexStorageParams{AutoSummarize: true, PagesPerRange: 2}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1]}}},
+					{Name: "idx2", Unique: false, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &IndexInclude{Columns: columns[1:]}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `((c * 2))`}, Attrs: []schema.Attr{&IndexColumnProperty{NullsLast: true}}}, {SeqNo: 2, C: columns[1], Attrs: []schema.Attr{&IndexColumnProperty{NullsLast: true}}}, {SeqNo: 3, C: columns[0], Attrs: []schema.Attr{&IndexColumnProperty{NullsLast: true}}}}},
 				}
 				pk := &schema.Index{
 					Name:   "t1_pkey",
@@ -191,7 +197,7 @@ users           | idx6            | brin        | c1          | f       | t
 					Attrs:  []schema.Attr{&IndexType{T: "btree"}, &ConType{T: "p"}},
 					Parts:  []*schema.IndexPart{{SeqNo: 1, C: columns[0], Desc: true}},
 				}
-				columns[0].Indexes = append(columns[0].Indexes, pk, indexes[3])
+				columns[0].Indexes = append(columns[0].Indexes, pk, indexes[3], indexes[6])
 				columns[1].Indexes = indexes[2:]
 				require.EqualValues(columns, t.Columns)
 				require.EqualValues(indexes, t.Indexes)
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index f975f69f..81111291 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -384,6 +384,17 @@ func TestPlanChanges(t *testing.T) {
 								From: &schema.Check{Name: "id_iseven", Expr: `("id" % 2 = 0)`},
 								To:   &schema.Check{Name: "id_iseven", Expr: `(("id") % 2 = 0)`},
 							},
+							&schema.AddIndex{
+								I: &schema.Index{
+									Name: "include_key",
+									Parts: []*schema.IndexPart{
+										{C: users.Columns[0]},
+									},
+									Attrs: []schema.Attr{
+										&IndexInclude{Columns: []*schema.Column{schema.NewColumn("a"), schema.NewColumn("b")}},
+									},
+								},
+							},
 						},
 					}
 				}(),
@@ -404,6 +415,10 @@ func TestPlanChanges(t *testing.T) {
 						Cmd:     `CREATE INDEX "id_brin" ON "users" USING BRIN ("id" DESC) WITH (pages_per_range = 2)`,
 						Reverse: `DROP INDEX "id_brin"`,
 					},
+					{
+						Cmd:     `CREATE INDEX "include_key" ON "users" ("id") INCLUDE ("a", "b")`,
+						Reverse: `DROP INDEX "include_key"`,
+					},
 					{
 						Cmd:     `COMMENT ON COLUMN "users" ."name" IS 'foo'`,
 						Reverse: `COMMENT ON COLUMN "users" ."name" IS ''`,
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 8c8a8072..67ed5771 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -10,6 +10,7 @@ import (
 	"testing"
 
 	"ariga.io/atlas/sql/internal/spectest"
+	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/schema"
 	"github.com/stretchr/testify/require"
 )
@@ -602,6 +603,99 @@ table "t" {
 	})
 }
 
+func TestUnmarshalSpec_IndexInclude(t *testing.T) {
+	f := `
+schema "s" {}
+table "t" {
+	schema = schema.s
+	column "c" {
+		type = int
+	}
+	column "d" {
+		type = int
+	}
+	index "c" {
+		columns = [
+			column.c,
+		]
+		include = [
+			column.d,
+		]
+	}
+}
+`
+	var s schema.Schema
+	err := EvalHCLBytes([]byte(f), &s, nil)
+	require.NoError(t, err)
+	require.Len(t, s.Tables, 1)
+	require.Len(t, s.Tables[0].Columns, 2)
+	require.Len(t, s.Tables[0].Indexes, 1)
+	idx, ok := s.Tables[0].Index("c")
+	require.True(t, ok)
+	require.Len(t, idx.Parts, 1)
+	require.Len(t, idx.Attrs, 1)
+	var include IndexInclude
+	require.True(t, sqlx.Has(idx.Attrs, &include))
+	require.Len(t, include.Columns, 1)
+	require.Equal(t, "d", include.Columns[0].Name)
+}
+
+func TestMarshalSpec_IndexInclude(t *testing.T) {
+	s := &schema.Schema{
+		Name: "test",
+		Tables: []*schema.Table{
+			{
+				Name: "users",
+				Columns: []*schema.Column{
+					{
+						Name: "c",
+						Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}},
+					},
+					{
+						Name: "d",
+						Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}},
+					},
+				},
+			},
+		},
+	}
+	s.Tables[0].Schema = s
+	s.Tables[0].Schema = s
+	s.Tables[0].Indexes = []*schema.Index{
+		{
+			Name:  "index",
+			Table: s.Tables[0],
+			Parts: []*schema.IndexPart{
+				{SeqNo: 0, C: s.Tables[0].Columns[0]},
+			},
+			Attrs: []schema.Attr{
+				&IndexInclude{Columns: s.Tables[0].Columns[1:]},
+			},
+		},
+	}
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "users" {
+  schema = schema.test
+  column "c" {
+    null = false
+    type = int
+  }
+  column "d" {
+    null = false
+    type = int
+  }
+  index "index" {
+    columns = [column.c]
+    include = [column.d]
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
 func TestMarshalSpec_GeneratedColumn(t *testing.T) {
 	s := schema.New("test").
 		AddTables(