Repository: ariga/atlas
Pull request #867 (2022-06-16 14:17:53)

sql/postgres: ensure identity column updates are not harmful

a8m: Checked that the new test case fails on master:

```
Error:      	Received unexpected error:
        	pq: duplicate key value violates unique constraint "global_id_pkey"
Test:       	TestPostgres_Ent/GlobalUniqueID/postgres14
```

Patch:
diff --git a/sql/postgres/crdb.go b/sql/postgres/crdb.go
index cde4ee97..61aff3f2 100644
--- a/sql/postgres/crdb.go
+++ b/sql/postgres/crdb.go
@@ -318,6 +318,7 @@ SELECT
 	t1.is_identity,
 	t5.start_value as identity_start,
 	t5.increment_by as identity_increment,
+	t5.last_value AS identity_last,
 	t1.identity_generation,
 	t1.generation_expression,
 	col_description(t3.oid, "ordinal_position") AS comment,
@@ -332,7 +333,7 @@ FROM
 	LEFT JOIN pg_sequences AS t5
 	ON t5.schemaname || '.' || t5.sequencename = btrim(btrim(t1.column_default, 'nextval('''), '''::REGCLASS)')
 WHERE
-	table_schema = $1 AND table_name IN (%s)
+	t1.table_schema = $1 AND t1.table_name IN (%s)
 ORDER BY
 	t1.table_name, t1.ordinal_position
 `
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 10a55724..be7096c2 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -165,12 +165,12 @@ func (i *inspect) columns(ctx context.Context, s *schema.Schema) error {
 // addColumn scans the current row and adds a new column from it to the table.
 func (i *inspect) addColumn(s *schema.Schema, rows *sql.Rows) error {
 	var (
-		typid, maxlen, precision, timeprecision, scale, seqstart, seqinc                                              sql.NullInt64
+		typid, maxlen, precision, timeprecision, scale, seqstart, seqinc, seqlast                                     sql.NullInt64
 		table, name, typ, nullable, defaults, udt, identity, genidentity, genexpr, charset, collate, comment, typtype sql.NullString
 	)
 	if err := rows.Scan(
 		&table, &name, &typ, &nullable, &defaults, &maxlen, &precision, &timeprecision, &scale, &charset,
-		&collate, &udt, &identity, &seqstart, &seqinc, &genidentity, &genexpr, &comment, &typtype, &typid,
+		&collate, &udt, &identity, &seqstart, &seqinc, &seqlast, &genidentity, &genexpr, &comment, &typtype, &typid,
 	); err != nil {
 		return err
 	}
@@ -202,6 +202,7 @@ func (i *inspect) addColumn(s *schema.Schema, rows *sql.Rows) error {
 		c.Attrs = append(c.Attrs, &Identity{
 			Generation: genidentity.String,
 			Sequence: &Sequence{
+				Last:      seqlast.Int64,
 				Start:     seqstart.Int64,
 				Increment: seqinc.Int64,
 			},
@@ -717,6 +718,9 @@ type (
 	// https://www.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.
+		Last int64
 	}
 
 	// Identity defines an identity column.
@@ -894,6 +898,7 @@ SELECT
 	t1.is_identity,
 	t1.identity_start,
 	t1.identity_increment,
+	t5.last_value AS identity_last,
 	t1.identity_generation,
 	t1.generation_expression,
 	col_description(t3.oid, "ordinal_position") AS comment,
@@ -905,6 +910,8 @@ FROM
 	JOIN pg_catalog.pg_class AS t3 ON t3.relnamespace = t2.oid AND t3.relname = t1.table_name
 	LEFT JOIN pg_catalog.pg_type AS t4
 	ON t1.udt_name = t4.typname
+	LEFT JOIN pg_sequences AS t5
+	ON t5.schemaname || '.' || t5.sequencename = pg_get_serial_sequence(table_schema || '.' || t1.table_name, t1.column_name)
 WHERE
 	table_schema = $1 AND table_name IN (%s)
 ORDER BY
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index c5fa5e6c..7f6fb876 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -326,7 +326,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.Change, change.To); err != nil {
+				if err := s.alterColumn(b, change); err != nil {
 					return err
 				}
 				if change.Change.Is(schema.ChangeGenerated) {
@@ -630,44 +630,49 @@ func (s *state) columnDefault(b *sqlx.Builder, c *schema.Column) {
 	}
 }
 
-func (s *state) alterColumn(b *sqlx.Builder, k schema.ChangeKind, c *schema.Column) error {
-	for !k.Is(schema.NoChange) {
-		b.P("ALTER COLUMN").Ident(c.Name)
+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.Type.Type)
+			t, err := FormatType(c.To.Type.Type)
 			if err != nil {
 				return err
 			}
 			b.P("TYPE", t)
-			if collate := (schema.Collation{}); sqlx.Has(c.Attrs, &collate) {
+			if collate := (schema.Collation{}); sqlx.Has(c.To.Attrs, &collate) {
 				b.P("COLLATE", collate.V)
 			}
 			k &= ^schema.ChangeType
-		case k.Is(schema.ChangeNull) && c.Type.Null:
+		case k.Is(schema.ChangeNull) && c.To.Type.Null:
 			b.P("DROP NOT NULL")
 			k &= ^schema.ChangeNull
-		case k.Is(schema.ChangeNull) && !c.Type.Null:
+		case k.Is(schema.ChangeNull) && !c.To.Type.Null:
 			b.P("SET NOT NULL")
 			k &= ^schema.ChangeNull
-		case k.Is(schema.ChangeDefault) && c.Default == nil:
+		case k.Is(schema.ChangeDefault) && c.To.Default == nil:
 			b.P("DROP DEFAULT")
 			k &= ^schema.ChangeDefault
-		case k.Is(schema.ChangeDefault) && c.Default != nil:
-			s.columnDefault(b.P("SET"), c)
+		case k.Is(schema.ChangeDefault) && c.To.Default != nil:
+			s.columnDefault(b.P("SET"), c.To)
 			k &= ^schema.ChangeDefault
 		case k.Is(schema.ChangeAttr):
-			id, ok := identity(c.Attrs)
+			toI, ok := identity(c.To.Attrs)
 			if !ok {
-				return fmt.Errorf("unexpected attribute change (expect IDENTITY): %v", c.Attrs)
+				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", id.Generation, "SET START WITH", strconv.FormatInt(id.Sequence.Start, 10), "SET INCREMENT BY", strconv.FormatInt(id.Sequence.Increment, 10), "RESTART")
+			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.Attrs, &schema.GeneratedExpr{}) {
-				return fmt.Errorf("unexpected generation expression change (expect DROP EXPRESSION): %v", c.Attrs)
+			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


Test patch:
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index 052e67ef..ef4b2958 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -434,6 +434,51 @@ func TestPostgres_Ent(t *testing.T) {
 	pgRun(t, func(t *pgTest) {
 		testEntIntegration(t, dialect.Postgres, t.db)
 	})
+	// Migration to global unique identifiers.
+	t.Run("GlobalUniqueID", func(t *testing.T) {
+		pgRun(t, func(t *pgTest) {
+			ctx := context.Background()
+			t.dropTables("global_id")
+			_, err := t.driver().ExecContext(ctx, "CREATE TABLE global_id (id int NOT NULL GENERATED BY DEFAULT AS IDENTITY, PRIMARY KEY(id))")
+			require.NoError(t, err)
+			_, err = t.driver().ExecContext(ctx, "ALTER TABLE global_id ALTER COLUMN id RESTART WITH 1024")
+			require.NoError(t, err)
+			_, err = t.driver().ExecContext(ctx, "INSERT INTO global_id VALUES (default), (default)")
+			require.NoError(t, err)
+			var id int
+			require.NoError(t, t.db.QueryRow("SELECT id FROM global_id").Scan(&id))
+			require.Equal(t, 1024, id)
+			_, err = t.driver().ExecContext(ctx, "DELETE FROM global_id WHERE id = 1024")
+			require.NoError(t, err)
+
+			globalT := t.loadTable("global_id")
+			c, ok := globalT.Column("id")
+			require.True(t, ok)
+			require.EqualValues(t, 1, c.Attrs[0].(*postgres.Identity).Sequence.Start)
+			t.migrate(&schema.ModifyTable{
+				T: globalT,
+				Changes: []schema.Change{
+					&schema.ModifyColumn{
+						From: globalT.Columns[0],
+						To: schema.NewIntColumn("id", "int").
+							AddAttrs(&postgres.Identity{
+								Generation: "BY DEFAULT",
+								Sequence: &postgres.Sequence{
+									Start: 1024,
+								},
+							}),
+						Change: schema.ChangeAttr,
+					},
+				},
+			})
+			_, err = t.driver().ExecContext(ctx, "INSERT INTO global_id VALUES (default), (default)")
+			require.NoError(t, err)
+			globalT = t.loadTable("global_id")
+			c, ok = globalT.Column("id")
+			require.True(t, ok)
+			require.EqualValues(t, 1024, c.Attrs[0].(*postgres.Identity).Sequence.Start)
+		})
+	})
 }
 
 func TestPostgres_AdvisoryLock(t *testing.T) {
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index eba79b9f..0afff12a 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -41,38 +41,38 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid
--------------+--------------+-----------------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+-------------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
- users       |  id          | bigint                      | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8        | YES         |      100       |          1         |    BY DEFAULT       |                       |         | b       |    20
- users       |  rank        | integer                     | YES         |                                 |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                     |                       | rank    | b       |    23
- users       |  c1          | smallint                    | NO          |           1000                  |                          |                16 |                    |             0 |                    |                | int2        | NO          |                |                    |                     |                       |         | b       |    21
- users       |  c2          | bit                         | NO          |                                 |                        1 |                   |                    |               |                    |                | bit         | NO          |                |                    |                     |                       |         | b       |  1560
- users       |  c3          | bit varying                 | NO          |                                 |                       10 |                   |                    |               |                    |                | varbit      | NO          |                |                    |                     |                       |         | b       |  1562
- users       |  c4          | boolean                     | NO          |                                 |                          |                   |                    |               |                    |                | bool        | NO          |                |                    |                     |                       |         | b       |    16
- users       |  c5          | bytea                       | NO          |                                 |                          |                   |                    |               |                    |                | bytea       | NO          |                |                    |                     |                       |         | b       |    17
- users       |  c6          | character                   | NO          |                                 |                      100 |                   |                    |               |                    |                | bpchar      | NO          |                |                    |                     |                       |         | b       |  1042
- users       |  c7          | character varying           | NO          | 'logged_in'::character varying  |                          |                   |                    |               |                    |                | varchar     | NO          |                |                    |                     |                       |         | b       |  1043
- users       |  c8          | cidr                        | NO          |                                 |                          |                   |                    |               |                    |                | cidr        | NO          |                |                    |                     |                       |         | b       |   650
- users       |  c9          | circle                      | NO          |                                 |                          |                   |                    |               |                    |                | circle      | NO          |                |                    |                     |                       |         | b       |   718
- users       |  c10         | date                        | NO          |                                 |                          |                   |                    |               |                    |                | date        | NO          |                |                    |                     |                       |         | b       |  1082
- users       |  c11         | time with time zone         | NO          |                                 |                          |                   |                    |               |                    |                | timetz      | NO          |                |                    |                     |                       |         | b       |  1266
- users       |  c12         | double precision            | NO          |                                 |                          |                53 |                    |               |                    |                | float8      | NO          |                |                    |                     |                       |         | b       |   701
- users       |  c13         | real                        | NO          |           random()              |                          |                24 |                    |               |                    |                | float4      | NO          |                |                    |                     |                       |         | b       |   700
- users       |  c14         | json                        | NO          |           '{}'::json            |                          |                   |                    |               |                    |                | json        | NO          |                |                    |                     |                       |         | b       |   114
- users       |  c15         | jsonb                       | NO          |           '{}'::jsonb           |                          |                   |                    |               |                    |                | jsonb       | NO          |                |                    |                     |                       |         | b       |  3802
- users       |  c16         | money                       | NO          |                                 |                          |                   |                    |               |                    |                | money       | NO          |                |                    |                     |                       |         | b       |   790
- users       |  c17         | numeric                     | NO          |                                 |                          |                   |                    |               |                    |                | numeric     | NO          |                |                    |                     |                       |         | b       |  1700
- users       |  c18         | numeric                     | NO          |                                 |                          |                 4 |                    |             4 |                    |                | numeric     | NO          |                |                    |                     |                       |         | b       |  1700
- users       |  c19         | integer                     | NO          | nextval('t1_c19_seq'::regclass) |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                     |                       |         | b       |    23
- users       |  c20         | uuid                        | NO          |                                 |                          |                   |                    |               |                    |                | uuid        | NO          |                |                    |                     |                       |         | b       |  2950
- users       |  c21         | xml                         | NO          |                                 |                          |                   |                    |               |                    |                | xml         | NO          |                |                    |                     |                       |         | b       |   142
- users       |  c22         | ARRAY                       | YES         |                                 |                          |                   |                    |               |                    |                | _int4       | NO          |                |                    |                     |                       |         | b       |  1007
- users       |  c23         | USER-DEFINED                | YES         |                                 |                          |                   |                    |               |                    |                | ltree       | NO          |                |                    |                     |                       |         | b       | 16535
- users       |  c24         | USER-DEFINED                | NO          |                                 |                          |                   |                    |               |                    |                | state       | NO          |                |                    |                     |                       |         | e       | 16774
- users       |  c25         | timestamp without time zone | NO          |            now()                |                          |                   |                  4 |               |                    |                | timestamp   | NO          |                |                    |                     |                       |         | b       |  1114
- users       |  c26         | timestamp with time zone    | NO          |                                 |                          |                   |                  6 |               |                    |                | timestamptz | NO          |                |                    |                     |                       |         | b       |  1184
- users       |  c27         | time without time zone      | NO          |                                 |                          |                   |                  6 |               |                    |                | time        | NO          |                |                    |                     |                       |         | b       |  1266
- users       |  c28         | int                         | NO          |                                 |                          |                   |                  6 |               |                    |                | int8        | NO          |                |                    |                     |        (c1 + c2)      |         | b       |  1267
+ table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype |  oid
+-------------+--------------+-----------------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+-------------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-------
+ users       |  id          | bigint                      | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8        | YES         |      100       |          1         |          1       |    BY DEFAULT       |                       |         | b       |    20
+ users       |  rank        | integer                     | YES         |                                 |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                  |                     |                       | rank    | b       |    23
+ users       |  c1          | smallint                    | NO          |           1000                  |                          |                16 |                    |             0 |                    |                | int2        | NO          |                |                    |                  |                     |                       |         | b       |    21
+ users       |  c2          | bit                         | NO          |                                 |                        1 |                   |                    |               |                    |                | bit         | NO          |                |                    |                  |                     |                       |         | b       |  1560
+ users       |  c3          | bit varying                 | NO          |                                 |                       10 |                   |                    |               |                    |                | varbit      | NO          |                |                    |                  |                     |                       |         | b       |  1562
+ users       |  c4          | boolean                     | NO          |                                 |                          |                   |                    |               |                    |                | bool        | NO          |                |                    |                  |                     |                       |         | b       |    16
+ users       |  c5          | bytea                       | NO          |                                 |                          |                   |                    |               |                    |                | bytea       | NO          |                |                    |                  |                     |                       |         | b       |    17
+ users       |  c6          | character                   | NO          |                                 |                      100 |                   |                    |               |                    |                | bpchar      | NO          |                |                    |                  |                     |                       |         | b       |  1042
+ users       |  c7          | character varying           | NO          | 'logged_in'::character varying  |                          |                   |                    |               |                    |                | varchar     | NO          |                |                    |                  |                     |                       |         | b       |  1043
+ users       |  c8          | cidr                        | NO          |                                 |                          |                   |                    |               |                    |                | cidr        | NO          |                |                    |                  |                     |                       |         | b       |   650
+ users       |  c9          | circle                      | NO          |                                 |                          |                   |                    |               |                    |                | circle      | NO          |                |                    |                  |                     |                       |         | b       |   718
+ users       |  c10         | date                        | NO          |                                 |                          |                   |                    |               |                    |                | date        | NO          |                |                    |                  |                     |                       |         | b       |  1082
+ users       |  c11         | time with time zone         | NO          |                                 |                          |                   |                    |               |                    |                | timetz      | NO          |                |                    |                  |                     |                       |         | b       |  1266
+ users       |  c12         | double precision            | NO          |                                 |                          |                53 |                    |               |                    |                | float8      | NO          |                |                    |                  |                     |                       |         | b       |   701
+ users       |  c13         | real                        | NO          |           random()              |                          |                24 |                    |               |                    |                | float4      | NO          |                |                    |                  |                     |                       |         | b       |   700
+ users       |  c14         | json                        | NO          |           '{}'::json            |                          |                   |                    |               |                    |                | json        | NO          |                |                    |                  |                     |                       |         | b       |   114
+ users       |  c15         | jsonb                       | NO          |           '{}'::jsonb           |                          |                   |                    |               |                    |                | jsonb       | NO          |                |                    |                  |                     |                       |         | b       |  3802
+ users       |  c16         | money                       | NO          |                                 |                          |                   |                    |               |                    |                | money       | NO          |                |                    |                  |                     |                       |         | b       |   790
+ users       |  c17         | numeric                     | NO          |                                 |                          |                   |                    |               |                    |                | numeric     | NO          |                |                    |                  |                     |                       |         | b       |  1700
+ users       |  c18         | numeric                     | NO          |                                 |                          |                 4 |                    |             4 |                    |                | numeric     | NO          |                |                    |                  |                     |                       |         | b       |  1700
+ users       |  c19         | integer                     | NO          | nextval('t1_c19_seq'::regclass) |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                  |                     |                       |         | b       |    23
+ users       |  c20         | uuid                        | NO          |                                 |                          |                   |                    |               |                    |                | uuid        | NO          |                |                    |                  |                     |                       |         | b       |  2950
+ users       |  c21         | xml                         | NO          |                                 |                          |                   |                    |               |                    |                | xml         | NO          |                |                    |                  |                     |                       |         | b       |   142
+ users       |  c22         | ARRAY                       | YES         |                                 |                          |                   |                    |               |                    |                | _int4       | NO          |                |                    |                  |                     |                       |         | b       |  1007
+ users       |  c23         | USER-DEFINED                | YES         |                                 |                          |                   |                    |               |                    |                | ltree       | NO          |                |                    |                  |                     |                       |         | b       | 16535
+ users       |  c24         | USER-DEFINED                | NO          |                                 |                          |                   |                    |               |                    |                | state       | NO          |                |                    |                  |                     |                       |         | e       | 16774
+ users       |  c25         | timestamp without time zone | NO          |            now()                |                          |                   |                  4 |               |                    |                | timestamp   | NO          |                |                    |                  |                     |                       |         | b       |  1114
+ users       |  c26         | timestamp with time zone    | NO          |                                 |                          |                   |                  6 |               |                    |                | timestamptz | NO          |                |                    |                  |                     |                       |         | b       |  1184
+ users       |  c27         | time without time zone      | NO          |                                 |                          |                   |                  6 |               |                    |                | time        | NO          |                |                    |                  |                     |                       |         | b       |  1266
+ users       |  c28         | int                         | NO          |                                 |                          |                   |                  6 |               |                    |                | int8        | NO          |                |                    |                  |                     |        (c1 + c2)      |         | b       |  1267
 `))
 				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1)`)).
 					WithArgs(16774).
@@ -91,7 +91,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.NoError(err)
 				require.Equal("users", t.Name)
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&Identity{Generation: "BY DEFAULT", Sequence: &Sequence{Start: 100, Increment: 1}}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&Identity{Generation: "BY DEFAULT", Sequence: &Sequence{Start: 100, Increment: 1, Last: 1}}}},
 					{Name: "rank", Type: &schema.ColumnType{Raw: "integer", Null: true, Type: &schema.IntegerType{T: "integer"}}, Attrs: []schema.Attr{&schema.Comment{Text: "rank"}}},
 					{Name: "c1", Type: &schema.ColumnType{Raw: "smallint", Type: &schema.IntegerType{T: "smallint"}}, Default: &schema.Literal{V: "1000"}},
 					{Name: "c2", Type: &schema.ColumnType{Raw: "bit", Type: &BitType{T: "bit", Len: 1}}},
@@ -131,11 +131,11 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-table_name | column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid
------------+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
-users      | id          | bigint              | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |                       |         | b       |    20
-users      | c1          | smallint            | NO          |                                 |                          |                16 |                    |             0 |                    |                | int2     | NO          |                |                    |                     |                       |         | b       |    21
-users      | parent_id   | bigint              | YES         |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |                       |         | b       |    22
+table_name | column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype |  oid
+-----------+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-------
+users      | id          | bigint              | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                  |                     |                       |         | b       |    20
+users      | c1          | smallint            | NO          |                                 |                          |                16 |                    |             0 |                    |                | int2     | NO          |                |                    |                  |                     |                       |         | b       |    21
+users      | parent_id   | bigint              | YES         |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                  |                     |                       |         | b       |    22
 `))
 				m.ExpectQuery(queryIndexes).
 					WithArgs("public", "users").
@@ -192,11 +192,11 @@ users           | idx6            | brin        | c1          | f       | t
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-table_name | column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid
------------+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
-users      | id          | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                     |                       |         | b       |    20
-users      | oid         | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                     |                       |         | b       |    21
-users      | uid         | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                     |                       |         | b       |    21
+table_name | column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype |  oid
+-----------+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-------
+users      | id          | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                  |                     |                       |         | b       |    20
+users      | oid         | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                  |                     |                       |         | b       |    21
+users      | uid         | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                  |                     |                       |         | b       |    21
 `))
 				m.noIndexes()
 				m.ExpectQuery(queryFKs).
@@ -239,11 +239,11 @@ self_reference  | users      | uid         | public       | users
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-table_name |column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype | oid
------------+------------+-----------+-------------+----------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-----
-users      | c1         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
-users      | c2         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
-users      | c3         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
+table_name |column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype | oid
+-----------+------------+-----------+-------------+----------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-----
+users      | c1         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
+users      | c2         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
+users      | c3         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -332,13 +332,13 @@ public
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(columnsQuery, "$2, $3, $4"))).
 		WithArgs("public", "logs1", "logs2", "logs3").
 		WillReturnRows(sqltest.Rows(`
-table_name |column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype | oid
------------+------------+-----------+-------------+----------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-----
-logs1      | c1         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
-logs2      | c2         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
-logs2      | c3         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
-logs3      | c4         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
-logs3      | c5         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |                       |         | b       |  23
+table_name |column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment |   identity_last  | identity_generation | generation_expression | comment | typtype | oid
+-----------+------------+-----------+-------------+----------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+------------------+---------------------+-----------------------+---------+---------+-----
+logs1      | c1         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
+logs2      | c2         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
+logs2      | c3         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
+logs3      | c4         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
+logs3      | c5         | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                  |                     |                       |         | b       |  23
 `))
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(indexesQuery, "$2, $3, $4"))).
 		WillReturnRows(sqlmock.NewRows([]string{"table_name", "index_name", "column_name", "primary", "unique", "constraint_type", "predicate", "expression"}))
@@ -400,12 +400,12 @@ public
 	mk.ExpectQuery(queryCrdbColumns).
 		WithArgs("public", "users").
 		WillReturnRows(sqltest.Rows(`
-table_name  | column_name | data_type | is_nullable |              column_default               | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment |       identity_generation        | generation_expression | comment | typtype | oid 
-------------+-------------+-----------+-------------+-------------------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+----------------------------------+-----------------------+---------+---------+-----
-users       | a           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                                  |                       |         | b       | 20 
-users       | b           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                                  |                       |         | b       | 20 
-users       | c           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                                  |                       |         | b       | 20 
-users       | d           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                                  |                       |         | b       | 20 
+table_name  | column_name | data_type | is_nullable |              column_default               | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment |   identity_last  |  identity_generation  | generation_expression | comment | typtype | oid 
+------------+-------------+-----------+-------------+-------------------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+------------------+-----------------------+-----------------------+---------+---------+-----
+users       | a           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                  |                       |                       |         | b       | 20 
+users       | b           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                  |                       |                       |         | b       | 20 
+users       | c           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                  |                       |                       |         | b       | 20 
+users       | d           | bigint    | NO          |                                           |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                  |                       |                       |         | b       | 20 
 `))
 	mk.ExpectQuery(queryCrdbIndexes).
 		WithArgs("public", "users").
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 45ed4adb..155aa853 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -414,6 +414,41 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := &schema.Table{
+						Name: "users",
+						Columns: []*schema.Column{
+							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}},
+						},
+					}
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.DropColumn{
+								C: &schema.Column{Name: "name", Type: &schema.ColumnType{Type: &schema.StringType{T: "varchar"}}},
+							},
+							&schema.ModifyColumn{
+								From:   &schema.Column{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&Identity{Sequence: &Sequence{Start: 0, Last: 1025}}}},
+								To:     &schema.Column{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&Identity{Sequence: &Sequence{Start: 1024}}}},
+								Change: schema.ChangeAttr,
+							},
+						},
+					}
+				}(),
+			},
+			plan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "users" DROP COLUMN "name", ALTER COLUMN "id" SET GENERATED BY DEFAULT SET START WITH 1024 SET INCREMENT BY 1`,
+						Reverse: `ALTER TABLE "users" ALTER COLUMN "id" SET GENERATED BY DEFAULT SET START WITH 1 SET INCREMENT BY 1, ADD COLUMN "name" character varying NOT NULL`,
+					},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				&schema.ModifyTable{