Repository: ariga/atlas
Pull request #476 (2022-01-24 15:04:18)

sql/postgres: inspect: support fractional seconds precision in timest…
…amp type


Patch:
diff --git a/sql/postgres/convert.go b/sql/postgres/convert.go
index be045698..5e3fba04 100644
--- a/sql/postgres/convert.go
+++ b/sql/postgres/convert.go
@@ -184,14 +184,15 @@ func arrayType(t string) (string, bool) {
 
 // columnDesc represents a column descriptor.
 type columnDesc struct {
-	typ       string
-	size      int64
-	udt       string
-	precision int64
-	scale     int64
-	typtype   string
-	typid     int64
-	parts     []string
+	typ           string
+	size          int64
+	udt           string
+	precision     int64
+	timePrecision int64
+	scale         int64
+	typtype       string
+	typid         int64
+	parts         []string
 }
 
 func parseColumn(s string) (*columnDesc, error) {
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 255896a8..94655bd1 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -173,10 +173,13 @@ func (i *inspect) columns(ctx context.Context, t *schema.Table) error {
 // addColumn scans the current row and adds a new column from it to the table.
 func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
 	var (
-		typid, maxlen, precision, scale, seqstart, seqinc                                              sql.NullInt64
+		typid, maxlen, precision, timeprecision, scale, seqstart, seqinc                               sql.NullInt64
 		name, typ, nullable, defaults, udt, identity, generation, charset, collation, comment, typtype sql.NullString
 	)
-	if err := rows.Scan(&name, &typ, &nullable, &defaults, &maxlen, &precision, &scale, &charset, &collation, &udt, &identity, &seqstart, &seqinc, &generation, &comment, &typtype, &typid); err != nil {
+	if err := rows.Scan(
+		&name, &typ, &nullable, &defaults, &maxlen, &precision, &timeprecision, &scale, &charset,
+		&collation, &udt, &identity, &seqstart, &seqinc, &generation, &comment, &typtype, &typid,
+	); err != nil {
 		return err
 	}
 	c := &schema.Column{
@@ -187,13 +190,14 @@ func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
 		},
 	}
 	c.Type.Type = columnType(&columnDesc{
-		typ:       typ.String,
-		size:      maxlen.Int64,
-		udt:       udt.String,
-		precision: precision.Int64,
-		scale:     scale.Int64,
-		typtype:   typtype.String,
-		typid:     typid.Int64,
+		typ:           typ.String,
+		size:          maxlen.Int64,
+		udt:           udt.String,
+		precision:     precision.Int64,
+		timePrecision: timeprecision.Int64,
+		scale:         scale.Int64,
+		typtype:       typtype.String,
+		typid:         typid.Int64,
 	})
 	if sqlx.ValidString(defaults) {
 		c.Default = defaultExpr(c, defaults.String)
@@ -247,7 +251,7 @@ func columnType(c *columnDesc) schema.Type {
 		typ = &schema.SpatialType{T: t}
 	case TypeDate, TypeTime, TypeTimeWTZ, TypeTimeWOTZ,
 		TypeTimestamp, TypeTimestampTZ, TypeTimestampWTZ, TypeTimestampWOTZ:
-		typ = &schema.TimeType{T: t}
+		typ = &schema.TimeType{T: t, Precision: int(c.timePrecision)}
 	case TypeInterval:
 		// TODO: get 'interval_type' from query above before implementing.
 		typ = &schema.UnsupportedType{T: t}
@@ -755,6 +759,7 @@ SELECT
 	t1.column_default,
 	t1.character_maximum_length,
 	t1.numeric_precision,
+	t1.datetime_precision,
 	t1.numeric_scale,
 	t1.character_set_name,
 	t1.collation_name,


Test patch:
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index c833ec29..f9fa5013 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -546,6 +546,7 @@ create table atlas_types_sanity
     "tTimestampTZ"         timestamptz                 default now()                                    null,
     "tTimestampWTZ"        timestamp with time zone    default now()                                    null,
     "tTimestampWOTZ"       timestamp without time zone default now()                                    null,
+    "tTimestampPrec"       timestamp(4)                default now()                                    null,
     "tDouble"              double precision            default 0                                        null,
     "tReal"                real                        default 0                                        null,
     "tFloat8"              float8                      default 0                                        null,
@@ -716,37 +717,42 @@ create table atlas_types_sanity
 				},
 				{
 					Name:    "tTime",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "time without time zone"}, Raw: "time without time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "time without time zone", Precision: 6}, Raw: "time without time zone", Null: true},
 					Default: &schema.RawExpr{X: "CURRENT_TIME"},
 				},
 				{
 					Name:    "tTimeWTZ",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "time with time zone"}, Raw: "time with time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "time with time zone", Precision: 6}, Raw: "time with time zone", Null: true},
 					Default: &schema.RawExpr{X: "CURRENT_TIME"},
 				},
 				{
 					Name:    "tTimeWOTZ",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "time without time zone"}, Raw: "time without time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "time without time zone", Precision: 6}, Raw: "time without time zone", Null: true},
 					Default: &schema.RawExpr{X: "CURRENT_TIME"},
 				},
 				{
 					Name:    "tTimestamp",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp without time zone"}, Raw: "timestamp without time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp without time zone", Precision: 6}, Raw: "timestamp without time zone", Null: true},
 					Default: &schema.RawExpr{X: "now()"},
 				},
 				{
 					Name:    "tTimestampTZ",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp with time zone"}, Raw: "timestamp with time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp with time zone", Precision: 6}, Raw: "timestamp with time zone", Null: true},
 					Default: &schema.RawExpr{X: "now()"},
 				},
 				{
 					Name:    "tTimestampWTZ",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp with time zone"}, Raw: "timestamp with time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp with time zone", Precision: 6}, Raw: "timestamp with time zone", Null: true},
 					Default: &schema.RawExpr{X: "now()"},
 				},
 				{
 					Name:    "tTimestampWOTZ",
-					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp without time zone"}, Raw: "timestamp without time zone", Null: true},
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp without time zone", Precision: 6}, Raw: "timestamp without time zone", Null: true},
+					Default: &schema.RawExpr{X: "now()"},
+				},
+				{
+					Name:    "tTimestampPrec",
+					Type:    &schema.ColumnType{Type: &schema.TimeType{T: "timestamp without time zone", Precision: 4}, Raw: "timestamp without time zone", Null: true},
 					Default: &schema.RawExpr{X: "now()"},
 				},
 				{
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 641f99be..b76e1063 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -58,34 +58,37 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(sqltest.Escape(columnsQuery)).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype |  oid  
--------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-------
- id          | bigint              | NO          |                                 |                          |                64 |             0 |                    |                | int8     | YES         |      100       |          1         |    BY DEFAULT       |         | b       |    20
- rank        | integer             | YES         |                                 |                          |                32 |             0 |                    |                | int4     | NO          |                |                    |                     | rank    | b       |    23
- c1          | smallint            | NO          |           1000                  |                          |                16 |             0 |                    |                | int2     | NO          |                |                    |                     |         | b       |    21
- c2          | bit                 | NO          |                                 |                        1 |                   |               |                    |                | bit      | NO          |                |                    |                     |         | b       |  1560
- c3          | bit varying         | NO          |                                 |                       10 |                   |               |                    |                | varbit   | NO          |                |                    |                     |         | b       |  1562
- c4          | boolean             | NO          |                                 |                          |                   |               |                    |                | bool     | NO          |                |                    |                     |         | b       |    16
- c5          | bytea               | NO          |                                 |                          |                   |               |                    |                | bytea    | NO          |                |                    |                     |         | b       |    17
- c6          | character           | NO          |                                 |                      100 |                   |               |                    |                | bpchar   | NO          |                |                    |                     |         | b       |  1042
- c7          | character varying   | NO          |                                 |                          |                   |               |                    |                | varchar  | NO          |                |                    |                     |         | b       |  1043
- c8          | cidr                | NO          |                                 |                          |                   |               |                    |                | cidr     | NO          |                |                    |                     |         | b       |   650
- c9          | circle              | NO          |                                 |                          |                   |               |                    |                | circle   | NO          |                |                    |                     |         | b       |   718
- c10         | date                | NO          |                                 |                          |                   |               |                    |                | date     | NO          |                |                    |                     |         | b       |  1082
- c11         | time with time zone | NO          |                                 |                          |                   |               |                    |                | timetz   | NO          |                |                    |                     |         | b       |  1266
- c12         | double precision    | NO          |                                 |                          |                53 |               |                    |                | float8   | NO          |                |                    |                     |         | b       |   701
- c13         | real                | NO          |           random()              |                          |                24 |               |                    |                | float4   | NO          |                |                    |                     |         | b       |   700
- c14         | json                | NO          |           '{}'::json            |                          |                   |               |                    |                | json     | NO          |                |                    |                     |         | b       |   114
- c15         | jsonb               | NO          |           '{}'::jsonb           |                          |                   |               |                    |                | jsonb    | NO          |                |                    |                     |         | b       |  3802
- c16         | money               | NO          |                                 |                          |                   |               |                    |                | money    | NO          |                |                    |                     |         | b       |   790
- c17         | numeric             | NO          |                                 |                          |                   |               |                    |                | numeric  | NO          |                |                    |                     |         | b       |  1700
- c18         | numeric             | NO          |                                 |                          |                 4 |             4 |                    |                | numeric  | NO          |                |                    |                     |         | b       |  1700
- c19         | integer             | NO          | nextval('t1_c19_seq'::regclass) |                          |                32 |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |    23
- c20         | uuid                | NO          |                                 |                          |                   |               |                    |                | uuid     | NO          |                |                    |                     |         | b       |  2950
- c21         | xml                 | NO          |                                 |                          |                   |               |                    |                | xml      | NO          |                |                    |                     |         | b       |   142
- c22         | ARRAY               | YES         |                                 |                          |                   |               |                    |                | _int4    | NO          |                |                    |                     |         | b       |  1007
- c23         | USER-DEFINED        | YES         |                                 |                          |                   |               |                    |                | ltree    | NO          |                |                    |                     |         | b       | 16535
- c24         | USER-DEFINED        | NO          |                                 |                          |                   |               |                    |                | state    | NO          |                |                    |                     |         | e       | 16774
+ column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment | identity_generation | comment | typtype |  oid  
+-------------+-----------------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+-------------+-------------+----------------+--------------------+---------------------+---------+---------+-------
+ id          | bigint                      | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8        | YES         |      100       |          1         |    BY DEFAULT       |         | b       |    20
+ rank        | integer                     | YES         |                                 |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                     | rank    | b       |    23
+ c1          | smallint                    | NO          |           1000                  |                          |                16 |                    |             0 |                    |                | int2        | NO          |                |                    |                     |         | b       |    21
+ c2          | bit                         | NO          |                                 |                        1 |                   |                    |               |                    |                | bit         | NO          |                |                    |                     |         | b       |  1560
+ c3          | bit varying                 | NO          |                                 |                       10 |                   |                    |               |                    |                | varbit      | NO          |                |                    |                     |         | b       |  1562
+ c4          | boolean                     | NO          |                                 |                          |                   |                    |               |                    |                | bool        | NO          |                |                    |                     |         | b       |    16
+ c5          | bytea                       | NO          |                                 |                          |                   |                    |               |                    |                | bytea       | NO          |                |                    |                     |         | b       |    17
+ c6          | character                   | NO          |                                 |                      100 |                   |                    |               |                    |                | bpchar      | NO          |                |                    |                     |         | b       |  1042
+ c7          | character varying           | NO          |                                 |                          |                   |                    |               |                    |                | varchar     | NO          |                |                    |                     |         | b       |  1043
+ c8          | cidr                        | NO          |                                 |                          |                   |                    |               |                    |                | cidr        | NO          |                |                    |                     |         | b       |   650
+ c9          | circle                      | NO          |                                 |                          |                   |                    |               |                    |                | circle      | NO          |                |                    |                     |         | b       |   718
+ c10         | date                        | NO          |                                 |                          |                   |                    |               |                    |                | date        | NO          |                |                    |                     |         | b       |  1082
+ c11         | time with time zone         | NO          |                                 |                          |                   |                    |               |                    |                | timetz      | NO          |                |                    |                     |         | b       |  1266
+ c12         | double precision            | NO          |                                 |                          |                53 |                    |               |                    |                | float8      | NO          |                |                    |                     |         | b       |   701
+ c13         | real                        | NO          |           random()              |                          |                24 |                    |               |                    |                | float4      | NO          |                |                    |                     |         | b       |   700
+ c14         | json                        | NO          |           '{}'::json            |                          |                   |                    |               |                    |                | json        | NO          |                |                    |                     |         | b       |   114
+ c15         | jsonb                       | NO          |           '{}'::jsonb           |                          |                   |                    |               |                    |                | jsonb       | NO          |                |                    |                     |         | b       |  3802
+ c16         | money                       | NO          |                                 |                          |                   |                    |               |                    |                | money       | NO          |                |                    |                     |         | b       |   790
+ c17         | numeric                     | NO          |                                 |                          |                   |                    |               |                    |                | numeric     | NO          |                |                    |                     |         | b       |  1700
+ c18         | numeric                     | NO          |                                 |                          |                 4 |                    |             4 |                    |                | numeric     | NO          |                |                    |                     |         | b       |  1700
+ c19         | integer                     | NO          | nextval('t1_c19_seq'::regclass) |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                     |         | b       |    23
+ c20         | uuid                        | NO          |                                 |                          |                   |                    |               |                    |                | uuid        | NO          |                |                    |                     |         | b       |  2950
+ c21         | xml                         | NO          |                                 |                          |                   |                    |               |                    |                | xml         | NO          |                |                    |                     |         | b       |   142
+ c22         | ARRAY                       | YES         |                                 |                          |                   |                    |               |                    |                | _int4       | NO          |                |                    |                     |         | b       |  1007
+ c23         | USER-DEFINED                | YES         |                                 |                          |                   |                    |               |                    |                | ltree       | NO          |                |                    |                     |         | b       | 16535
+ c24         | USER-DEFINED                | NO          |                                 |                          |                   |                    |               |                    |                | state       | NO          |                |                    |                     |         | e       | 16774
+ c25         | timestamp without time zone | NO          |            now()                |                          |                   |                  4 |               |                    |                | timestamp   | NO          |                |                    |                     |         | b       |  1114
+ c26         | timestamp with time zone    | NO          |                                 |                          |                   |                  6 |               |                    |                | timestamptz | NO          |                |                    |                     |         | b       |  1184
+ c27         | time without time zone      | NO          |                                 |                          |                   |                  6 |               |                    |                | time        | NO          |                |                    |                     |         | b       |  1266
 `))
 				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1)`)).
 					WithArgs(16774).
