Repository: ariga/atlas
Pull request #970 (2022-07-21 07:57:02)

sql/postgres: cross schema enums


Patch:
diff --git a/doc/md/ddl/sql-types.md b/doc/md/ddl/sql-types.md
index 531075ce..e61bae78 100644
--- a/doc/md/ddl/sql-types.md
+++ b/doc/md/ddl/sql-types.md
@@ -323,6 +323,33 @@ table "t" {
 
 ## PostgreSQL
 
+### Array
+
+Atlas supports defining PostgreSQL array types using the `sql` function.
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "c1" {
+    type = sql("int[]")
+  }
+  column "c2" {
+    type = sql("text[]")
+  }
+  column "c3" {
+    type = sql("int ARRAY")
+  }
+  column "c4" {
+    type = sql("varchar(255)[]")
+  }
+  column "c5" {
+    // The current PostgreSQL implementation
+    // ignores any supplied array size limits.
+    type = sql("point[4][4]")
+  }
+}
+```
+
 ### Bit
 
 The `bit` and `bit varying` types allow creating
@@ -414,6 +441,31 @@ table "t" {
 }
 ```
 
+### Enum
+
+The `enum` type allows storing a set of enumerated values.
+
+```hcl
+enum "status" {
+  schema = schema.test
+  values = ["on", "off"]
+}
+
+table "t1" {
+  schema = schema.test
+  column "c1" {
+    type = enum.status
+  }
+}
+
+table "t2" {
+  schema = schema.test
+  column "c1" {
+    type = enum.status
+  }
+}
+```
+
 ### Fixed Point (Decimal)
 
 The `decimal` and `numeric` types are supported for storing exact numeric values. Note that in PostgreSQL the two types
@@ -517,6 +569,35 @@ table "t" {
 }
 ```
 
+### JSON
+
+The `json` and `jsonb` types allow creating columns for storing JSON objects.
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "c1" {
+    type = json
+  }
+  column "c2" {
+    type = jsonb
+  }
+}
+```
+
+### Money
+
+The `money` data type allows creating columns for storing currency amount with a fixed fractional precision.
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "c1" {
+    type = money
+  }
+}
+```
+
 ### Network Address
 
 The `inet`, `cidr`, `macaddr` and `macaddr8` types allow creating network address columns.
@@ -539,6 +620,26 @@ table "t" {
 }
 ```
 
+### Serial
+
+PostgreSQL supports creating columns of types `smallserial`, `serial`, and `bigserial`. Note that these types are not
+_actual_ types, but more like "macros" for creating non-nullable integer columns with sequences attached.
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "c1" {
+      type = smallserial
+  }
+  column "c2" {
+      type = serial
+  }
+  column "c3" {
+      type = bigserial
+  }
+}
+```
+
 ### String
 
 The `varchar`, `char`, `character_varying`, `character` and `text` types allow creating string columns.
@@ -567,3 +668,33 @@ table "t" {
   }
 }
 ```
+
+### UUID
+
+The `uuid` data type allows creating columns for storing Universally Unique Identifiers (UUID).
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "c1" {
+    type = uuid
+  }
+  column "c2" {
+    type    = uuid
+    default = sql("gen_random_uuid()")
+  }
+}
+```
+
+### XML
+
+The `xml` data type allows creating columns for storing XML data.
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "c1" {
+    type = xml
+  }
+}
+```
\ No newline at end of file
diff --git a/sql/internal/sqlx/dev.go b/sql/internal/sqlx/dev.go
index 631f88ae..42d64f81 100644
--- a/sql/internal/sqlx/dev.go
+++ b/sql/internal/sqlx/dev.go
@@ -45,6 +45,9 @@ func (d *DevDriver) NormalizeRealm(ctx context.Context, r *schema.Realm) (nr *sc
 		}
 	)
 	for _, s := range r.Schemas {
+		if s.Realm != r {
+			s.Realm = r
+		}
 		dev := d.formatName(s.Name)
 		names[dev] = s.Name
 		s.Name = dev
@@ -59,6 +62,11 @@ func (d *DevDriver) NormalizeRealm(ctx context.Context, r *schema.Realm) (nr *sc
 			if t.Schema != s {
 				t.Schema = s
 			}
+			for _, c := range t.Columns {
+				if e, ok := c.Type.Type.(*schema.EnumType); ok && e.Schema != s {
+					e.Schema = s
+				}
+			}
 			changes = append(changes, &schema.AddTable{T: t})
 		}
 	}
diff --git a/sql/postgres/convert.go b/sql/postgres/convert.go
index abdf27ba..e38b627f 100644
--- a/sql/postgres/convert.go
+++ b/sql/postgres/convert.go
@@ -144,6 +144,8 @@ func FormatType(t schema.Type) (string, error) {
 		f = strings.ToLower(t.T)
 	case *UserDefinedType:
 		f = strings.ToLower(t.T)
+	case *XMLType:
+		f = strings.ToLower(t.T)
 	case *schema.UnsupportedType:
 		return "", fmt.Errorf("postgres: unsupported type: %q", t.T)
 	default:
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index ed1d9877..d2fec87f 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -213,12 +213,11 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 			return false, err
 		}
 		changed = t1 != t2
-	case *enumType:
-		toT := toT.(*schema.EnumType)
-		changed = fromT.T != toT.T || !sqlx.ValuesEqual(fromT.Values, toT.Values)
 	case *schema.EnumType:
 		toT := toT.(*schema.EnumType)
