Repository: ariga/atlas
Pull request #882 (2022-06-20 13:53:48)

sql/postgres: support inspecting interval types


Patch:
diff --git a/sql/postgres/convert.go b/sql/postgres/convert.go
index 8f1e5b3f..aaa63088 100644
--- a/sql/postgres/convert.go
+++ b/sql/postgres/convert.go
@@ -51,6 +51,14 @@ func FormatType(t schema.Type) (string, error) {
 		case TypeInt8:
 			f = TypeBigInt
 		}
+	case *IntervalType:
+		f = strings.ToLower(t.T)
+		if t.F != "" {
+			f += " " + strings.ToLower(t.F)
+		}
+		if t.Precision != nil && *t.Precision != defaultTimePrecision {
+			f += fmt.Sprintf("(%d)", *t.Precision)
+		}
 	case *schema.StringType:
 		switch f = strings.ToLower(t.T); f {
 		case TypeText:
@@ -169,6 +177,19 @@ func arrayType(t string) (string, bool) {
 	return matches[1], true
 }
 
+// reInterval parses declaration of interval fields. See: https://www.postgresql.org/docs/current/datatype-datetime.html.
+var reInterval = regexp.MustCompile(`(?i)(?:INTERVAL\s*)?(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|YEAR TO MONTH|DAY TO HOUR|DAY TO MINUTE|DAY TO SECOND|HOUR TO MINUTE|HOUR TO SECOND|MINUTE TO SECOND)?\s*(?:\(([0-6])\))?$`)
+
+// intervalField reports if the given string is an interval
+// field type and returns its value (e.g. SECOND, MINUTE TO SECOND).
+func intervalField(t string) (string, bool) {
+	matches := reInterval.FindStringSubmatch(t)
+	if len(matches) != 3 || matches[1] == "" {
+		return "", false
+	}
+	return matches[1], true
+}
+
 // columnDesc represents a column descriptor.
 type columnDesc struct {
 	typ           string
@@ -180,6 +201,7 @@ type columnDesc struct {
 	typtype       string
 	typid         int64
 	parts         []string
+	interval      string
 }
 
 var reDigits = regexp.MustCompile(`\d`)
@@ -236,6 +258,16 @@ func parseColumn(s string) (*columnDesc, error) {
 		}
 		c.typ = timeAlias(t)
 		c.timePrecision = &p
+	case TypeInterval:
+		matches := reInterval.FindStringSubmatch(s)
+		c.interval = matches[1]
+		if matches[2] != "" {
+			i, err := strconv.ParseInt(matches[2], 10, 64)
+			if err != nil {
+				return nil, fmt.Errorf("postgres: parse interval precision %q: %w", parts[1], err)
+			}
+			c.timePrecision = &i
+		}
 	default:
 		c.typ = s
 	}
diff --git a/sql/postgres/crdb.go b/sql/postgres/crdb.go
index 0d78fd84..4bac89b2 100644
--- a/sql/postgres/crdb.go
+++ b/sql/postgres/crdb.go
@@ -312,6 +312,7 @@ SELECT
 	t1.numeric_precision,
 	t1.datetime_precision,
 	t1.numeric_scale,
+	t1.interval_type,
 	t1.character_set_name,
 	t1.collation_name,
 	t1.udt_name,
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index e9d8ade2..3eaa43d7 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -165,11 +165,11 @@ 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, seqlast                                     sql.NullInt64
-		table, name, typ, nullable, defaults, udt, identity, genidentity, genexpr, charset, collate, comment, typtype sql.NullString
+		typid, maxlen, precision, timeprecision, scale, seqstart, seqinc, seqlast                                               sql.NullInt64
+		table, name, typ, nullable, defaults, udt, identity, genidentity, genexpr, charset, collate, comment, typtype, interval sql.NullString
 	)
 	if err := rows.Scan(
-		&table, &name, &typ, &nullable, &defaults, &maxlen, &precision, &timeprecision, &scale, &charset,
+		&table, &name, &typ, &nullable, &defaults, &maxlen, &precision, &timeprecision, &scale, &interval, &charset,
 		&collate, &udt, &identity, &seqstart, &seqinc, &seqlast, &genidentity, &genexpr, &comment, &typtype, &typid,
 	); err != nil {
 		return err
@@ -192,6 +192,7 @@ func (i *inspect) addColumn(s *schema.Schema, rows *sql.Rows) error {
 		scale:         scale.Int64,
 		typtype:       typtype.String,
 		typid:         typid.Int64,
+		interval:      interval.String,
 		precision:     precision.Int64,
 		timePrecision: &timeprecision.Int64,
 	})