@@ -129,6 +132,9 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c22", Type: &schema.ColumnType{Raw: "ARRAY", Null: true, Type: &ArrayType{T: "int4[]"}}},
 					{Name: "c23", Type: &schema.ColumnType{Raw: "USER-DEFINED", Null: true, Type: &UserDefinedType{T: "ltree"}}},
 					{Name: "c24", Type: &schema.ColumnType{Raw: "state", Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}}}},
+					{Name: "c25", Type: &schema.ColumnType{Raw: "timestamp without time zone", Type: &schema.TimeType{T: "timestamp without time zone", Precision: 4}}, Default: &schema.RawExpr{X: "now()"}},
+					{Name: "c26", Type: &schema.ColumnType{Raw: "timestamp with time zone", Type: &schema.TimeType{T: "timestamp with time zone", Precision: 6}}},
+					{Name: "c27", Type: &schema.ColumnType{Raw: "time without time zone", Type: &schema.TimeType{T: "time without time zone", Precision: 6}}},
 				}, t.Columns)
 			},
 		},
@@ -140,10 +146,10 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(sqltest.Escape(columnsQuery)).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype |  oid
--------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-------
- id          | bigint              | NO          |                                 |                          |                64 |             0 |                    |                | int8     | NO          |                |                    |                     |         | b       |    20
- c1          | smallint            | NO          |                                 |                          |                16 |             0 |                    |                | int2     | NO          |                |                    |                     |         | b       |    21
+ column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype |  oid
+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-------
+ id          | bigint              | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |         | b       |    20
+ c1          | smallint            | NO          |                                 |                          |                16 |                    |             0 |                    |                | int2     | NO          |                |                    |                     |         | b       |    21
 `))
 				m.ExpectQuery(sqltest.Escape(indexesQuery)).
 					WithArgs("public", "users").
@@ -197,11 +203,11 @@ func TestDriver_InspectTable(t *testing.T) {
 					WithArgs("public", "users").
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype |  oid
--------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-------
- id          | integer             | NO          |                                 |                          |                32 |             0 |                    |                | int      | NO          |                |                    |                     |         | b       |    20
- oid         | integer             | NO          |                                 |                          |                32 |             0 |                    |                | int      | NO          |                |                    |                     |         | b       |    21
- uid         | integer             | NO          |                                 |                          |                32 |             0 |                    |                | int      | NO          |                |                    |                     |         | b       |    21
+ column_name |      data_type      | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype |  oid
+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-------
+ id          | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                     |         | b       |    20
+ oid         | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                     |         | b       |    21
+ uid         | integer             | NO          |                                 |                          |                32 |                    |             0 |                    |                | int      | NO          |                |                    |                     |         | b       |    21
 `))
 				m.noIndexes()
 				m.ExpectQuery(sqltest.Escape(fksQuery)).
@@ -247,11 +253,11 @@ func TestDriver_InspectTable(t *testing.T) {
 					WithArgs("public", "users").
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype | oid
--------------+-----------+-------------+----------------+--------------------------+-------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-----
- c1          | integer   | NO          |                |                          |                32 |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |  23
- c2          | integer   | NO          |                |                          |                32 |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |  23
- c3          | integer   | NO          |                |                          |                32 |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |  23
+ column_name | data_type | is_nullable | column_default | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name | udt_name | is_identity | identity_start | identity_increment | identity_generation | comment | typtype | oid
+-------------+-----------+-------------+----------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+---------+---------+-----
+ c1          | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |  23
+ c2          | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |  23
+ c3          | integer   | NO          |                |                          |                32 |                    |             0 |                    |                | int4     | NO          |                |                    |                     |         | b       |  23
 `))
 				m.noIndexes()
 				m.noFKs()