Repository: ariga/atlas
Pull request #954 (2022-07-17 10:59:47)

sql/postgres: support modifying serial columns


Patch:
diff --git a/cmd/atlas/internal/cmdapi/project.go b/cmd/atlas/internal/cmdapi/project.go
index 4d4679a7..6cab4102 100644
--- a/cmd/atlas/internal/cmdapi/project.go
+++ b/cmd/atlas/internal/cmdapi/project.go
@@ -5,6 +5,7 @@
 package cmdapi
 
 import (
+	"errors"
 	"fmt"
 	"os"
 
@@ -90,7 +91,7 @@ func (e *Env) Sources() ([]string, error) {
 	if s, err := attr.Strings(); err == nil {
 		return s, nil
 	}
-	return nil, fmt.Errorf("expected src to be either a string or a string array")
+	return nil, errors.New("expected src to be either a string or a string array")
 }
 
 var hclState = schemahcl.New(
diff --git a/schemahcl/opts.go b/schemahcl/opts.go
index 5cda1724..956261df 100644
--- a/schemahcl/opts.go
+++ b/schemahcl/opts.go
@@ -141,7 +141,7 @@ func typeFuncSpec(typeSpec *TypeSpec) function.Function {
 }
 
 // typeFuncSpecImpl returns the function implementation for the HCL function spec.
-func typeFuncSpecImpl(spec *function.Spec, typeSpec *TypeSpec) function.ImplFunc {
+func typeFuncSpecImpl(_ *function.Spec, typeSpec *TypeSpec) function.ImplFunc {
 	return func(args []cty.Value, retType cty.Type) (cty.Value, error) {
 		t := &Type{
 			T: typeSpec.T,
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index b0145dd1..0dc726d4 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -298,7 +298,7 @@ func columnType(c *columnDesc) (schema.Type, error) {
 		typ = &UserDefinedType{T: c.fmtype}
 		// The `typtype` column is set to 'e' for enum types, and the
 		// values are filled in batch after the rows above is closed.
-		// https://www.postgresql.org/docs/current/catalog-pg-type.html
+		// https://postgresql.org/docs/current/catalog-pg-type.html
 		if c.typtype == "e" {
 			typ = &enumType{T: c.fmtype, ID: c.typid}
 		}
@@ -615,31 +615,22 @@ func nArgs(start, n int) string {
 	return b.String()
 }
 
-var nextval = regexp.MustCompile(`(?i) *nextval\('(?:[\w$]+\.)*[\w$]+_[\w$]+_seq'(?:::regclass)*\) *$`)
+var nextval = regexp.MustCompile(`(?i) *nextval\('(?:[\w$]+\.)*([\w$]+_[\w$]+_seq)'(?:::regclass)*\) *$`)
 
 func defaultExpr(c *schema.Column, s string) {
-	switch {
+	switch m := nextval.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.
-	case nextval.MatchString(s):
-		var (
-			st     string
-			tt, ok = c.Type.Type.(*schema.IntegerType)
-		)
+	case len(m) == 2:
+		tt, ok := c.Type.Type.(*schema.IntegerType)
 		if !ok {
 			return
 		}
-		switch tt.T {
-		case TypeSmallInt:
-			st = TypeSmallSerial
-		case TypeInteger:
-			st = TypeSerial
-		case TypeBigInt:
-			st = TypeBigSerial
-		}
-		c.Type.Raw = st
-		c.Type.Type = &SerialType{T: st}
+		st := &SerialType{SequenceName: m[1]}
+		st.SetType(tt)
+		c.Type.Raw = st.T
+		c.Type.Type = st
 	case sqlx.IsLiteralBool(s), sqlx.IsLiteralNumber(s), sqlx.IsQuoted(s, '\''):
 		c.Default = &schema.Literal{V: s}
 	default:
@@ -701,14 +692,14 @@ type (
 	}
 
 	// ArrayType defines an array type.
-	// https://www.postgresql.org/docs/current/arrays.html
+	// https://postgresql.org/docs/current/arrays.html
 	ArrayType struct {
 		schema.Type        // Underlying items type (e.g. varchar(255)).
 		T           string // Formatted type (e.g. int[]).
 	}
 
 	// BitType defines a bit type.
-	// https://www.postgresql.org/docs/current/datatype-bit.html
+	// https://postgresql.org/docs/current/datatype-bit.html
 	BitType struct {
 		schema.Type
 		T   string
@@ -716,7 +707,7 @@ type (
 	}
 
 	// IntervalType defines an interval type.
-	// https://www.postgresql.org/docs/current/datatype-datetime.html
+	// https://postgresql.org/docs/current/datatype-datetime.html
 	IntervalType struct {
 		schema.Type
 		T         string // Type name.
@@ -725,7 +716,7 @@ type (
 	}
 
 	// A NetworkType defines a network type.
-	// https://www.postgresql.org/docs/current/datatype-net-types.html
+	// https://postgresql.org/docs/current/datatype-net-types.html
 	NetworkType struct {
 		schema.Type
 		T   string
@@ -739,10 +730,15 @@ type (
 	}
 
 	// A SerialType defines a serial type.
+	// https://postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
 	SerialType struct {
 		schema.Type
 		T         string
 		Precision int
+		// SequenceName holds the inspected sequence name attached to the column.
+		// It defaults to <Table>_<Column>_seq when the column is created, but may
+		// be different in case the table or the column was renamed.
+		SequenceName string
 	}
 
 	// A UUIDType defines a UUID type.
@@ -758,18 +754,18 @@ type (
 	}
 
 	// ConType describes constraint type.
-	// https://www.postgresql.org/docs/current/catalog-pg-constraint.html
+	// https://postgresql.org/docs/current/catalog-pg-constraint.html
 	ConType struct {
 		schema.Attr
 		T string // c, f, p, u, t, x.
 	}
 
 	// Sequence defines (the supported) sequence options.
-	// https://www.postgresql.org/docs/current/sql-createsequence.html
+	// https://postgresql.org/docs/current/sql-createsequence.html
 	Sequence struct {
 		Start, Increment int64
 		// Last sequence value written to disk.
-		// https://www.postgresql.org/docs/current/view-pg-sequences.html.
+		// https://postgresql.org/docs/current/view-pg-sequences.html.
 		Last int64
 	}
 
@@ -781,21 +777,21 @@ type (
 	}
 
 	// IndexType represents an index type.
-	// https://www.postgresql.org/docs/current/indexes-types.html
+	// https://postgresql.org/docs/current/indexes-types.html
 	IndexType struct {
 		schema.Attr
 		T string // BTREE, BRIN, HASH, GiST, SP-GiST, GIN.
 	}
 
 	// IndexPredicate describes a partial index predicate.
-	// https://www.postgresql.org/docs/current/catalog-pg-index.html
+	// https://postgresql.org/docs/current/catalog-pg-index.html
 	IndexPredicate struct {
 		schema.Attr
 		P string
 	}
 
 	// IndexColumnProperty describes an index column property.
-	// https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS
+	// https://postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS
 	IndexColumnProperty struct {
 		schema.Attr
 		// NullsFirst defaults to true for DESC indexes.
@@ -805,7 +801,7 @@ type (
 	}
 
 	// IndexStorageParams describes index storage parameters add with the WITH clause.
-	// https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
+	// https://postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
 	IndexStorageParams struct {
 		schema.Attr
 		// AutoSummarize defines the authsummarize storage parameter.
@@ -816,7 +812,7 @@ type (
 	}
 
 	// NoInherit attribute defines the NO INHERIT flag for CHECK constraint.
-	// https://www.postgresql.org/docs/current/catalog-pg-constraint.html
+	// https://postgresql.org/docs/current/catalog-pg-constraint.html
 	NoInherit struct {
 		schema.Attr
 	}
@@ -855,6 +851,39 @@ type (
 // IsUnique reports if the type is unique constraint.
 func (c ConType) IsUnique() bool { return strings.ToLower(c.T) == "u" }
 
+// IntegerType returns the underlying integer type this serial type represents.
+func (s *SerialType) IntegerType() *schema.IntegerType {
+	t := &schema.IntegerType{T: TypeInteger}
+	switch s.T {
+	case TypeSerial2, TypeSmallSerial:
+		t.T = TypeSmallInt
+	case TypeSerial8, TypeBigSerial:
+		t.T = TypeBigInt
+	}
+	return t
+}
+
+// SetType sets the serial type from the given integer type.
+func (s *SerialType) SetType(t *schema.IntegerType) {
+	switch t.T {
+	case TypeSmallInt, TypeInt2:
+		s.T = TypeSmallSerial
+	case TypeInteger, TypeInt4, TypeInt:
+		s.T = TypeSerial
+	case TypeBigInt, TypeInt8:
+		s.T = TypeBigSerial
+	}
+}
+
+// sequence returns the inspected name of the sequence
+// or the standard name defined by postgres.
+func (s *SerialType) sequence(t *schema.Table, c *schema.Column) string {
+	if s.SequenceName != "" {
+		return s.SequenceName
+	}
+	return fmt.Sprintf("%s_%s_seq", t.Name, c.Name)
+}
+
 // newIndexStorage parses and returns the index storage parameters.
 func newIndexStorage(opts string) (*IndexStorageParams, error) {
 	params := &IndexStorageParams{}
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index f059423d..8eaaabdc 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -321,7 +321,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 		reverse    []schema.Change
 		reversible = true
 	)
-	build := func(changes []schema.Change) (string, error) {
+	build := func(alter *alterChange, changes []schema.Change) (string, error) {
 		b := Build("ALTER TABLE").Table(t)
 		err := b.MapCommaErr(changes, func(i int, b *sqlx.Builder) error {
 			switch change := changes[i].(type) {
@@ -332,7 +332,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 				}
 				reverse = append(reverse, &schema.DropColumn{C: change.C})
 			case *schema.ModifyColumn:
-				if err := s.alterColumn(b, change); err != nil {
+				if err := s.alterColumn(b, alter, t, change); err != nil {
 					return err
 				}
 				if change.Change.Is(schema.ChangeGenerated) {
@@ -397,12 +397,13 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 		}
 		return b.String(), nil
 	}
-	cmd, err := build(changes)
+	cmd := &alterChange{}
+	stmt, err := build(cmd, changes)
 	if err != nil {
 		return fmt.Errorf("alter table %q: %v", t.Name, err)
 	}
-	change := &migrate.Change{
-		Cmd: cmd,
+	cmd.main = &migrate.Change{
+		Cmd: stmt,
 		Source: &schema.ModifyTable{
 			T:       t,
 			Changes: changes,
@@ -413,11 +414,161 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 		// Changes should be reverted in
 		// a reversed order they were created.
 		sqlx.ReverseChanges(reverse)
-		if change.Reverse, err = build(reverse); err != nil {
+		if cmd.main.Reverse, err = build(&alterChange{}, reverse); err != nil {
 			return fmt.Errorf("reverse alter table %q: %v", t.Name, err)
 		}
 	}
-	s.append(change)
+	cmd.append(s)
+	return nil
+}
+
+// alterChange describes an alter table migrate.Change where its main command
+// can be supported by additional statements before and after it is executed.
+type alterChange struct {
+	main          *migrate.Change
+	before, after []*migrate.Change
+}
+
+func (a *alterChange) append(s *state) {
+	s.append(a.before...)
+	s.append(a.main)
+	s.append(a.after...)
+}
+
+func (s *state) alterColumn(b *sqlx.Builder, alter *alterChange, t *schema.Table, c *schema.ModifyColumn) error {
+	for k := c.Change; !k.Is(schema.NoChange); {
+		b.P("ALTER COLUMN").Ident(c.To.Name)
+		switch {
+		case k.Is(schema.ChangeType):
+			if err := alterType(b, alter, t, c); err != nil {
+				return err
+			}
+			k &= ^schema.ChangeType
+		case k.Is(schema.ChangeNull) && c.To.Type.Null:
+			if t, ok := c.To.Type.Type.(*SerialType); ok {
+				return fmt.Errorf("NOT NULL constraint is required for %s column %q", t.T, c.To.Name)
+			}
+			b.P("DROP NOT NULL")
+			k &= ^schema.ChangeNull
+		case k.Is(schema.ChangeNull) && !c.To.Type.Null:
+			b.P("SET NOT NULL")
+			k &= ^schema.ChangeNull
+		case k.Is(schema.ChangeDefault) && c.To.Default == nil:
+			b.P("DROP DEFAULT")
+			k &= ^schema.ChangeDefault
+		case k.Is(schema.ChangeDefault) && c.To.Default != nil:
+			s.columnDefault(b.P("SET"), c.To)
+			k &= ^schema.ChangeDefault
+		case k.Is(schema.ChangeAttr):
+			toI, ok := identity(c.To.Attrs)
+			if !ok {
+				return fmt.Errorf("unexpected attribute change (expect IDENTITY): %v", c.To.Attrs)
+			}
+			// The syntax for altering identity columns is identical to sequence_options.
+			// https://www.postgresql.org/docs/current/sql-altersequence.html
+			b.P("SET GENERATED", toI.Generation, "SET START WITH", strconv.FormatInt(toI.Sequence.Start, 10), "SET INCREMENT BY", strconv.FormatInt(toI.Sequence.Increment, 10))
+			// Skip SEQUENCE RESTART in case the "start value" is less than the "current value" in one
+			// of the states (inspected and desired), because this function is used for both UP and DOWN.
+			if fromI, ok := identity(c.From.Attrs); (!ok || fromI.Sequence.Last < toI.Sequence.Start) && toI.Sequence.Last < toI.Sequence.Start {
+				b.P("RESTART")
+			}
+			k &= ^schema.ChangeAttr
+		case k.Is(schema.ChangeGenerated):
+			if sqlx.Has(c.To.Attrs, &schema.GeneratedExpr{}) {
+				return fmt.Errorf("unexpected generation expression change (expect DROP EXPRESSION): %v", c.To.Attrs)
+			}
+			b.P("DROP EXPRESSION")
+			k &= ^schema.ChangeGenerated
+		default: // e.g. schema.ChangeComment.
+			return fmt.Errorf("unexpected column change: %d", k)
+		}
+		if !k.Is(schema.NoChange) {
+			b.Comma()
+		}
+	}
+	return nil
+}
+
+// alterType appends the clause(s) to alter the column type and assuming the
+// "ALTER COLUMN <Name>" was called before by the alterColumn function.
+func alterType(b *sqlx.Builder, alter *alterChange, t *schema.Table, c *schema.ModifyColumn) error {
+	// Commands for creating and dropping serial sequences.
+	createDropSeq := func(s *SerialType) (string, string, string) {
+		seq := fmt.Sprintf(`%q.%q`, t.Schema.Name, s.sequence(t, c.To))
+		drop := Build("DROP SEQUENCE IF EXISTS").P(seq).String()
+		create := Build("CREATE SEQUENCE IF NOT EXISTS").P(seq, "OWNED BY").
+			P(fmt.Sprintf(`%q.%q.%q`, t.Schema.Name, t.Name, c.To.Name)).
+			String()
+		return create, drop, seq
+	}
+	toS, toHas := c.To.Type.Type.(*SerialType)
+	fromS, fromHas := c.From.Type.Type.(*SerialType)
+	switch {
+	// Sequence was dropped.
+	case fromHas && !toHas:
+		b.P("DROP DEFAULT")
+		create, drop, _ := createDropSeq(fromS)
+		// Sequence should be deleted after it was dropped
+		// from the DEFAULT value.
+		alter.after = append(alter.after, &migrate.Change{
+			Source:  c,
+			Comment: fmt.Sprintf("drop sequence used by serial column %q", c.From.Name),
+			Cmd:     drop,
+			Reverse: create,
+		})
+		toT, err := FormatType(c.To.Type.Type)
+		if err != nil {
+			return err
+		}
+		fromT, err := FormatType(fromS.IntegerType())
+		if err != nil {
+			return err
+		}
+		// Underlying type was changed. e.g. serial to bigint.
+		if toT != fromT {
+			b.Comma().P("ALTER COLUMN").Ident(c.To.Name).P("TYPE", toT)
+		}
+	// Sequence was added.
+	case !fromHas && toHas:
+		create, drop, seq := createDropSeq(toS)
+		// Sequence should be created before it is used by the
+		// column DEFAULT value.
+		alter.before = append(alter.before, &migrate.Change{
+			Source:  c,
+			Comment: fmt.Sprintf("create sequence for serial column %q", c.To.Name),
+			Cmd:     create,
+			Reverse: drop,
+		})
+		b.P("SET DEFAULT", fmt.Sprintf("nextval('%s')", seq))
+		toT, err := FormatType(toS.IntegerType())
+		if err != nil {
+			return err
+		}
+		fromT, err := FormatType(c.From.Type.Type)
+		if err != nil {
+			return err
+		}
+		// Underlying type was changed. e.g. integer to bigserial (bigint).
+		if toT != fromT {
+			b.Comma().P("ALTER COLUMN").Ident(c.To.Name).P("TYPE", toT)
+		}
+	// Serial type was changed. e.g. serial to bigserial.
+	case fromHas && toHas:
+		t, err := FormatType(toS.IntegerType())
+		if err != nil {
+			return err
+		}
+		b.P("TYPE", t)
+	default:
+		t, err := FormatType(c.To.Type.Type)
+		if err != nil {
+			return err
+		}
+		b.P("TYPE", t)
+	}
+	if collate := (schema.Collation{}); sqlx.Has(c.To.Attrs, &collate) {
+		b.P("COLLATE", collate.V)
+	}
 	return nil
 }
 
@@ -646,65 +797,6 @@ func (s *state) columnDefault(b *sqlx.Builder, c *schema.Column) {
 	}
 }
 
-func (s *state) alterColumn(b *sqlx.Builder, c *schema.ModifyColumn) error {
-	for k := c.Change; !k.Is(schema.NoChange); {
-		b.P("ALTER COLUMN").Ident(c.To.Name)
-		switch {
-		case k.Is(schema.ChangeType):
-			t, err := FormatType(c.To.Type.Type)
-			if err != nil {
-				return err
-			}
-			b.P("TYPE", t)
-			if collate := (schema.Collation{}); sqlx.Has(c.To.Attrs, &collate) {
-				b.P("COLLATE", collate.V)
-			}
-			k &= ^schema.ChangeType
-		case k.Is(schema.ChangeNull) && c.To.Type.Null:
-			if t, ok := c.To.Type.Type.(*SerialType); ok {
-				return fmt.Errorf("NOT NULL constraint is required for %s column %q", t.T, c.To.Name)
-			}
-			b.P("DROP NOT NULL")
-			k &= ^schema.ChangeNull
-		case k.Is(schema.ChangeNull) && !c.To.Type.Null:
-			b.P("SET NOT NULL")
-			k &= ^schema.ChangeNull
-		case k.Is(schema.ChangeDefault) && c.To.Default == nil:
-			b.P("DROP DEFAULT")
-			k &= ^schema.ChangeDefault
-		case k.Is(schema.ChangeDefault) && c.To.Default != nil:
-			s.columnDefault(b.P("SET"), c.To)
-			k &= ^schema.ChangeDefault
-		case k.Is(schema.ChangeAttr):
-			toI, ok := identity(c.To.Attrs)
-			if !ok {
-				return fmt.Errorf("unexpected attribute change (expect IDENTITY): %v", c.To.Attrs)
-			}
-			// The syntax for altering identity columns is identical to sequence_options.
-			// https://www.postgresql.org/docs/current/sql-altersequence.html
-			b.P("SET GENERATED", toI.Generation, "SET START WITH", strconv.FormatInt(toI.Sequence.Start, 10), "SET INCREMENT BY", strconv.FormatInt(toI.Sequence.Increment, 10))
-			// Skip SEQUENCE RESTART in case the "start value" is less than the "current value" in one
-			// of the states (inspected and desired), because this function is used for both UP and DOWN.
-			if fromI, ok := identity(c.From.Attrs); (!ok || fromI.Sequence.Last < toI.Sequence.Start) && toI.Sequence.Last < toI.Sequence.Start {
-				b.P("RESTART")
-			}
-			k &= ^schema.ChangeAttr
-		case k.Is(schema.ChangeGenerated):
-			if sqlx.Has(c.To.Attrs, &schema.GeneratedExpr{}) {
-				return fmt.Errorf("unexpected generation expression change (expect DROP EXPRESSION): %v", c.To.Attrs)
-			}
-			b.P("DROP EXPRESSION")
-			k &= ^schema.ChangeGenerated
-		default: // e.g. schema.ChangeComment.
-			return fmt.Errorf("unexpected column change: %d", k)
-		}
-		if !k.Is(schema.NoChange) {
-			b.Comma()
-		}
-	}
-	return nil
-}
-
 func (s *state) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
 	b.Wrap(func(b *sqlx.Builder) {
 		b.MapComma(parts, func(i int, b *sqlx.Builder) {
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 5656388a..19646f00 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -87,9 +87,9 @@ type (
 
 // Schema returns the first schema that matched the given name.
 func (r *Realm) Schema(name string) (*Schema, bool) {
-	for _, t := range r.Schemas {
-		if t.Name == name {
-			return t, true
+	for _, s := range r.Schemas {
+		if s.Name == name {
+			return s, true
 		}
 	}
 	return nil, false
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index cac49273..df377ac8 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -134,7 +134,7 @@ func (*Diff) ReferenceChanged(from, to schema.ReferenceOption) bool {
 func (d *Diff) Normalize(from, to *schema.Table) error {
 	used := make([]bool, len(to.ForeignKeys))
 	// In SQLite, there is no easy way to get the foreign-key constraint
-	// name, except for parsing the CREATE statement). Therefore, we check
+	// name, except for parsing the CREATE statement. Therefore, we check
 	// if there is a foreign-key with identical properties.
 	for _, fk1 := range from.ForeignKeys {
 		for i, fk2 := range to.ForeignKeys {


Test patch:
diff --git a/internal/integration/testdata/postgres/column-serial.txt b/internal/integration/testdata/postgres/column-serial.txt
new file mode 100644
index 00000000..65f84476
--- /dev/null
+++ b/internal/integration/testdata/postgres/column-serial.txt
@@ -0,0 +1,82 @@
+apply 1.hcl
+cmpshow t 1.sql
+
+apply 2.hcl
+cmpshow t 2.sql
+
+apply 3.hcl
+cmpshow t 3.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "t" {
+  schema = schema.$db
+  column "x" {
+    type = smallserial
+  }
+  column "y" {
+    type = serial
+  }
+  column "z" {
+    type = bigserial
+  }
+}
+
+-- 1.sql --
+                                Table "script_column_serial.t"
+ Column |   Type   | Collation | Nullable |                      Default
+--------+----------+-----------+----------+---------------------------------------------------
+ x      | smallint |           | not null | nextval('script_column_serial.t_x_seq'::regclass)
+ y      | integer  |           | not null | nextval('script_column_serial.t_y_seq'::regclass)
+ z      | bigint   |           | not null | nextval('script_column_serial.t_z_seq'::regclass)
+
+-- 2.hcl --
+ schema "$db" {}
+
+ table "t" {
+   schema = schema.$db
+   column "x" {
+     # Drop sequence.
+     type = smallint
+   }
+   column "y" {
+     # Drop sequence and change type.
+     type = bigint
+   }
+   column "z" {
+     # Change sequence type.
+     type = serial
+   }
+ }
+
+-- 2.sql --
+                                Table "script_column_serial.t"
+ Column |   Type   | Collation | Nullable |                      Default
+--------+----------+-----------+----------+---------------------------------------------------
+ x      | smallint |           | not null |
+ y      | bigint   |           | not null |
+ z      | integer  |           | not null | nextval('script_column_serial.t_z_seq'::regclass)
+
+
+-- 3.hcl --
+ schema "$db" {}
+
+ table "t" {
+   schema = schema.$db
+   column "x" {
+     # Add sequence.
+     type = smallserial
+   }
+   column "y" {
+     # Add sequence and change type.
+     type = serial
+   }
+ }
+
+-- 3.sql --
+                                Table "script_column_serial.t"
+ Column |   Type   | Collation | Nullable |                      Default
+--------+----------+-----------+----------+---------------------------------------------------
+ x      | smallint |           | not null | nextval('script_column_serial.t_x_seq'::regclass)
+ y      | integer  |           | not null | nextval('script_column_serial.t_y_seq'::regclass)
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 24a08c90..6c6184b8 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -115,7 +115,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c16", Type: &schema.ColumnType{Raw: "money", Type: &CurrencyType{T: "money"}}},
 					{Name: "c17", Type: &schema.ColumnType{Raw: "numeric", Type: &schema.DecimalType{T: "numeric"}}},
 					{Name: "c18", Type: &schema.ColumnType{Raw: "numeric", Type: &schema.DecimalType{T: "numeric", Precision: 4, Scale: 4}}},
-					{Name: "c19", Type: &schema.ColumnType{Raw: "serial", Type: &SerialType{T: "serial"}}},
+					{Name: "c19", Type: &schema.ColumnType{Raw: "serial", Type: &SerialType{T: "serial", SequenceName: "t1_c19_seq"}}},
 					{Name: "c20", Type: &schema.ColumnType{Raw: "uuid", Type: &UUIDType{T: "uuid"}}},
 					{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[]"}}},
@@ -128,7 +128,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c29", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", Precision: p(6)}}},
 					{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"}}},
+					{Name: "c32", Type: &schema.ColumnType{Raw: "bigserial", Type: &SerialType{T: "bigserial", SequenceName: "t1_c32_seq"}}},
 				}, t.Columns)
 			},
 		},
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 82479d75..6839f645 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -179,19 +179,6 @@ func TestPlanChanges(t *testing.T) {
 				Changes:       []*migrate.Change{{Cmd: `DROP SCHEMA "atlas" CASCADE`}},
 			},
 		},
-		{
-			changes: []schema.Change{
-				&schema.AddTable{
-					T: &schema.Table{
-						Name: "posts",
-						Columns: []*schema.Column{
-							{Name: "id", Type: &schema.ColumnType{Type: &SerialType{T: "serial"}, Null: true}},
-						},
-					},
-				},
-			},
-			wantErr: true,
-		},
 		{
 			changes: []schema.Change{
 				&schema.AddTable{
@@ -859,6 +846,141 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		// Invalid serial type.
+		{
+			changes: []schema.Change{
+				&schema.AddTable{
+					T: &schema.Table{
+						Name: "posts",
+						Columns: []*schema.Column{
+							{Name: "id", Type: &schema.ColumnType{Type: &SerialType{T: "serial"}, Null: true}},
+						},
+					},
+				},
+			},
+			wantErr: true,
+		},
+		// Drop serial sequence.
+		{
+			changes: []schema.Change{
+				&schema.ModifyTable{
+					T: schema.NewTable("posts").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewIntColumn("c1", "integer"),
+							schema.NewIntColumn("c2", "integer"),
+						),
+					Changes: schema.Changes{
+						&schema.ModifyColumn{
+							From:   schema.NewColumn("c1").SetType(&SerialType{T: "smallserial"}),
+							To:     schema.NewIntColumn("c1", "integer"),
+							Change: schema.ChangeType,
+						},
+						&schema.ModifyColumn{
+							From:   schema.NewColumn("c2").SetType(&SerialType{T: "serial", SequenceName: "previous_name"}),
+							To:     schema.NewIntColumn("c2", "integer"),
+							Change: schema.ChangeType,
+						},
+					},
+				},
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "public"."posts" ALTER COLUMN "c1" DROP DEFAULT, ALTER COLUMN "c1" TYPE integer, ALTER COLUMN "c2" DROP DEFAULT`,
+						Reverse: `ALTER TABLE "public"."posts" ALTER COLUMN "c2" SET DEFAULT nextval('"public"."previous_name"'), ALTER COLUMN "c1" SET DEFAULT nextval('"public"."posts_c1_seq"'), ALTER COLUMN "c1" TYPE smallint`,
+					},
+					{
+						Cmd:     `DROP SEQUENCE IF EXISTS "public"."posts_c1_seq"`,
+						Reverse: `CREATE SEQUENCE IF NOT EXISTS "public"."posts_c1_seq" OWNED BY "public"."posts"."c1"`,
+					},
+					{
+						Cmd:     `DROP SEQUENCE IF EXISTS "public"."previous_name"`,
+						Reverse: `CREATE SEQUENCE IF NOT EXISTS "public"."previous_name" OWNED BY "public"."posts"."c2"`,
+					},
+				},
+			},
+		},
+		// Add serial sequence.
+		{
+			changes: []schema.Change{
+				&schema.ModifyTable{
+					T: schema.NewTable("posts").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewColumn("c1").SetType(&SerialType{T: "serial"}),
+							schema.NewColumn("c2").SetType(&SerialType{T: "bigserial"}),
+						),
+					Changes: schema.Changes{
+						&schema.ModifyColumn{
+							From:   schema.NewIntColumn("c1", "integer"),
+							To:     schema.NewColumn("c1").SetType(&SerialType{T: "serial"}),
+							Change: schema.ChangeType,
+						},
+						&schema.ModifyColumn{
+							From:   schema.NewIntColumn("c2", "integer"),
+							To:     schema.NewColumn("c2").SetType(&SerialType{T: "bigserial"}),
+							Change: schema.ChangeType,
+						},
+					},
+				},
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `CREATE SEQUENCE IF NOT EXISTS "public"."posts_c1_seq" OWNED BY "public"."posts"."c1"`,
+						Reverse: `DROP SEQUENCE IF EXISTS "public"."posts_c1_seq"`,
+					},
+					{
+						Cmd:     `CREATE SEQUENCE IF NOT EXISTS "public"."posts_c2_seq" OWNED BY "public"."posts"."c2"`,
+						Reverse: `DROP SEQUENCE IF EXISTS "public"."posts_c2_seq"`,
+					},
+					{
+						Cmd:     `ALTER TABLE "public"."posts" ALTER COLUMN "c1" SET DEFAULT nextval('"public"."posts_c1_seq"'), ALTER COLUMN "c2" SET DEFAULT nextval('"public"."posts_c2_seq"'), ALTER COLUMN "c2" TYPE bigint`,
+						Reverse: `ALTER TABLE "public"."posts" ALTER COLUMN "c2" DROP DEFAULT, ALTER COLUMN "c2" TYPE integer, ALTER COLUMN "c1" DROP DEFAULT`,
+					},
+				},
+			},
+		},
+		// Change underlying sequence type.
+		{
+			changes: []schema.Change{
+				&schema.ModifyTable{
+					T: schema.NewTable("posts").
+						SetSchema(schema.New("public")).
+						AddColumns(
+							schema.NewColumn("c1").SetType(&SerialType{T: "serial"}),
+							schema.NewColumn("c2").SetType(&SerialType{T: "bigserial"}),
+						),
+					Changes: schema.Changes{
+						&schema.ModifyColumn{
+							From:   schema.NewColumn("c1").SetType(&SerialType{T: "smallserial"}),
+							To:     schema.NewColumn("c1").SetType(&SerialType{T: "serial"}),
+							Change: schema.ChangeType,
+						},
+						&schema.ModifyColumn{
+							From:   schema.NewColumn("c2").SetType(&SerialType{T: "serial"}),
+							To:     schema.NewColumn("c2").SetType(&SerialType{T: "bigserial"}),
+							Change: schema.ChangeType,
+						},
+					},
+				},
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "public"."posts" ALTER COLUMN "c1" TYPE integer, ALTER COLUMN "c2" TYPE bigint`,
+						Reverse: `ALTER TABLE "public"."posts" ALTER COLUMN "c2" TYPE integer, ALTER COLUMN "c1" TYPE smallint`,
+					},
+				},
+			},
+		},
 	}
 	for i, tt := range tests {
 		t.Run(strconv.Itoa(i), func(t *testing.T) {