@@ -255,8 +256,18 @@ func columnType(c *columnDesc) schema.Type {
 		}
 		typ = &schema.TimeType{T: t, Precision: &p}
 	case TypeInterval:
-		// TODO: get 'interval_type' from query above before implementing.
-		typ = &schema.UnsupportedType{T: t}
+		p := defaultTimePrecision
+		if c.timePrecision != nil {
+			p = int(*c.timePrecision)
+		}
+		typ = &IntervalType{T: t, Precision: &p}
+		if c.interval != "" {
+			f, ok := intervalField(c.interval)
+			if !ok {
+				return &schema.UnsupportedType{T: c.interval}
+			}
+			typ.(*IntervalType).F = f
+		}
 	case TypeReal, TypeDouble, TypeFloat4, TypeFloat8:
 		typ = &schema.FloatType{T: t, Precision: int(c.precision)}
 	case TypeJSON, TypeJSONB:
@@ -674,6 +685,15 @@ type (
 		Len int64
 	}
 
+	// IntervalType defines an interval type.
+	// https://www.postgresql.org/docs/current/datatype-datetime.html
+	IntervalType struct {
+		schema.Type
+		T         string // Type name.
+		F         string // Optional field. YEAR, MONTH, ..., MINUTE TO SECOND.
+		Precision *int   // Optional precision.
+	}
+
 	// A NetworkType defines a network type.
 	// https://www.postgresql.org/docs/current/datatype-net-types.html
 	NetworkType struct {
@@ -895,6 +915,7 @@ SELECT
 	t1.numeric_precision,
 	t1.datetime_precision,
 	t1.numeric_scale,
+	t1.interval_type,
 	t1.character_set_name,
 	t1.collation_name,
 	t1.udt_name,


Test patch:
diff --git a/internal/integration/cockroach_test.go b/internal/integration/cockroach_test.go
index 3bc38042..cf019d63 100644
--- a/internal/integration/cockroach_test.go
+++ b/internal/integration/cockroach_test.go
@@ -808,7 +808,7 @@ create table atlas_types_sanity
 				},
 				{
 					Name: "tInterval",
-					Type: &schema.ColumnType{Type: &schema.UnsupportedType{T: "interval"}, Raw: "interval", Null: true},
+					Type: &schema.ColumnType{Type: &postgres.IntervalType{T: "interval", Precision: intp(6)}, Raw: "interval", Null: true},
 					Default: &schema.RawExpr{
 						X: "'04:00:00':::INTERVAL",
 					},
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index ef4b2958..03337739 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -1059,7 +1059,7 @@ create table atlas_types_sanity
 				},
 				{
 					Name: "tInterval",
-					Type: &schema.ColumnType{Type: &schema.UnsupportedType{T: "interval"}, Raw: "interval", Null: true},
+					Type: &schema.ColumnType{Type: &postgres.IntervalType{T: "interval", Precision: intp(6)}, Raw: "interval", Null: true},
 					Default: &schema.RawExpr{
 						X: "'04:00:00'::interval",
 					},
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 0afff12a..ab10857b 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -41,38 +41,41 @@ 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_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
+ table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale |    interval_type    | 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
+ users       |  c29         | interval                    | NO          |                                 |                          |                   |                  6 |               |                     |                    |                | interval    | NO          |                |                    |                  |                     |                       |         | b       |  1268
+ users       |  c30         | interval                    | NO          |                                 |                          |                   |                  6 |               |        MONTH        |                    |                | interval    | NO          |                |                    |                  |                     |                       |         | b       |  1269
+ users       |  c31         | interval                    | NO          |                                 |                          |                   |                  6 |               | MINUTE TO SECOND(6) |                    |                | interval    | NO          |                |                    |                  |                     |                       |         | b       |  1233
 `))
 				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1)`)).
 					WithArgs(16774).
