Fix `pq: invalid input syntax for integer` when trying to get a numeric localpart (#2505)

* Match at least once

* Add the test

2 files changed, 10 insertions, 1 deletions

diff --git a/userapi/storage/postgres/accounts_table.go b/userapi/storage/postgres/accounts_table.go
index f86812f1..268889ac 100644
--- a/userapi/storage/postgres/accounts_table.go
+++ b/userapi/storage/postgres/accounts_table.go
@@ -65,7 +65,7 @@ const selectPasswordHashSQL = "" +
 	"SELECT password_hash FROM account_accounts WHERE localpart = $1 AND is_deactivated = FALSE"
 
 const selectNewNumericLocalpartSQL = "" +
-	"SELECT COALESCE(MAX(localpart::integer), 0) FROM account_accounts WHERE localpart ~ '^[0-9]*$'"
+	"SELECT COALESCE(MAX(localpart::integer), 0) FROM account_accounts WHERE localpart ~ '^[0-9]{1,}$'"
 
 type accountsStatements struct {
 	insertAccountStmt             *sql.Stmt
diff --git a/userapi/storage/storage_test.go b/userapi/storage/storage_test.go
index 5bee880d..66c15454 100644
--- a/userapi/storage/storage_test.go
+++ b/userapi/storage/storage_test.go
@@ -124,6 +124,15 @@ func Test_Accounts(t *testing.T) {
 
 		_, err = db.GetAccountByLocalpart(ctx, "unusename")
 		assert.Error(t, err, "expected an error for non existent localpart")
+
+		// create an empty localpart; this should never happen, but is required to test getting a numeric localpart
+		// if there's already a user without a localpart in the database
+		_, err = db.CreateAccount(ctx, "", "", "", api.AccountTypeUser)
+		assert.NoError(t, err)
+
+		// test getting a numeric localpart, with an existing user without a localpart
+		_, err = db.CreateAccount(ctx, "", "", "", api.AccountTypeGuest)
+		assert.NoError(t, err)
 	})
 }