Repository: schemahero/schemahero
Pull request #1191 (2025-07-15 01:11:11)

fix timescaledb interval parameter
Updates the create parameter for Timescaledb to be able to set the chunk_time_interval.

xavpaice: Updated to add integration test coverage, and resolve another issue with the retention policy.

Logs from testing:
```
{"level":"debug","msg":"checking migration[{name 15 0 f588bcd <nil>} {tableName 15 0 flight-status3 <nil>}]"}
Executing query "create table \"flight_status3\" (\"time\" timestamp without time zone not null, \"flight_number\" character varying (40), \"status\" character varying (40));"
Executing query "select create_hypertable('flight_status3', 'time', chunk_time_interval => INTERVAL '1 day');"
Executing query "select add_retention_policy('flight_status3', interval '7 days')"
{"level":"debug","msg":"checking migration[{name 15 0 f588bcd <nil>} {tableName 15 0 flight-status3 <nil>}]"}
```

Patch:
diff --git a/examples/timescaledb/tables/flight-status.yaml b/examples/timescaledb/tables/flight-status.yaml
index cb07af8b..9ffed997 100644
--- a/examples/timescaledb/tables/flight-status.yaml
+++ b/examples/timescaledb/tables/flight-status.yaml
@@ -9,9 +9,14 @@ spec:
     timescaledb:
       hypertable:
         timeColumnName: time
+        chunkTimeInterval: "1 day"
+        retention:
+          interval: "30 days"
       columns:
         - name: time
           type: timestamp without time zone
+          constraints:
+            notNull: true
         - name: flight_number
           type: varchar (40)
         - name: status
diff --git a/pkg/database/timescaledb/create.go b/pkg/database/timescaledb/create.go
index d1e532f4..507e915e 100644
--- a/pkg/database/timescaledb/create.go
+++ b/pkg/database/timescaledb/create.go
@@ -2,6 +2,7 @@ package timescaledb
 
 import (
 	"fmt"
+	"strings"
 
 	"github.com/jackc/pgx/v4"
 	"github.com/pkg/errors"
@@ -86,7 +87,7 @@ func createRetentionStatements(tableName string, hypertable *schemasv1alpha4.Tim
 				return nil, errors.New("invalid interval")
 			}
 
-			stmt := fmt.Sprintf(`select add_retention_policy(%s, interval '%s')`, pgx.Identifier{tableName}.Sanitize(), hypertable.Retention.Interval)
+			stmt := fmt.Sprintf(`select add_retention_policy(%s, interval '%s')`, strings.ReplaceAll(pgx.Identifier{tableName}.Sanitize(), "\"", "'"), hypertable.Retention.Interval)
 			stmts = append(stmts, stmt)
 		}
 	}
@@ -115,7 +116,7 @@ func createCompressionStatements(tableName string, hypertable *schemasv1alpha4.T
 			return nil, errors.New("invalid interval")
 		}
 
-		stmt := fmt.Sprintf(`select add_compression_policy(%s, INTERVAL '%s')`, pgx.Identifier{tableName}.Sanitize(), *hypertable.Compression.Interval)
+		stmt := fmt.Sprintf(`select add_compression_policy(%s, INTERVAL '%s')`, strings.ReplaceAll(pgx.Identifier{tableName}.Sanitize(), "\"", "'"), *hypertable.Compression.Interval)
 		stmts = append(stmts, stmt)
 	}
 
@@ -180,7 +181,7 @@ func getHypertableParams(hypertable *schemasv1alpha4.TimescaleDBHypertable, colu
 			return nil, errors.New("invalid chunk time interval")
 		}
 
-		params = append(params, fmt.Sprintf("chunk_time_interval => '%s'", *hypertable.ChunkTimeInterval))
+		params = append(params, fmt.Sprintf("chunk_time_interval => INTERVAL '%s'", *hypertable.ChunkTimeInterval))
 	}
 
 	if hypertable.CreateDefaultIndexes != nil {


Test patch:
diff --git a/integration/tests/timescaledb/create-table/expect.sql b/integration/tests/timescaledb/create-table/expect.sql
index 2fe98934..ae5e0cc3 100644
--- a/integration/tests/timescaledb/create-table/expect.sql
+++ b/integration/tests/timescaledb/create-table/expect.sql
@@ -1,4 +1,5 @@
 insert into other (id, something) values (1, 'one') on conflict ("id") do update set (id, something) = (excluded.id, excluded.something);
 insert into other (id, something) values (2, 'two') on conflict ("id") do update set (id, something) = (excluded.id, excluded.something);
 create table "users" ("created_at" timestamp without time zone not null, "id" integer, "login" character varying (255), "name" character varying (255) not null default 'ethan', "tz_1" timestamp, "tz_2" timestamp with time zone, "tz_3" timestamp without time zone, primary key ("created_at", "id"));
-select create_hypertable('users', 'created_at');
+select create_hypertable('users', 'created_at', chunk_time_interval => INTERVAL '1 day');
+select add_retention_policy('users', interval '30 days');
diff --git a/integration/tests/timescaledb/create-table/specs/users.yaml b/integration/tests/timescaledb/create-table/specs/users.yaml
index 29f3efc1..213f326d 100644
--- a/integration/tests/timescaledb/create-table/specs/users.yaml
+++ b/integration/tests/timescaledb/create-table/specs/users.yaml
@@ -5,6 +5,9 @@ schema:
   timescaledb:
     hypertable:
       timeColumnName: created_at
+      chunkTimeInterval: "1 day"
+      retention:
+        interval: "30 days"
     primaryKey:
       - created_at
       - id
diff --git a/pkg/database/timescaledb/create_test.go b/pkg/database/timescaledb/create_test.go
index 9eca4254..a23497cc 100644
--- a/pkg/database/timescaledb/create_test.go
+++ b/pkg/database/timescaledb/create_test.go
@@ -88,7 +88,7 @@ func Test_createHypertableStatement(t *testing.T) {
 					Type: "timestamptz",
 				},
 			},
-			want: `select create_hypertable('table1', 'time', chunk_time_interval => '7 days')`,
+			want: `select create_hypertable('table1', 'time', chunk_time_interval => INTERVAL '7 days')`,
 		},
 		{
 			name:      "create default indexes",