@@ -121,6 +124,9 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c26", Type: &schema.ColumnType{Raw: "timestamp with time zone", Type: &schema.TimeType{T: "timestamp with time zone", Precision: p(6)}}},
 					{Name: "c27", Type: &schema.ColumnType{Raw: "time without time zone", Type: &schema.TimeType{T: "time without time zone", Precision: p(6)}}},
 					{Name: "c28", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&schema.GeneratedExpr{Expr: "(c1 + c2)"}}},
+					{Name: "c29", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", Precision: p(6)}}},
+					{Name: "c30", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MONTH", Precision: p(6)}}},
+					{Name: "c31", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MINUTE TO SECOND", Precision: p(6)}}},
 				}, t.Columns)
 			},
 		},
@@ -131,11 +137,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_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
+table_name | column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | interval_type | 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 +198,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_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
+table_name | column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | interval_type | 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 +245,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_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
+table_name |column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | interval_type | 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 +338,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_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
+table_name |column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | interval_type | 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 +406,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_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 
+table_name  | column_name | data_type | is_nullable |              column_default               | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | interval_type | 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/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index f19c9967..b56b75b0 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -6,6 +6,7 @@ package postgres
 
 import (
 	"fmt"
+	"strconv"
 	"testing"
 
 	"ariga.io/atlas/sql/internal/spectest"
@@ -1114,6 +1115,83 @@ func TestParseType_Time(t *testing.T) {
 	}
 }
 
+func TestFormatType_Interval(t *testing.T) {
+	p := func(i int) *int { return &i }
+	for i, tt := range []struct {
+		typ *IntervalType
+		fmt string
+	}{
+		{
+			typ: &IntervalType{T: "interval"},
+			fmt: "interval",
+		},
+		{
+			typ: &IntervalType{T: "interval", Precision: p(6)},
+			fmt: "interval",
+		},
+		{
+			typ: &IntervalType{T: "interval", Precision: p(3)},
+			fmt: "interval(3)",
+		},
+		{
+			typ: &IntervalType{T: "interval", F: "DAY"},
+			fmt: "interval day",
+		},
+		{
+			typ: &IntervalType{T: "interval", F: "HOUR TO SECOND"},
+			fmt: "interval hour to second",
+		},
+		{
+			typ: &IntervalType{T: "interval", F: "HOUR TO SECOND", Precision: p(2)},
+			fmt: "interval hour to second(2)",
+		},
+		{
+			typ: &IntervalType{T: "interval", F: "DAY TO HOUR", Precision: p(6)},
+			fmt: "interval day to hour",
+		},
+	} {
+		t.Run(strconv.Itoa(i), func(t *testing.T) {
+			f, err := FormatType(tt.typ)
+			require.NoError(t, err)
+			require.Equal(t, tt.fmt, f)
+		})
+	}
+}
+func TestParseType_Interval(t *testing.T) {
+	p := func(i int) *int { return &i }
+	for i, tt := range []struct {
+		typ    string
+		parsed *IntervalType
+	}{
+		{
+			typ:    "interval",
+			parsed: &IntervalType{T: "interval", Precision: p(6)},
+		},
+		{
+			typ:    "interval(2)",
+			parsed: &IntervalType{T: "interval", Precision: p(2)},
+		},
+		{
+			typ:    "interval day",
+			parsed: &IntervalType{T: "interval", F: "day", Precision: p(6)},
+		},
+		{
+			typ:    "interval day to second(2)",
+			parsed: &IntervalType{T: "interval", F: "day to second", Precision: p(2)},
+		},
+		{
+			typ:    "interval day to second (2)",
+			parsed: &IntervalType{T: "interval", F: "day to second", Precision: p(2)},
+		},
+	} {
+		t.Run(strconv.Itoa(i), func(t *testing.T) {
+			p, err := ParseType(tt.typ)
+			require.NoError(t, err)
+			require.Equal(t, tt.parsed, p)
+		})
+	}
+}
+
 func TestRegistrySanity(t *testing.T) {
 	spectest.RegistrySanityTest(t, TypeRegistry, []string{"enum"})
 }