-		changed = fromT.T != toT.T || !sqlx.ValuesEqual(fromT.Values, toT.Values)
+		// Column type was changed if the underlying enum type was changed or values are not equal.
+		changed = !sqlx.ValuesEqual(fromT.Values, toT.Values) || fromT.T != toT.T ||
+			(toT.Schema != nil && fromT.Schema != nil && fromT.Schema.Name != toT.Schema.Name)
 	case *CurrencyType:
 		toT := toT.(*CurrencyType)
 		changed = fromT.T != toT.T
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index fdfbc454..215758a0 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -300,7 +300,7 @@ func columnType(c *columnDesc) (schema.Type, error) {
 		// values are filled in batch after the rows above is closed.
 		// https://postgresql.org/docs/current/catalog-pg-type.html
 		if c.typtype == "e" {
-			typ = &enumType{T: c.fmtype, ID: c.typid}
+			typ = newEnumType(c.fmtype, c.typid)
 		}
 	default:
 		typ = &schema.UnsupportedType{T: t}
@@ -321,9 +321,12 @@ func (i *inspect) enumValues(ctx context.Context, s *schema.Schema) error {
 				if _, ok := ids[enum.ID]; !ok {
 					args = append(args, enum.ID)
 				}
-				// Convert the intermediate type to the
-				// standard schema.EnumType.
-				e := &schema.EnumType{T: enum.T}
+				// Convert the intermediate type to
+				// the standard schema.EnumType.
+				e := &schema.EnumType{T: enum.T, Schema: s}
+				if enum.Schema != "" && enum.Schema != s.Name {
+					e.Schema = schema.New(enum.Schema)
+				}
 				c.Type.Type = e
 				c.Type.Raw = enum.T
 				ids[enum.ID] = append(ids[enum.ID], e)
@@ -615,10 +618,10 @@ func nArgs(start, n int) string {
 	return b.String()
 }
 
-var nextval = regexp.MustCompile(`(?i) *nextval\('(?:[\w$]+\.)*([\w$]+_[\w$]+_seq)'(?:::regclass)*\) *$`)
+var reNextval = regexp.MustCompile(`(?i) *nextval\('(?:[\w$]+\.)*([\w$]+_[\w$]+_seq)'(?:::regclass)*\) *$`)
 
 func defaultExpr(c *schema.Column, s string) {
-	switch m := nextval.FindStringSubmatch(s); {
+	switch m := reNextval.FindStringSubmatch(s); {
 	// The definition of "<column> <serial type>" is equivalent to specifying:
 	// "<column> <int type> NOT NULL DEFAULT nextval('<table>_<column>_seq')".
 	// https://postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL.
@@ -644,17 +647,15 @@ func defaultExpr(c *schema.Column, s string) {
 }
 
 func canConvert(t *schema.ColumnType, x string) (string, bool) {
-	r := t.Raw
-	if t, ok := t.Type.(*ArrayType); ok {
-		r = t.T
-	}
-	i := strings.Index(x, "::"+r)
+	i := strings.LastIndex(x, "::")
 	if i == -1 || !sqlx.IsQuoted(x[:i], '\'') {
 		return "", false
 	}
 	q := x[0:i]
 	x = x[1 : i-1]
 	switch t.Type.(type) {
+	case *enumType:
+		return q, true
 	case *schema.BoolType:
 		if sqlx.IsLiteralBool(x) {
 			return x, true
@@ -687,6 +688,7 @@ type (
 	enumType struct {
 		schema.Type
 		T      string // Type name.
+		Schema string // Optional schema name.
 		ID     int64  // Type id.
 		Values []string
 	}
@@ -910,6 +912,43 @@ func newIndexStorage(opts string) (*IndexStorageParams, error) {
 	return params, nil
 }
 
+// reEnumType extracts the enum type and an option schema qualifier.
+var reEnumType = regexp.MustCompile(`^(?:(".+"|\w+)\.)?(".+"|\w+)$`)
+
+func newEnumType(t string, id int64) *enumType {
+	var (
+		e     = &enumType{T: t, ID: id}
+		parts = reEnumType.FindStringSubmatch(e.T)
+		r     = func(s string) string {
+			s = strings.ReplaceAll(s, `""`, `"`)
+			if len(s) > 1 && s[0] == '"' && s[len(s)-1] == '"' {
+				s = s[1 : len(s)-1]
+			}
+			return s
+		}
+	)
+	if len(parts) > 1 {
+		e.Schema = r(parts[1])
+	}
+	if len(parts) > 2 {
+		e.T = r(parts[2])
+	}
+	return e
+}
+
+func enumIdent(s *schema.Schema, e *schema.EnumType) string {
+	switch {
+	// Enum schema has higher precedence.
+	case e.Schema != nil:
+		return fmt.Sprintf("%q.%q", e.Schema.Name, e.T)
+	// Fallback to table schema if exists.
+	case s != nil:
+		return fmt.Sprintf("%q.%q", s.Name, e.T)
+	default:
+		return strconv.Quote(e.T)
+	}
+}
+
 const (
 	// Query to list runtime parameters.
 	paramsQuery = `SELECT setting FROM pg_settings WHERE name IN ('lc_collate', 'lc_ctype', 'server_version_num', 'crdb_version') ORDER BY name DESC`
@@ -992,7 +1031,7 @@ FROM
 	JOIN pg_catalog.pg_namespace AS t2 ON t2.nspname = t1.table_schema
 	JOIN pg_catalog.pg_class AS t3 ON t3.relnamespace = t2.oid AND t3.relname = t1.table_name
 	JOIN pg_catalog.pg_attribute AS a ON a.attrelid = t3.oid AND a.attname = t1.column_name
-	LEFT JOIN pg_catalog.pg_type AS t4 ON t1.udt_name = t4.typname
+	LEFT JOIN pg_catalog.pg_type AS t4 ON t1.udt_name = t4.typname AND t4.typnamespace = t2.oid
 WHERE
 	t1.table_schema = $1 AND t1.table_name IN (%s)
 ORDER BY
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 8eaaabdc..74241637 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -28,6 +28,8 @@ func (p *planApply) PlanChanges(ctx context.Context, name string, changes []sche
 			Reversible:    true,
 			Transactional: true,
 		},
+		altered: make(map[string]*schema.EnumType),
+		created: make(map[string]*schema.EnumType),
 	}
 	if err := s.plan(ctx, changes); err != nil {
 		return nil, err
@@ -53,6 +55,9 @@ func (p *planApply) ApplyChanges(ctx context.Context, changes []schema.Change) e
 type state struct {
 	conn
 	migrate.Plan
+	// Track the enums that were altered or created
+	// in this phase to avoid duplicate updates.
+	altered, created map[string]*schema.EnumType
 }
 
 // Exec executes the changes on the database. An error is returned
@@ -121,7 +126,7 @@ func (s *state) topLevel(changes []schema.Change) []schema.Change {
 // addTable builds and executes the query for creating a table in a schema.
 func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 	// Create enum types before using them in the `CREATE TABLE` statement.
-	if err := s.addTypes(ctx, add.T.Columns...); err != nil {
+	if err := s.addTypes(ctx, add.T, add.T.Columns...); err != nil {
 		return err
 	}
 	var (
@@ -134,7 +139,7 @@ func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 	b.Table(add.T)
 	b.Wrap(func(b *sqlx.Builder) {
 		b.MapComma(add.T.Columns, func(i int, b *sqlx.Builder) {
-			if err := s.column(b, add.T.Columns[i]); err != nil {
+			if err := s.column(b, add.T, add.T.Columns[i]); err != nil {
 				errs = append(errs, err.Error())
 			}
 		})
@@ -250,7 +255,7 @@ func (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) err
 				F: change.To,
 			})
 		case *schema.AddColumn:
-			if err := s.addTypes(ctx, change.C); err != nil {
+			if err := s.addTypes(ctx, modify.T, change.C); err != nil {
 				return err
 			}
 			if c := (schema.Comment{}); sqlx.Has(change.C.Attrs, &c) {
@@ -274,8 +279,8 @@ func (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) err
 			to, ok2 := change.To.Type.Type.(*schema.EnumType)
 			switch {
 			// Enum was changed.
-			case ok1 && ok2 && from.T == to.T:
-				if err := s.alterType(from, to); err != nil {
+			case ok1 && ok2 && enumIdent(modify.T.Schema, from) == enumIdent(modify.T.Schema, to):
+				if err := s.alterEnum(modify.T, from, to); err != nil {
 					return err
 				}
 				// If only the enum values were changed,
@@ -283,9 +288,10 @@ func (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) err
 				if k == schema.ChangeType {
 					continue
 				}
-			// Enum was added (and column type was changed).
-			case !ok1 && ok2:
-				if err := s.addTypes(ctx, change.To); err != nil {
+			// Column type was changed to enum, or enum type was changed.
+			case !ok1 && ok2 ||
+				ok1 && ok2 && enumIdent(modify.T.Schema, from) != enumIdent(modify.T.Schema, to):
+				if err := s.addTypes(ctx, modify.T, change.To); err != nil {
 					return err
 				}
 			}
@@ -327,7 +333,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 			switch change := changes[i].(type) {
 			case *schema.AddColumn:
 				b.P("ADD COLUMN")
-				if err := s.column(b, change.C); err != nil {
+				if err := s.column(b, t, change.C); err != nil {
 					return err
 				}
 				reverse = append(reverse, &schema.DropColumn{C: change.C})
@@ -554,17 +560,22 @@ func alterType(b *sqlx.Builder, alter *alterChange, t *schema.Table, c *schema.M
 		}
 	// Serial type was changed. e.g. serial to bigserial.
 	case fromHas && toHas:
-		t, err := FormatType(toS.IntegerType())
+		f, err := FormatType(toS.IntegerType())
 		if err != nil {
 			return err
 		}
-		b.P("TYPE", t)
+		b.P("TYPE", f)
 	default:
-		t, err := FormatType(c.To.Type.Type)
-		if err != nil {
+		var (
+			f   string
+			err error
+		)
+		if e, ok := c.To.Type.Type.(*schema.EnumType); ok {
+			f = enumIdent(t.Schema, e)
+		} else if f, err = FormatType(c.To.Type.Type); err != nil {
 			return err
 		}
-		b.P("TYPE", t)
+		b.P("TYPE", f)
 	}
 	if collate := (schema.Collation{}); sqlx.Has(c.To.Attrs, &collate) {
 		b.P("COLLATE", collate.V)
@@ -639,7 +650,7 @@ func (s *state) dropIndexes(t *schema.Table, indexes ...*schema.Index) {
 	}
 }
 
-func (s *state) addTypes(ctx context.Context, columns ...*schema.Column) error {
+func (s *state) addTypes(ctx context.Context, t *schema.Table, columns ...*schema.Column) error {
 	for _, c := range columns {
 		e, ok := c.Type.Type.(*schema.EnumType)
 		if !ok {
@@ -648,28 +659,37 @@ func (s *state) addTypes(ctx context.Context, columns ...*schema.Column) error {
 		if e.T == "" {
 			return fmt.Errorf("missing enum name for column %q", c.Name)
 		}
-		c.Type.Raw = e.T
-		if exists, err := s.enumExists(ctx, e.T); err != nil {
+		if exists, err := s.enumExists(ctx, t.Schema, e); err != nil {
 			return err
 		} else if exists {
+			// Enum exists and was not created
+			// on this migration phase.
 			continue
 		}
-		b := Build("CREATE TYPE").Ident(e.T).P("AS ENUM")
+		name := enumIdent(t.Schema, e)
+		if prev, ok := s.created[name]; ok {
+			if !sqlx.ValuesEqual(prev.Values, e.Values) {
+				return fmt.Errorf("enum type %s has inconsistent desired state: %q != %q", name, prev.Values, e.Values)
+			}
+			continue
+		}
+		s.created[name] = e
+		b := Build("CREATE TYPE").P(name, "AS ENUM")
 		b.Wrap(func(b *sqlx.Builder) {
 			b.MapComma(e.Values, func(i int, b *sqlx.Builder) {
-				b.WriteString("'" + e.Values[i] + "'")
+				b.WriteString(quote(e.Values[i]))
 			})
 		})
 		s.append(&migrate.Change{
 			Cmd:     b.String(),
 			Comment: fmt.Sprintf("create enum type %q", e.T),
-			Reverse: Build("DROP TYPE").Ident(e.T).String(),
+			Reverse: Build("DROP TYPE").P(name).String(),
 		})
 	}
 	return nil
 }
 
-func (s *state) alterType(from, to *schema.EnumType) error {
+func (s *state) alterEnum(t *schema.Table, from, to *schema.EnumType) error {
 	if len(from.Values) > len(to.Values) {
 		return fmt.Errorf("dropping enum (%q) value is not supported", from.T)
 	}
@@ -678,19 +698,30 @@ func (s *state) alterType(from, to *schema.EnumType) error {
 			return fmt.Errorf("replacing or reordering enum (%q) value is not supported: %q != %q", to.T, to.Values, from.Values)
 		}
 	}
+	name := enumIdent(t.Schema, from)
+	if prev, ok := s.altered[name]; ok {
+		if !sqlx.ValuesEqual(prev.Values, to.Values) {
+			return fmt.Errorf("enum type %s has inconsistent desired state: %q != %q", name, prev.Values, to.Values)
+		}
+		return nil
+	}
+	s.altered[name] = to
 	for _, v := range to.Values[len(from.Values):] {
 		s.append(&migrate.Change{
-			Cmd:     Build("ALTER TYPE").Ident(from.T).P("ADD VALUE", quote(v)).String(),
+			Cmd:     Build("ALTER TYPE").P(name, "ADD VALUE", quote(v)).String(),
 			Comment: fmt.Sprintf("add value to enum type: %q", from.T),
 		})
 	}
 	return nil
 }
 
-func (s *state) enumExists(ctx context.Context, name string) (bool, error) {
-	rows, err := s.QueryContext(ctx, "SELECT * FROM pg_type WHERE typname = $1 AND typtype = 'e'", name)
+func (s *state) enumExists(ctx context.Context, ns *schema.Schema, e *schema.EnumType) (bool, error) {
+	if e.Schema != nil {
+		ns = e.Schema
+	}
+	rows, err := s.QueryContext(ctx, `SELECT * FROM pg_type t join pg_namespace n on t.typnamespace = n.oid WHERE t.typname = $1 AND n.nspname = $2 AND t.typtype = 'e'`, e.T, ns.Name)
 	if err != nil {
-		return false, fmt.Errorf("check index existence: %w", err)
+		return false, fmt.Errorf("check enum existence: %w", err)
 	}
 	defer rows.Close()
 	return rows.Next(), rows.Err()
@@ -730,12 +761,17 @@ func (s *state) addIndexes(t *schema.Table, indexes ...*schema.Index) {
 	}
 }
 
-func (s *state) column(b *sqlx.Builder, c *schema.Column) error {
-	t, err := FormatType(c.Type.Type)
-	if err != nil {
+func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) error {
+	var (
+		f   string
+		err error
+	)
+	if e, ok := c.Type.Type.(*schema.EnumType); ok {
+		f = enumIdent(t.Schema, e)
+	} else if f, err = FormatType(c.Type.Type); err != nil {
 		return err
 	}
-	b.Ident(c.Name).P(t)
+	b.Ident(c.Name).P(f)
 	if !c.Type.Null {
 		b.P("NOT")
 	} else if t, ok := c.Type.Type.(*SerialType); ok {
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index a094ad13..5305145b 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -21,8 +21,8 @@ import (
 type (
 	doc struct {
 		Tables  []*sqlspec.Table  `spec:"table"`
-		Schemas []*sqlspec.Schema `spec:"schema"`
 		Enums   []*Enum           `spec:"enum"`
+		Schemas []*sqlspec.Schema `spec:"schema"`
 	}
 	// Enum holds a specification for an enum, that can be referenced as a column type.
 	Enum struct {
@@ -49,24 +49,21 @@ func evalSpec(p *hclparse.Parser, v interface{}, input map[string]string) error
 			return fmt.Errorf("specutil: failed converting to *schema.Realm: %w", err)
 		}
 		if len(d.Enums) > 0 {
-			for _, sch := range v.Schemas {
-				if err := convertEnums(d.Tables, d.Enums, sch); err != nil {
-					return err
-				}
+			if err := convertEnums(d.Tables, d.Enums, v); err != nil {
+				return err
 			}
 		}
 	case *schema.Schema:
 		if len(d.Schemas) != 1 {
 			return fmt.Errorf("specutil: expecting document to contain a single schema, got %d", len(d.Schemas))
 		}
-		var r schema.Realm
-		if err := specutil.Scan(&r, d.Schemas, d.Tables, convertTable); err != nil {
+		r := &schema.Realm{}
+		if err := specutil.Scan(r, d.Schemas, d.Tables, convertTable); err != nil {
 			return err
 		}
-		if err := convertEnums(d.Tables, d.Enums, r.Schemas[0]); err != nil {
+		if err := convertEnums(d.Tables, d.Enums, r); err != nil {
 			return err
 		}
-		r.Schemas[0].Realm = nil
 		*v = *r.Schemas[0]
 	default:
 		return fmt.Errorf("specutil: failed unmarshaling spec. %T is not supported", v)
@@ -341,44 +338,61 @@ func convertColumnType(spec *sqlspec.Column) (schema.Type, error) {
 
 // convertEnums converts possibly referenced column types (like enums) to
 // an actual schema.Type and sets it on the correct schema.Column.
-func convertEnums(tbls []*sqlspec.Table, enums []*Enum, sch *schema.Schema) error {
-	for _, tbl := range tbls {
-		for _, col := range tbl.Columns {
-			if col.Type.IsRef {
-				e, err := resolveEnum(col.Type, enums)
-				if err != nil {
-					return err
-				}
-				t, ok := sch.Table(tbl.Name)
-				if !ok {
-					return fmt.Errorf("postgres: table %q not found in schema %q", tbl.Name, sch.Name)
-				}
-				c, ok := t.Column(col.Name)
-				if !ok {
-					return fmt.Errorf("postgrs: column %q not found in table %q", col.Name, t.Name)
-				}
-				c.Type.Type = &schema.EnumType{
-					T:      e.Name,
-					Values: e.Values,
-				}
+func convertEnums(tables []*sqlspec.Table, enums []*Enum, r *schema.Realm) error {
+	var (
+		used   = make(map[*Enum]struct{})
+		byName = make(map[string]*Enum)
+	)
+	for _, e := range enums {
+		byName[e.Name] = e
+	}
+	for _, t := range tables {
+		for _, c := range t.Columns {
+			if !c.Type.IsRef {
+				continue
+			}
+			name, err := enumName(c.Type)
+			if err != nil {
+				return err
+			}
+			e, ok := byName[name]
+			if !ok {
+				return fmt.Errorf("enum %q was not found", name)
+			}
+			used[e] = struct{}{}
+			schemaE, err := specutil.SchemaName(e.Schema)
+			if err != nil {
+				return fmt.Errorf("extract schema name from enum refrence: %w", err)
+			}
+			es, ok := r.Schema(schemaE)
+			if !ok {
+				return fmt.Errorf("schema %q not found in realm for table %q", schemaE, t.Name)
+			}
+			schemaT, err := specutil.SchemaName(t.Schema)
+			if err != nil {
+				return fmt.Errorf("extract schema name from table refrence: %w", err)
+			}
+			ts, ok := r.Schema(schemaT)
+			if !ok {
+				return fmt.Errorf("schema %q not found in realm for table %q", schemaT, t.Name)
 			}
+			tt, ok := ts.Table(t.Name)
+			if !ok {
+				return fmt.Errorf("table %q not found in schema %q", t.Name, ts.Name)
+			}
+			cc, ok := tt.Column(c.Name)
+			if !ok {
+				return fmt.Errorf("column %q not found in table %q", c.Name, t.Name)
+			}
+			cc.Type.Type = &schema.EnumType{T: e.Name, Schema: es, Values: e.Values}
 		}
 	}
-	return nil
-}
-
-// resolveEnum returns the first Enum that matches the name referenced by the given column type.
-func resolveEnum(ref *schemahcl.Type, enums []*Enum) (*Enum, error) {
-	n, err := enumName(ref)
-	if err != nil {
-		return nil, err
-	}
 	for _, e := range enums {
-		if e.Name == n {
-			return e, err
+		if _, ok := used[e]; !ok {
+			return fmt.Errorf("enum %q declared but not used", e.Name)
 		}
 	}
-	return nil, fmt.Errorf("postgres: enum %q not found", n)
+	return nil
 }
 
 // enumName extracts the name of the referenced Enum from the reference string.
diff --git a/sql/schema/dsl.go b/sql/schema/dsl.go
index 6456f806..e568fae4 100644
--- a/sql/schema/dsl.go
+++ b/sql/schema/dsl.go
@@ -268,6 +268,13 @@ func EnumValues(values ...string) EnumOption {
 	}
 }
 
+// EnumSchema configures the schema of the enum.
+func EnumSchema(s *Schema) EnumOption {
+	return func(e *EnumType) {
+		e.Schema = s
+	}
+}
+
 // NewEnumColumn creates a new EnumType column.
 func NewEnumColumn(name string, opts ...EnumOption) *Column {
 	t := &EnumType{}
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 18578304..feac1d67 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -189,6 +189,7 @@ type (
 	EnumType struct {
 		T      string   // Optional type.
 		Values []string // Enum values.
+		Schema *Schema  // Optional schema.
 	}
 
 	// BinaryType represents a type that stores a binary data.


Test patch:
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index 85d8ceae..62127ab3 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -832,12 +832,12 @@ create table atlas_types_sanity
 				{
 					Name:    "tCharacter",
 					Type:    &schema.ColumnType{Type: &schema.StringType{T: "character", Size: 10}, Raw: "character", Null: true},
-					Default: &schema.RawExpr{X: "'atlas'::bpchar"},
+					Default: &schema.Literal{V: "'atlas'"},
 				},
 				{
 					Name:    "tChar",
 					Type:    &schema.ColumnType{Type: &schema.StringType{T: "character", Size: 10}, Raw: "character", Null: true},
-					Default: &schema.RawExpr{X: "'atlas'::bpchar"},
+					Default: &schema.Literal{V: "'atlas'"},
 				},
 				{
 					Name:    "tCharVar",
diff --git a/internal/integration/script_test.go b/internal/integration/script_test.go
index 6d1ffef3..2e96d6da 100644
--- a/internal/integration/script_test.go
+++ b/internal/integration/script_test.go
@@ -570,7 +570,7 @@ func (t *liteTest) hclDiff(ts *testscript.TestScript, name string) ([]schema.Cha
 	current, err := drv.InspectSchema(context.Background(), desired.Name, nil)
 	ts.Check(err)
 	changes, err := drv.SchemaDiff(current, desired)
-	// Diff errors should returned back to the caller.
+	// Diff errors should return to the caller.
 	if err != nil {
 		return nil, err
 	}
diff --git a/internal/integration/testdata/postgres/column-enum.txt b/internal/integration/testdata/postgres/column-enum.txt
index 0763719f..613b7744 100644
--- a/internal/integration/testdata/postgres/column-enum.txt
+++ b/internal/integration/testdata/postgres/column-enum.txt
@@ -22,10 +22,10 @@ table "users" {
 }
 
 -- 1.sql --
-             Table "script_column_enum.users"
- Column |  Type  | Collation | Nullable |     Default
---------+--------+-----------+----------+------------------
- type   | status |           | not null | 'active'::status
+                          Table "script_column_enum.users"
+ Column |           Type            | Collation | Nullable |               Default
+--------+---------------------------+-----------+----------+-------------------------------------
+ type   | script_column_enum.status |           | not null | 'active'::script_column_enum.status
 
 
 -- 2.hcl --
@@ -45,7 +45,7 @@ table "users" {
 }
 
 -- 2.sql --
-              Table "script_column_enum.users"
- Column |  Type  | Collation | Nullable |      Default
---------+--------+-----------+----------+--------------------
- type   | status |           | not null | 'inactive'::status
+                                 Table "script_column_enum.users"
+ Column |           Type            | Collation | Nullable |                Default
+--------+---------------------------+-----------+----------+---------------------------------------
+ type   | script_column_enum.status |           | not null | 'inactive'::script_column_enum.status
diff --git a/sql/postgres/diff_test.go b/sql/postgres/diff_test.go
index 530e2ff7..d6ef78f8 100644
--- a/sql/postgres/diff_test.go
+++ b/sql/postgres/diff_test.go
@@ -249,6 +249,41 @@ func TestDiff_TableDiff(t *testing.T) {
 				},
 			}
 		}(),
+		// Modify enum type or values.
+		func() testcase {
+			var (
+				from = schema.NewTable("users").
+					SetSchema(schema.New("public")).
+					AddColumns(
+						schema.NewEnumColumn("state", schema.EnumName("state"), schema.EnumValues("on")),
+						schema.NewEnumColumn("enum1", schema.EnumName("enum1"), schema.EnumValues("a")),
+						schema.NewEnumColumn("enum3", schema.EnumName("enum3"), schema.EnumValues("a")),
+						schema.NewEnumColumn("enum4", schema.EnumName("enum4"), schema.EnumValues("a"), schema.EnumSchema(schema.New("public"))),
+					)
+				to = schema.NewTable("users").
+					SetSchema(schema.New("public")).
+					AddColumns(
+						// Add value.
+						schema.NewEnumColumn("state", schema.EnumName("state"), schema.EnumValues("on", "off")),
+						// Change type.
+						schema.NewEnumColumn("enum1", schema.EnumName("enum2"), schema.EnumValues("a")),
+						// No change as schema is optional.
+						schema.NewEnumColumn("enum3", schema.EnumName("enum3"), schema.EnumValues("a"), schema.EnumSchema(schema.New("public"))),
+						// Enum type was changed (reside in a different schema).
+						schema.NewEnumColumn("enum4", schema.EnumName("enum4"), schema.EnumValues("a"), schema.EnumSchema(schema.New("test"))),
+					)
+			)
+			return testcase{
+				name: "enums",
+				from: from,
+				to:   to,
+				wantChanges: []schema.Change{
+					&schema.ModifyColumn{From: from.Columns[0], To: to.Columns[0], Change: schema.ChangeType},
+					&schema.ModifyColumn{From: from.Columns[1], To: to.Columns[1], Change: schema.ChangeType},
+					&schema.ModifyColumn{From: from.Columns[3], To: to.Columns[3], Change: schema.ChangeType},
+				},
+			}
+		}(),
 		func() testcase {
 			var (
 				from = &schema.Table{
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 6c6184b8..13ac5db2 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -41,50 +41,52 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- table_name  |  column_name |          data_type          |  formatted  | is_nullable |         column_default                 | character_maximum_length | numeric_precision | datetime_precision | numeric_scale |    interval_type    | character_set_name | collation_name | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype |  oid
--------------+--------------+-----------------------------+-------------|-------------+----------------------------------------+--------------------------+-------------------+--------------------+---------------+---------------------+--------------------+----------------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-------
- users       |  id          | bigint                      | int8        | NO          |                                        |                          |                64 |                    |             0 |                     |                    |                | YES         |      100       |          1         |          1       |    BY DEFAULT       |                       |         | b       |    20
- users       |  rank        | integer                     | int4        | YES         |                                        |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       | rank    | b       |    23
- users       |  c1          | smallint                    | int2        | NO          |           1000                         |                          |                16 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    21
- users       |  c2          | bit                         | bit         | NO          |                                        |                        1 |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1560
- users       |  c3          | bit varying                 | varbit      | NO          |                                        |                       10 |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1562
- users       |  c4          | boolean                     | bool        | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    16
- users       |  c5          | bytea                       | bytea       | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    17
- users       |  c6          | character                   | bpchar      | NO          |                                        |                      100 |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1042
- users       |  c7          | character varying           | varchar     | NO          | 'logged_in'::character varying         |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1043
- users       |  c8          | cidr                        | cidr        | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   650
- users       |  c9          | circle                      | circle      | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   718
- users       |  c10         | date                        | date        | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1082
- users       |  c11         | time with time zone         | timetz      | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1266
- users       |  c12         | double precision            | float8      | NO          |                                        |                          |                53 |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   701
- users       |  c13         | real                        | float4      | NO          |           random()                     |                          |                24 |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   700
- users       |  c14         | json                        | json        | NO          |           '{}'::json                   |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   114
- users       |  c15         | jsonb                       | jsonb       | NO          |           '{}'::jsonb                  |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  3802
- users       |  c16         | money                       | money       | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   790
- users       |  c17         | numeric                     | numeric     | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1700
- users       |  c18         | numeric                     | numeric     | NO          |                                        |                          |                 4 |                    |             4 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1700
- users       |  c19         | integer                     | int4        | NO          | nextval('t1_c19_seq'::regclass)        |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    23
- users       |  c20         | uuid                        | uuid        | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  2950
- users       |  c21         | xml                         | xml         | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   142
- users       |  c22         | ARRAY                       | integer[]   | YES         |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1007
- users       |  c23         | USER-DEFINED                | ltree       | YES         |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       | 16535
- users       |  c24         | USER-DEFINED                | state       | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | e       | 16774
- users       |  c25         | timestamp without time zone | timestamp   | NO          |            now()                       |                          |                   |                  4 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1114
- users       |  c26         | timestamp with time zone    | timestamptz | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1184
- users       |  c27         | time without time zone      | time        | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1266
- users       |  c28         | int                         | int8        | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |        (c1 + c2)      |         | b       |  1267
- users       |  c29         | interval                    | interval    | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1268
- users       |  c30         | interval                    | interval    | NO          |                                        |                          |                   |                  6 |               |        MONTH        |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1269
- users       |  c31         | interval                    | interval    | NO          |                                        |                          |                   |                  6 |               | MINUTE TO SECOND(6) |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1233
- users       |  c32         | bigint                      | int4        | NO          | nextval('public.t1_c32_seq'::regclass) |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    23
+ table_name  |  column_name |          data_type          |  formatted          | is_nullable |         column_default                 | character_maximum_length | numeric_precision | datetime_precision | numeric_scale |    interval_type    | character_set_name | collation_name | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype |  oid
+-------------+--------------+-----------------------------+---------------------|-------------+----------------------------------------+--------------------------+-------------------+--------------------+---------------+---------------------+--------------------+----------------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-------
+ users       |  id          | bigint                      | int8                | NO          |                                        |                          |                64 |                    |             0 |                     |                    |                | YES         |      100       |          1         |          1       |    BY DEFAULT       |                       |         | b       |    20
+ users       |  rank        | integer                     | int4                | YES         |                                        |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       | rank    | b       |    23
+ users       |  c1          | smallint                    | int2                | NO          |           1000                         |                          |                16 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    21
+ users       |  c2          | bit                         | bit                 | NO          |                                        |                        1 |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1560
+ users       |  c3          | bit varying                 | varbit              | NO          |                                        |                       10 |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1562
+ users       |  c4          | boolean                     | bool                | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    16
+ users       |  c5          | bytea                       | bytea               | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    17
+ users       |  c6          | character                   | bpchar              | NO          |                                        |                      100 |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1042
+ users       |  c7          | character varying           | varchar             | NO          | 'logged_in'::character varying         |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1043
+ users       |  c8          | cidr                        | cidr                | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   650
+ users       |  c9          | circle                      | circle              | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   718
+ users       |  c10         | date                        | date                | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1082
+ users       |  c11         | time with time zone         | timetz              | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1266
+ users       |  c12         | double precision            | float8              | NO          |                                        |                          |                53 |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   701
+ users       |  c13         | real                        | float4              | NO          |           random()                     |                          |                24 |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   700
+ users       |  c14         | json                        | json                | NO          |           '{}'::json                   |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   114
+ users       |  c15         | jsonb                       | jsonb               | NO          |           '{}'::jsonb                  |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  3802
+ users       |  c16         | money                       | money               | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   790
+ users       |  c17         | numeric                     | numeric             | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1700
+ users       |  c18         | numeric                     | numeric             | NO          |                                        |                          |                 4 |                    |             4 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1700
+ users       |  c19         | integer                     | int4                | NO          | nextval('t1_c19_seq'::regclass)        |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    23
+ users       |  c20         | uuid                        | uuid                | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  2950
+ users       |  c21         | xml                         | xml                 | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |   142
+ users       |  c22         | ARRAY                       | integer[]           | YES         |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1007
+ users       |  c23         | USER-DEFINED                | ltree               | YES         |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       | 16535
+ users       |  c24         | USER-DEFINED                | state               | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | e       | 16774
+ users       |  c25         | timestamp without time zone | timestamp           | NO          |            now()                       |                          |                   |                  4 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1114
+ users       |  c26         | timestamp with time zone    | timestamptz         | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1184
+ users       |  c27         | time without time zone      | time                | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1266
+ users       |  c28         | int                         | int8                | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |        (c1 + c2)      |         | b       |  1267
+ users       |  c29         | interval                    | interval            | NO          |                                        |                          |                   |                  6 |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1268
+ users       |  c30         | interval                    | interval            | NO          |                                        |                          |                   |                  6 |               |        MONTH        |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1269
+ users       |  c31         | interval                    | interval            | NO          |                                        |                          |                   |                  6 |               | MINUTE TO SECOND(6) |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  1233
+ users       |  c32         | bigint                      | int4                | NO          | nextval('public.t1_c32_seq'::regclass) |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |    23
+ users       |  c33         | USER-DEFINED                | test."status""."    | NO          |  'unknown'::test."status""."           |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | e       | 16775
 `))
-				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1)`)).
-					WithArgs(16774).
+				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1, $2)`)).
+					WithArgs(16774, 16775).
 					WillReturnRows(sqltest.Rows(`
  enumtypid | enumlabel
 -----------+-----------
      16774 | on
      16774 | off
+     16775 | unknown
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -120,7 +122,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c21", Type: &schema.ColumnType{Raw: "xml", Type: &XMLType{T: "xml"}}},
 					{Name: "c22", Type: &schema.ColumnType{Raw: "ARRAY", Null: true, Type: &ArrayType{Type: &schema.IntegerType{T: "integer"}, T: "integer[]"}}},
 					{Name: "c23", Type: &schema.ColumnType{Raw: "USER-DEFINED", Null: true, Type: &UserDefinedType{T: "ltree"}}},
-					{Name: "c24", Type: &schema.ColumnType{Raw: "state", Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
+					{Name: "c24", Type: &schema.ColumnType{Raw: "state", Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}, Schema: t.Schema}}},
 					{Name: "c25", Type: &schema.ColumnType{Raw: "timestamp without time zone", Type: &schema.TimeType{T: "timestamp without time zone", Precision: p(4)}}, Default: &schema.RawExpr{X: "now()"}},
 					{Name: "c26", Type: &schema.ColumnType{Raw: "timestamp with time zone", Type: &schema.TimeType{T: "timestamp with time zone", Precision: p(6)}}},
 					{Name: "c27", Type: &schema.ColumnType{Raw: "time without time zone", Type: &schema.TimeType{T: "time without time zone", Precision: p(6)}}},
@@ -129,6 +131,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c30", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MONTH", Precision: p(6)}}},
 					{Name: "c31", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MINUTE TO SECOND", Precision: p(6)}}},
 					{Name: "c32", Type: &schema.ColumnType{Raw: "bigserial", Type: &SerialType{T: "bigserial", SequenceName: "t1_c32_seq"}}},
+					{Name: "c33", Type: &schema.ColumnType{Raw: `status".`, Type: &schema.EnumType{T: `status".`, Values: []string{"unknown"}, Schema: schema.New("test")}}, Default: &schema.Literal{V: "'unknown'"}},
 				}, t.Columns)
 			},
 		},
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 6839f645..d70ad388 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -521,18 +521,23 @@ func TestPlanChanges(t *testing.T) {
 		{
 			changes: []schema.Change{
 				func() schema.Change {
-					users := &schema.Table{
-						Name: "users",
-						Columns: []*schema.Column{
-							{Name: "state", Type: &schema.ColumnType{Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
-						},
-					}
+					users := schema.NewTable("users").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewEnumColumn("state", schema.EnumName("state"), schema.EnumValues("on", "off")),
+							schema.NewEnumColumn("status", schema.EnumName("status"), schema.EnumValues("a", "b"), schema.EnumSchema(schema.New("test"))),
+						)
 					return &schema.ModifyTable{
 						T: users,
 						Changes: []schema.Change{
 							&schema.ModifyColumn{
 								From:   &schema.Column{Name: "state", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}}},
-								To:     &schema.Column{Name: "state", Type: &schema.ColumnType{Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
+								To:     users.Columns[0],
+								Change: schema.ChangeType,
+							},
+							&schema.ModifyColumn{
+								From:   &schema.Column{Name: "status", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}}},
+								To:     users.Columns[1],
 								Change: schema.ChangeType,
 							},
 						},
@@ -540,32 +545,36 @@ func TestPlanChanges(t *testing.T) {
 				}(),
 			},
 			mock: func(m mock) {
-				m.ExpectQuery(sqltest.Escape("SELECT * FROM pg_type WHERE typname = $1 AND typtype = 'e'")).
-					WithArgs("state").WillReturnRows(sqlmock.NewRows([]string{"name"}))
+				m.ExpectQuery(sqltest.Escape("SELECT * FROM pg_type t join pg_namespace n on t.typnamespace = n.oid WHERE t.typname = $1 AND n.nspname = $2 AND t.typtype = 'e'")).
+					WithArgs("state", "public").
+					WillReturnRows(sqlmock.NewRows([]string{"name"}))
+				m.ExpectQuery(sqltest.Escape("SELECT * FROM pg_type t join pg_namespace n on t.typnamespace = n.oid WHERE t.typname = $1 AND n.nspname = $2 AND t.typtype = 'e'")).
+					WithArgs("status", "test").
+					WillReturnRows(sqlmock.NewRows([]string{"name"}))
 			},
 			wantPlan: &migrate.Plan{
 				Reversible:    true,
 				Transactional: true,
 				Changes: []*migrate.Change{
-					{Cmd: `CREATE TYPE "state" AS ENUM ('on', 'off')`, Reverse: `DROP TYPE "state"`},
-					{Cmd: `ALTER TABLE "users" ALTER COLUMN "state" TYPE state`, Reverse: `ALTER TABLE "users" ALTER COLUMN "state" TYPE text`},
+					{Cmd: `CREATE TYPE "public"."state" AS ENUM ('on', 'off')`, Reverse: `DROP TYPE "public"."state"`},
+					{Cmd: `CREATE TYPE "test"."status" AS ENUM ('a', 'b')`, Reverse: `DROP TYPE "test"."status"`},
+					{Cmd: `ALTER TABLE "public"."users" ALTER COLUMN "state" TYPE "public"."state", ALTER COLUMN "status" TYPE "test"."status"`, Reverse: `ALTER TABLE "public"."users" ALTER COLUMN "status" TYPE text, ALTER COLUMN "state" TYPE text`},
 				},
 			},
 		},
 		{
 			changes: []schema.Change{
 				func() schema.Change {
-					users := &schema.Table{
-						Name: "users",
-						Columns: []*schema.Column{
-							{Name: "state", Type: &schema.ColumnType{Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
-						},
-					}
+					users := schema.NewTable("users").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewEnumColumn("state", schema.EnumName("state"), schema.EnumValues("on", "off")),
+						)
 					return &schema.ModifyTable{
 						T: users,
 						Changes: []schema.Change{
 							&schema.ModifyColumn{
-								From:   &schema.Column{Name: "state", Type: &schema.ColumnType{Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
+								From:   users.Columns[0],
 								To:     &schema.Column{Name: "state", Type: &schema.ColumnType{Type: &schema.EnumType{T: "state", Values: []string{"on", "off", "unknown"}}}},
 								Change: schema.ChangeType,
 							},
@@ -577,7 +586,7 @@ func TestPlanChanges(t *testing.T) {
 				Reversible:    false,
 				Transactional: true,
 				Changes: []*migrate.Change{
-					{Cmd: `ALTER TYPE "state" ADD VALUE 'unknown'`},
+					{Cmd: `ALTER TYPE "public"."state" ADD VALUE 'unknown'`},
 				},
 			},
 		},
@@ -585,12 +594,11 @@ func TestPlanChanges(t *testing.T) {
 		{
 			changes: []schema.Change{
 				func() schema.Change {
-					users := &schema.Table{
-						Name: "users",
-						Columns: []*schema.Column{
-							{Name: "state", Type: &schema.ColumnType{Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
-						},
-					}
+					users := schema.NewTable("users").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewEnumColumn("state", schema.EnumName("state"), schema.EnumValues("on", "off")),
+						)
 					return &schema.ModifyTable{
 						T: users,
 						Changes: []schema.Change{
@@ -607,8 +615,8 @@ func TestPlanChanges(t *testing.T) {
 				Reversible:    false,
 				Transactional: true,
 				Changes: []*migrate.Change{
-					{Cmd: `ALTER TYPE "state" ADD VALUE 'unknown'`},
-					{Cmd: `COMMENT ON COLUMN "users" ."state" IS ''`, Reverse: `COMMENT ON COLUMN "users" ."state" IS 'foo'`},
+					{Cmd: `ALTER TYPE "public"."state" ADD VALUE 'unknown'`},
+					{Cmd: `COMMENT ON COLUMN "public"."users" ."state" IS ''`, Reverse: `COMMENT ON COLUMN "public"."users" ."state" IS 'foo'`},
 				},
 			},
 		},
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 6aaad975..406d7a31 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -93,6 +93,7 @@ table "accounts" {
 }
 
 enum "account_type" {
+	schema = schema.schema
 	values = ["private", "business"]
 }
 `
@@ -211,6 +212,7 @@ enum "account_type" {
 						Type: &schema.EnumType{
 							T:      "account_type",
 							Values: []string{"private", "business"},
+							Schema: exp,
 						},
 					},
 				},
@@ -255,6 +257,7 @@ enum "account_type" {
 			{SeqNo: 0, C: exp.Tables[1].Columns[0]},
 		},
 	}
+	exp.Realm = schema.NewRealm(exp)
 	require.EqualValues(t, exp, &s)
 }
 
@@ -337,6 +340,7 @@ table "logs" {
 		c := schema.NewStringColumn("name", "text")
 		expected := schema.New("test").
 			AddTables(schema.NewTable("logs").AddColumns(c).AddAttrs(&Partition{T: PartitionTypeHash, Parts: []*PartitionPart{{C: c}}}))
+		expected.SetRealm(schema.NewRealm(expected))
 		require.Equal(t, expected, s)
 	})
 
@@ -367,6 +371,7 @@ table "logs" {
 		c := schema.NewStringColumn("name", "text")
 		expected := schema.New("test").
 			AddTables(schema.NewTable("logs").AddColumns(c).AddAttrs(&Partition{T: PartitionTypeRange, Parts: []*PartitionPart{{C: c}, {X: &schema.RawExpr{X: "lower(name)"}}}}))
+		expected.SetRealm(schema.NewRealm(expected))
 		require.Equal(t, expected, s)
 	})
 
@@ -664,7 +669,7 @@ table "users" {
 	)
 	err := EvalHCLBytes([]byte(f), &s, nil)
 	require.NoError(t, err)
-	exp := schema.New("test").
+	expected := schema.New("test").
 		AddTables(
 			schema.NewTable("users").
 				AddColumns(
@@ -673,7 +678,8 @@ table "users" {
 					schema.NewIntColumn("c3", "int").SetGeneratedExpr(&schema.GeneratedExpr{Expr: "3", Type: "STORED"}),
 				),
 		)
-	require.EqualValues(t, exp, &s)
+	expected.SetRealm(schema.NewRealm(expected))
+	require.EqualValues(t, expected, &s)
 }
 
 func TestMarshalSpec_Enum(t *testing.T) {
@@ -710,12 +716,12 @@ table "table2" {
     type = enum.account_type
   }
 }
-schema "test" {
-}
 enum "account_type" {
   schema = schema.test
   values = ["private", "business"]
 }
+schema "test" {
+}
 `
 	require.EqualValues(t, expected, string(buf))
 }