Repository: ariga/atlas
Pull request #1257 (2022-11-02 10:27:33)

sql/postgres: add tsvector and tsquery


Patch:
diff --git a/doc/md/atlas-schema/sql-types.md b/doc/md/atlas-schema/sql-types.md
index 5ca1f641..edcaaa1d 100644
--- a/doc/md/atlas-schema/sql-types.md
+++ b/doc/md/atlas-schema/sql-types.md
@@ -669,6 +669,23 @@ table "t" {
 }
 ```
 
+### Text Search
+
+The `tsvector` and `tsquery` data types are designed to store and query full text search. Learn more about them in the
+[PostgreSQL website](https://www.postgresql.org/docs/current/datatype-textsearch.html).
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "tsv" {
+    type = tsvector
+  }
+  column "tsq" {
+    type = tsquery
+  }
+}
+```
+
 ### UUID
 
 The `uuid` data type allows creating columns for storing Universally Unique Identifiers (UUID).
diff --git a/sql/postgres/convert.go b/sql/postgres/convert.go
index 352324d3..86f90428 100644
--- a/sql/postgres/convert.go
+++ b/sql/postgres/convert.go
@@ -142,6 +142,10 @@ func FormatType(t schema.Type) (string, error) {
 		f = strings.ToLower(t.T)
 	case *NetworkType:
 		f = strings.ToLower(t.T)
+	case *TextSearchType:
+		if f = strings.ToLower(t.T); f != TypeTSVector && f != TypeTSQuery {
+			return "", fmt.Errorf("postgres: unsupported text search type: %q", t.T)
+		}
 	case *UserDefinedType:
 		f = strings.ToLower(t.T)
 	case *XMLType:
@@ -252,6 +256,8 @@ func columnType(c *columnDesc) (schema.Type, error) {
 			}
 			typ.(*ArrayType).Type = tt
 		}
+	case TypeTSVector, TypeTSQuery:
+		typ = &TextSearchType{T: t}
 	case TypeUserDefined:
 		typ = &UserDefinedType{T: c.fmtype}
 	default:
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index e5fbdf3c..a1cf5fe9 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -206,7 +206,7 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 	switch fromT := fromT.(type) {
 	case *schema.BinaryType, *BitType, *schema.BoolType, *schema.DecimalType, *schema.FloatType,
 		*IntervalType, *schema.IntegerType, *schema.JSONType, *SerialType, *schema.SpatialType,
-		*schema.StringType, *schema.TimeType, *NetworkType, *UserDefinedType:
+		*schema.StringType, *schema.TimeType, *TextSearchType, *NetworkType, *UserDefinedType:
 		t1, err := FormatType(toT)
 		if err != nil {
 			return false, err
diff --git a/sql/postgres/driver.go b/sql/postgres/driver.go
index 61d41192..eb602d28 100644
--- a/sql/postgres/driver.go
+++ b/sql/postgres/driver.go
@@ -375,6 +375,8 @@ const (
 	TypeUUID        = "uuid"
 	TypeMoney       = "money"
 	TypeInterval    = "interval"
+	TypeTSQuery     = "tsquery"
+	TypeTSVector    = "tsvector"
 	TypeUserDefined = "user-defined"
 )
 
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index afe6fd99..a709f935 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -694,6 +694,13 @@ type (
 		SequenceName string
 	}
 
+	// A TextSearchType defines full text search types.
+	// https://www.postgresql.org/docs/current/datatype-textsearch.html
+	TextSearchType struct {
+		schema.Type
+		T string
+	}
+
 	// A UUIDType defines a UUID type.
 	UUIDType struct {
 		schema.Type
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 0b09b92c..f417cea6 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -622,6 +622,8 @@ var TypeRegistry = schemahcl.NewRegistry(
 		schemahcl.NewTypeSpec(TypeJSONB),
 		schemahcl.NewTypeSpec(TypeUUID),
 		schemahcl.NewTypeSpec(TypeMoney),
+		schemahcl.NewTypeSpec(TypeTSVector),
+		schemahcl.NewTypeSpec(TypeTSQuery),
 		schemahcl.NewTypeSpec("hstore"),
 		schemahcl.NewTypeSpec("sql", schemahcl.WithAttributes(&schemahcl.TypeAttr{Name: "def", Required: true, Kind: reflect.String})),
 	),


Test patch:
diff --git a/internal/integration/testdata/postgres/column-textsearch.txt b/internal/integration/testdata/postgres/column-textsearch.txt
new file mode 100644
index 00000000..d9017dde
--- /dev/null
+++ b/internal/integration/testdata/postgres/column-textsearch.txt
@@ -0,0 +1,49 @@
+apply 1.hcl
+cmpshow t 1.sql
+cmphcl 1.inspect.hcl
+
+-- 1.hcl --
+schema "script_column_textsearch" {}
+
+table "t" {
+  schema = schema.script_column_textsearch
+  column "tsv" {
+    type = tsvector
+  }
+  column "tsq" {
+    type = tsquery
+  }
+  index "idx_tsv" {
+    type = GIN
+    columns = [column.tsv]
+  }
+}
+
+-- 1.sql --
+         Table "script_column_textsearch.t"
+ Column |   Type   | Collation | Nullable | Default
+--------+----------+-----------+----------+---------
+ tsv    | tsvector |           | not null |
+ tsq    | tsquery  |           | not null |
+Indexes:
+    "idx_tsv" gin (tsv)
+
+
+-- 1.inspect.hcl --
+table "t" {
+  schema = schema.script_column_textsearch
+  column "tsv" {
+    null = false
+    type = tsvector
+  }
+  column "tsq" {
+    null = false
+    type = tsquery
+  }
+  index "idx_tsv" {
+    columns = [column.tsv]
+    type    = GIN
+  }
+}
+schema "script_column_textsearch" {
+}
\ No newline at end of file
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 6dc62d73..75e83be3 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -80,6 +80,8 @@ func TestDriver_InspectTable(t *testing.T) {
  users       |  c33         | USER-DEFINED                | test."status""."    | NO          |  'unknown'::test."status""."           |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | e       |         |         | 16775
  users       |  c34         | ARRAY                       | state[]             | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  16774  |  e      | 16779
  users       |  c35         | character                   | domain_char         | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | d       |  16774  |  e      | 16779
+ users       |  c36         | tsvector                    | tsvector            | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  16774  |         | 16779
+ users       |  c37         | tsquery                     | tsquery             | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  16774  |         | 16779
 `))
 				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1, $2)`)).
 					WithArgs(16774, 16775).
@@ -136,6 +138,8 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c33", Type: &schema.ColumnType{Raw: `status".`, Type: &schema.EnumType{T: `status".`, Values: []string{"unknown"}, Schema: schema.New("test")}}, Default: &schema.Literal{V: "'unknown'"}},
 					{Name: "c34", Type: &schema.ColumnType{Raw: "ARRAY", Type: &ArrayType{T: "state[]", Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}, Schema: t.Schema}}}},
 					{Name: "c35", Type: &schema.ColumnType{Raw: "character", Type: &UserDefinedType{T: "domain_char"}}},
+					{Name: "c36", Type: &schema.ColumnType{Raw: "tsvector", Type: &TextSearchType{T: "tsvector"}}},
+					{Name: "c37", Type: &schema.ColumnType{Raw: "tsquery", Type: &TextSearchType{T: "tsquery"}}},
 				}, t.Columns)
 			},
 		},