Repository: sqlc-dev/sqlc
Pull request #2542 (2023-07-28 22:37:26)

feat(sql/catalog): Support ALTER TABLE IF EXISTS
Add `IF EXISTS` support to the following `ALTER TABLE` statements:

```
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]
```

Fixes https://github.com/sqlc-dev/sqlc/issues/2524


Patch:
diff --git a/internal/engine/postgresql/parse.go b/internal/engine/postgresql/parse.go
index de100be0c..779e5ea50 100644
--- a/internal/engine/postgresql/parse.go
+++ b/internal/engine/postgresql/parse.go
@@ -241,6 +241,7 @@ func translate(node *nodes.Node) (ast.Node, error) {
 			return &ast.AlterTableSetSchemaStmt{
 				Table:     rel.TableName(),
 				NewSchema: makeString(n.Newschema),
+				MissingOk: n.MissingOk,
 			}, nil
 
 		case nodes.ObjectType_OBJECT_TYPE:
@@ -259,8 +260,9 @@ func translate(node *nodes.Node) (ast.Node, error) {
 		n := inner.AlterTableStmt
 		rel := parseRelationFromRangeVar(n.Relation)
 		at := &ast.AlterTableStmt{
-			Table: rel.TableName(),
-			Cmds:  &ast.List{},
+			Table:     rel.TableName(),
+			Cmds:      &ast.List{},
+			MissingOk: n.MissingOk,
 		}
 		for _, cmd := range n.Cmds {
 			switch cmdOneOf := cmd.Node.(type) {
@@ -600,16 +602,18 @@ func translate(node *nodes.Node) (ast.Node, error) {
 		case nodes.ObjectType_OBJECT_COLUMN:
 			rel := parseRelationFromRangeVar(n.Relation)
 			return &ast.RenameColumnStmt{
-				Table:   rel.TableName(),
-				Col:     &ast.ColumnRef{Name: n.Subname},
-				NewName: makeString(n.Newname),
+				Table:     rel.TableName(),
+				Col:       &ast.ColumnRef{Name: n.Subname},
+				NewName:   makeString(n.Newname),
+				MissingOk: n.MissingOk,
 			}, nil
 
 		case nodes.ObjectType_OBJECT_TABLE:
 			rel := parseRelationFromRangeVar(n.Relation)
 			return &ast.RenameTableStmt{
-				Table:   rel.TableName(),
-				NewName: makeString(n.Newname),
+				Table:     rel.TableName(),
+				NewName:   makeString(n.Newname),
+				MissingOk: n.MissingOk,
 			}, nil
 
 		case nodes.ObjectType_OBJECT_TYPE:
diff --git a/internal/sql/ast/alter_table_set_schema_stmt.go b/internal/sql/ast/alter_table_set_schema_stmt.go
index 51ca9f5be..890cb3e5e 100644
--- a/internal/sql/ast/alter_table_set_schema_stmt.go
+++ b/internal/sql/ast/alter_table_set_schema_stmt.go
@@ -3,6 +3,7 @@ package ast
 type AlterTableSetSchemaStmt struct {
 	Table     *TableName
 	NewSchema *string
+	MissingOk bool
 }
 
 func (n *AlterTableSetSchemaStmt) Pos() int {
diff --git a/internal/sql/ast/rename_column_stmt.go b/internal/sql/ast/rename_column_stmt.go
index e85c91e67..498b89d6e 100644
--- a/internal/sql/ast/rename_column_stmt.go
+++ b/internal/sql/ast/rename_column_stmt.go
@@ -1,9 +1,10 @@
 package ast
 
 type RenameColumnStmt struct {
-	Table   *TableName
-	Col     *ColumnRef
-	NewName *string
+	Table     *TableName
+	Col       *ColumnRef
+	NewName   *string
+	MissingOk bool
 }
 
 func (n *RenameColumnStmt) Pos() int {
diff --git a/internal/sql/ast/rename_table_stmt.go b/internal/sql/ast/rename_table_stmt.go
index 53464c779..b879ddb0b 100644
--- a/internal/sql/ast/rename_table_stmt.go
+++ b/internal/sql/ast/rename_table_stmt.go
@@ -1,8 +1,9 @@
 package ast
 
 type RenameTableStmt struct {
-	Table   *TableName
-	NewName *string
+	Table     *TableName
+	NewName   *string
+	MissingOk bool
 }
 
 func (n *RenameTableStmt) Pos() int {
diff --git a/internal/sql/catalog/table.go b/internal/sql/catalog/table.go
index 93486bdc9..ba6ce7a21 100644
--- a/internal/sql/catalog/table.go
+++ b/internal/sql/catalog/table.go
@@ -18,6 +18,16 @@ type Table struct {
 	Comment string
 }
 
+func checkMissing(err error, missingOK bool) error {
+	var serr *sqlerr.Error
+	if errors.As(err, &serr) {
+		if serr.Err == sqlerr.NotFound && missingOK {
+			return nil
+		}
+	}
+	return err
+}
+
 func (table *Table) isExistColumn(cmd *ast.AlterTableCmd) (int, error) {
 	for i, c := range table.Columns {
 		if c.Name == *cmd.Name {
@@ -167,7 +177,7 @@ func (c *Catalog) alterTable(stmt *ast.AlterTableStmt) error {
 	}
 	_, table, err := c.getTable(stmt.Table)
 	if err != nil {
-		return err
+		return checkMissing(err, stmt.MissingOk)
 	}
 	for _, item := range stmt.Cmds.Items {
 		switch cmd := item.(type) {
@@ -206,11 +216,11 @@ func (c *Catalog) alterTableSetSchema(stmt *ast.AlterTableSetSchemaStmt) error {
 	}
 	oldSchema, err := c.getSchema(ns)
 	if err != nil {
-		return err
+		return checkMissing(err, stmt.MissingOk)
 	}
 	tbl, idx, err := oldSchema.getTable(stmt.Table)
 	if err != nil {
-		return err
+		return checkMissing(err, stmt.MissingOk)
 	}
 	tbl.Rel.Schema = *stmt.NewSchema
 	newSchema, err := c.getSchema(*stmt.NewSchema)
@@ -354,7 +364,7 @@ func (c *Catalog) dropTable(stmt *ast.DropTableStmt) error {
 func (c *Catalog) renameColumn(stmt *ast.RenameColumnStmt) error {
 	_, tbl, err := c.getTable(stmt.Table)
 	if err != nil {
-		return err
+		return checkMissing(err, stmt.MissingOk)
 	}
 	idx := -1
 	for i := range tbl.Columns {
@@ -375,7 +385,7 @@ func (c *Catalog) renameColumn(stmt *ast.RenameColumnStmt) error {
 func (c *Catalog) renameTable(stmt *ast.RenameTableStmt) error {
 	sch, tbl, err := c.getTable(stmt.Table)
 	if err != nil {
-		return err
+		return checkMissing(err, stmt.MissingOk)
 	}
 	if _, _, err := sch.getTable(&ast.TableName{Name: *stmt.NewName}); err == nil {
 		return sqlerr.RelationExists(*stmt.NewName)


Test patch:
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/db.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/db.go
new file mode 100644
index 000000000..f3f26ec6f
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+
+package querytest
+
+import (
+	"context"
+
+	"github.com/jackc/pgconn"
+	"github.com/jackc/pgx/v4"
+)
+
+type DBTX interface {
+	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
+	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
+	QueryRow(context.Context, string, ...interface{}) pgx.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx pgx.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/models.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/models.go
new file mode 100644
index 000000000..90d199196
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/models.go
@@ -0,0 +1,7 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+
+package querytest
+
+import ()
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/query.sql.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/query.sql.go
new file mode 100644
index 000000000..45a2497f9
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/go/query.sql.go
@@ -0,0 +1,19 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+)
+
+const placeholder = `-- name: Placeholder :exec
+SELECT 1
+`
+
+func (q *Queries) Placeholder(ctx context.Context) error {
+	_, err := q.db.Exec(ctx, placeholder)
+	return err
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/query.sql b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/query.sql
new file mode 100644
index 000000000..6520aef4b
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/query.sql
@@ -0,0 +1,2 @@
+-- name: Placeholder :exec
+SELECT 1;
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/schema.sql b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/schema.sql
new file mode 100644
index 000000000..43a3e75aa
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/schema.sql
@@ -0,0 +1,7 @@
+ALTER TABLE IF EXISTS foo ADD COLUMN bar integer;
+ALTER TABLE IF EXISTS bar RENAME COLUMN bar TO baz;
+ALTER TABLE IF EXISTS bat RENAME CONSTRAINT bar TO baz;
+ALTER TABLE IF EXISTS baz RENAME TO bar;
+ALTER TABLE IF EXISTS goo SET SCHEMA bar;
+ALTER TABLE IF EXISTS gob ATTACH PARTITION partition_name DEFAULT;
+ALTER TABLE IF EXISTS doo DETACH PARTITION partition_name;
\ No newline at end of file
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/sqlc.json b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/sqlc.json
new file mode 100644
index 000000000..d1244c9e7
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v4/sqlc.json
@@ -0,0 +1,13 @@
+{
+  "version": "1",
+  "packages": [
+    {
+      "path": "go",
+      "engine": "postgresql",
+      "sql_package": "pgx/v4",
+      "name": "querytest",
+      "schema": "schema.sql",
+      "queries": "query.sql"
+    }
+  ]
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/db.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/db.go
new file mode 100644
index 000000000..4964d6b58
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+
+package querytest
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
+)
+
+type DBTX interface {
+	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
+	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
+	QueryRow(context.Context, string, ...interface{}) pgx.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx pgx.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/models.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/models.go
new file mode 100644
index 000000000..90d199196
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/models.go
@@ -0,0 +1,7 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+
+package querytest
+
+import ()
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/query.sql.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/query.sql.go
new file mode 100644
index 000000000..45a2497f9
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/go/query.sql.go
@@ -0,0 +1,19 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+)
+
+const placeholder = `-- name: Placeholder :exec
+SELECT 1
+`
+
+func (q *Queries) Placeholder(ctx context.Context) error {
+	_, err := q.db.Exec(ctx, placeholder)
+	return err
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/query.sql b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/query.sql
new file mode 100644
index 000000000..6520aef4b
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/query.sql
@@ -0,0 +1,2 @@
+-- name: Placeholder :exec
+SELECT 1;
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/schema.sql b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/schema.sql
new file mode 100644
index 000000000..43a3e75aa
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/schema.sql
@@ -0,0 +1,7 @@
+ALTER TABLE IF EXISTS foo ADD COLUMN bar integer;
+ALTER TABLE IF EXISTS bar RENAME COLUMN bar TO baz;
+ALTER TABLE IF EXISTS bat RENAME CONSTRAINT bar TO baz;
+ALTER TABLE IF EXISTS baz RENAME TO bar;
+ALTER TABLE IF EXISTS goo SET SCHEMA bar;
+ALTER TABLE IF EXISTS gob ATTACH PARTITION partition_name DEFAULT;
+ALTER TABLE IF EXISTS doo DETACH PARTITION partition_name;
\ No newline at end of file
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/sqlc.json b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/sqlc.json
new file mode 100644
index 000000000..32ede0715
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/pgx/v5/sqlc.json
@@ -0,0 +1,13 @@
+{
+  "version": "1",
+  "packages": [
+    {
+      "path": "go",
+      "engine": "postgresql",
+      "sql_package": "pgx/v5",
+      "name": "querytest",
+      "schema": "schema.sql",
+      "queries": "query.sql"
+    }
+  ]
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/db.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/db.go
new file mode 100644
index 000000000..fb6ae669f
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/db.go
@@ -0,0 +1,31 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+
+package querytest
+
+import (
+	"context"
+	"database/sql"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/models.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/models.go
new file mode 100644
index 000000000..90d199196
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/models.go
@@ -0,0 +1,7 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+
+package querytest
+
+import ()
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/query.sql.go b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/query.sql.go
new file mode 100644
index 000000000..a5a3ca6bb
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/go/query.sql.go
@@ -0,0 +1,19 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.19.1
+// source: query.sql
+
+package querytest
+
+import (
+	"context"
+)
+
+const placeholder = `-- name: Placeholder :exec
+SELECT 1
+`
+
+func (q *Queries) Placeholder(ctx context.Context) error {
+	_, err := q.db.ExecContext(ctx, placeholder)
+	return err
+}
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/query.sql b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/query.sql
new file mode 100644
index 000000000..6520aef4b
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/query.sql
@@ -0,0 +1,2 @@
+-- name: Placeholder :exec
+SELECT 1;
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/schema.sql b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/schema.sql
new file mode 100644
index 000000000..43a3e75aa
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/schema.sql
@@ -0,0 +1,7 @@
+ALTER TABLE IF EXISTS foo ADD COLUMN bar integer;
+ALTER TABLE IF EXISTS bar RENAME COLUMN bar TO baz;
+ALTER TABLE IF EXISTS bat RENAME CONSTRAINT bar TO baz;
+ALTER TABLE IF EXISTS baz RENAME TO bar;
+ALTER TABLE IF EXISTS goo SET SCHEMA bar;
+ALTER TABLE IF EXISTS gob ATTACH PARTITION partition_name DEFAULT;
+ALTER TABLE IF EXISTS doo DETACH PARTITION partition_name;
\ No newline at end of file
diff --git a/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/sqlc.json b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/sqlc.json
new file mode 100644
index 000000000..f717ca2e6
--- /dev/null
+++ b/internal/endtoend/testdata/ddl_alter_table_if_exists/postgresql/stdlib/sqlc.json
@@ -0,0 +1,12 @@
+{
+  "version": "1",
+  "packages": [
+    {
+      "path": "go",
+      "engine": "postgresql",
+      "name": "querytest",
+      "schema": "schema.sql",
+      "queries": "query.sql"
+    }
+  ]
+}