Repository: ariga/atlas
Pull request #1314 (2022-11-28 06:34:29)

sql/postgres: support range types
Fixed https://github.com/ariga/atlas/issues/1313


Patch:
diff --git a/doc/md/atlas-schema/sql-types.md b/doc/md/atlas-schema/sql-types.md
index edcaaa1d..2626edec 100644
--- a/doc/md/atlas-schema/sql-types.md
+++ b/doc/md/atlas-schema/sql-types.md
@@ -620,6 +620,54 @@ table "t" {
 }
 ```
 
+### Range
+
+PostgreSQL supports the creation of range types for storing range of values of some element type.
+Learn more about them in the [PostgreSQL website](https://www.postgresql.org/docs/current/rangetypes.html).
+
+
+```hcl
+table "t" {
+  schema = schema.test
+  column "r1" {
+    type = int4range
+  }
+  column "r2" {
+    type = int8range
+  }
+  column "r3" {
+    type = numrange
+  }
+  column "r4" {
+    type = tsrange
+  }
+  column "r5" {
+    type = tstzrange
+  }
+  column "r6" {
+    type = daterange
+  }
+  column "r7" {
+    type = int4multirange
+  }
+  column "r8" {
+    type = int8multirange
+  }
+  column "r9" {
+    type = nummultirange
+  }
+  column "r10" {
+    type = tsmultirange
+  }
+  column "r11" {
+    type = tstzmultirange
+  }
+  column "r12" {
+    type = datemultirange
+  }
+}
+```
+
 ### Serial
 
 PostgreSQL supports creating columns of types `smallserial`, `serial`, and `bigserial`. Note that these types are not
diff --git a/sql/postgres/convert.go b/sql/postgres/convert.go
index 86f90428..3162cc70 100644
--- a/sql/postgres/convert.go
+++ b/sql/postgres/convert.go
@@ -142,6 +142,13 @@ func FormatType(t schema.Type) (string, error) {
 		f = strings.ToLower(t.T)
 	case *NetworkType:
 		f = strings.ToLower(t.T)
+	case *RangeType:
+		switch f = strings.ToLower(t.T); f {
+		case TypeInt4Range, TypeInt4MultiRange, TypeInt8Range, TypeInt8MultiRange, TypeNumRange, TypeNumMultiRange,
+			TypeTSRange, TypeTSMultiRange, TypeTSTZRange, TypeTSTZMultiRange, TypeDateRange, TypeDateMultiRange:
+		default:
+			return "", fmt.Errorf("postgres: unsupported range type: %q", 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)
@@ -258,6 +265,9 @@ func columnType(c *columnDesc) (schema.Type, error) {
 		}
 	case TypeTSVector, TypeTSQuery:
 		typ = &TextSearchType{T: t}
+	case TypeInt4Range, TypeInt4MultiRange, TypeInt8Range, TypeInt8MultiRange, TypeNumRange, TypeNumMultiRange,
+		TypeTSRange, TypeTSMultiRange, TypeTSTZRange, TypeTSTZMultiRange, TypeDateRange, TypeDateMultiRange:
+		typ = &RangeType{T: t}
 	case TypeUserDefined:
 		typ = &UserDefinedType{T: c.fmtype}
 	default:
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index 71059fb3..328f5c1b 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -232,7 +232,7 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 	var changed bool
 	switch fromT := fromT.(type) {
 	case *schema.BinaryType, *BitType, *schema.BoolType, *schema.DecimalType, *schema.FloatType,
-		*IntervalType, *schema.IntegerType, *schema.JSONType, *SerialType, *schema.SpatialType,
+		*IntervalType, *schema.IntegerType, *schema.JSONType, *RangeType, *SerialType, *schema.SpatialType,
 		*schema.StringType, *schema.TimeType, *TextSearchType, *NetworkType, *UserDefinedType:
 		t1, err := FormatType(toT)
 		if err != nil {
diff --git a/sql/postgres/driver.go b/sql/postgres/driver.go
index 511e2652..0b5a87b3 100644
--- a/sql/postgres/driver.go
+++ b/sql/postgres/driver.go
@@ -378,6 +378,19 @@ const (
 	TypeTSQuery     = "tsquery"
 	TypeTSVector    = "tsvector"
 	TypeUserDefined = "user-defined"
+
+	TypeInt4Range      = "int4range"
+	TypeInt4MultiRange = "int4multirange"
+	TypeInt8Range      = "int8range"
+	TypeInt8MultiRange = "int8multirange"
+	TypeNumRange       = "numrange"
+	TypeNumMultiRange  = "nummultirange"
+	TypeTSRange        = "tsrange"
+	TypeTSMultiRange   = "tsmultirange"
+	TypeTSTZRange      = "tstzrange"
+	TypeTSTZMultiRange = "tstzmultirange"
+	TypeDateRange      = "daterange"
+	TypeDateMultiRange = "datemultirange"
 )
 
 // List of supported index types.
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index eb5ebec5..f4fb7e19 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -711,6 +711,13 @@ type (
 		T string
 	}
 
+	// A RangeType defines a range type.
+	// https://www.postgresql.org/docs/current/rangetypes.html
+	RangeType struct {
+		schema.Type
+		T string
+	}
+
 	// A SerialType defines a serial type.
 	// https://postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
 	SerialType struct {
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index e3d92ff1..6aec4d70 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -679,6 +679,18 @@ var TypeRegistry = schemahcl.NewRegistry(
 		schemahcl.NewTypeSpec(TypeMoney),
 		schemahcl.NewTypeSpec(TypeTSVector),
 		schemahcl.NewTypeSpec(TypeTSQuery),
+		schemahcl.NewTypeSpec(TypeInt4Range),
+		schemahcl.NewTypeSpec(TypeInt4MultiRange),
+		schemahcl.NewTypeSpec(TypeInt8Range),
+		schemahcl.NewTypeSpec(TypeInt8MultiRange),
+		schemahcl.NewTypeSpec(TypeNumRange),
+		schemahcl.NewTypeSpec(TypeNumMultiRange),
+		schemahcl.NewTypeSpec(TypeTSRange),
+		schemahcl.NewTypeSpec(TypeTSMultiRange),
+		schemahcl.NewTypeSpec(TypeTSTZRange),
+		schemahcl.NewTypeSpec(TypeTSTZMultiRange),
+		schemahcl.NewTypeSpec(TypeDateRange),
+		schemahcl.NewTypeSpec(TypeDateMultiRange),
 		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-range.txt b/internal/integration/testdata/postgres/column-range.txt
new file mode 100644
index 00000000..756a3139
--- /dev/null
+++ b/internal/integration/testdata/postgres/column-range.txt
@@ -0,0 +1,64 @@
+only postgres14 postgres15
+
+apply 1.hcl
+cmpshow t 1.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "t" {
+  schema = schema.$db
+  column "r1" {
+    type = int4range
+  }
+  column "r2" {
+    type = int8range
+  }
+  column "r3" {
+    type = numrange
+  }
+  column "r4" {
+    type = tsrange
+  }
+  column "r5" {
+    type = tstzrange
+  }
+  column "r6" {
+    type = daterange
+  }
+  column "r7" {
+    type = int4multirange
+  }
+  column "r8" {
+    type = int8multirange
+  }
+  column "r9" {
+    type = nummultirange
+  }
+  column "r10" {
+    type = tsmultirange
+  }
+  column "r11" {
+    type = tstzmultirange
+  }
+  column "r12" {
+    type = datemultirange
+  }
+}
+
+-- 1.sql --
+            Table "script_column_range.t"
+ Column |      Type      | Collation | Nullable | Default
+--------+----------------+-----------+----------+---------
+ r1     | int4range      |           | not null |
+ r2     | int8range      |           | not null |
+ r3     | numrange       |           | not null |
+ r4     | tsrange        |           | not null |
+ r5     | tstzrange      |           | not null |
+ r6     | daterange      |           | not null |
+ r7     | int4multirange |           | not null |
+ r8     | int8multirange |           | not null |
+ r9     | nummultirange  |           | not null |
+ r10    | tsmultirange   |           | not null |
+ r11    | tstzmultirange |           | not null |
+ r12    | datemultirange |           | not null |
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 8b27f3e2..57593367 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -82,6 +82,8 @@ func TestDriver_InspectTable(t *testing.T) {
  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
+ users       |  c38         | datemultirange              | datemultirange      | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | m       |         |         | 4535
+ users       |  c39         | numrange                    | numrange            | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | m       |         |         | 4536
 `))
 				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1, $2)`)).
 					WithArgs(16774, 16775).
@@ -140,6 +142,8 @@ func TestDriver_InspectTable(t *testing.T) {
 					{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"}}},
+					{Name: "c38", Type: &schema.ColumnType{Raw: "datemultirange", Type: &RangeType{T: "datemultirange"}}},
+					{Name: "c39", Type: &schema.ColumnType{Raw: "numrange", Type: &RangeType{T: "numrange"}}},
 				}, t.Columns)
 			},
 		},
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 59a930e5..220984c0 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -1363,6 +1363,54 @@ func TestTypes(t *testing.T) {
 			typeExpr: "money",
 			expected: &CurrencyType{T: TypeMoney},
 		},
+		{
+			typeExpr: "int4range",
+			expected: &RangeType{T: TypeInt4Range},
+		},
+		{
+			typeExpr: "int4multirange",
+			expected: &RangeType{T: TypeInt4MultiRange},
+		},
+		{
+			typeExpr: "int8range",
+			expected: &RangeType{T: TypeInt8Range},
+		},
+		{
+			typeExpr: "int8multirange",
+			expected: &RangeType{T: TypeInt8MultiRange},
+		},
+		{
+			typeExpr: "numrange",
+			expected: &RangeType{T: TypeNumRange},
+		},
+		{
+			typeExpr: "nummultirange",
+			expected: &RangeType{T: TypeNumMultiRange},
+		},
+		{
+			typeExpr: "tsrange",
+			expected: &RangeType{T: TypeTSRange},
+		},
+		{
+			typeExpr: "tsmultirange",
+			expected: &RangeType{T: TypeTSMultiRange},
+		},
+		{
+			typeExpr: "tstzrange",
+			expected: &RangeType{T: TypeTSTZRange},
+		},
+		{
+			typeExpr: "tstzmultirange",
+			expected: &RangeType{T: TypeTSTZMultiRange},
+		},
+		{
+			typeExpr: "daterange",
+			expected: &RangeType{T: TypeDateRange},
+		},
+		{
+			typeExpr: "datemultirange",
+			expected: &RangeType{T: TypeDateMultiRange},
+		},
 		{
 			typeExpr: `sql("int[]")`,
 			expected: &ArrayType{Type: &schema.IntegerType{T: "int"}, T: "int[]"},