Repository navigation
Type casting not supported in SQLite #3574
Description
Version
1.27.0
What happened?
I want to type-cast some query variables but it doesn't compile
Relevant log output
❯ sqlc generate
line 15:8 no viable alternative at input ';'
line 23:14 extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
# package store
database/query.sql:1:1: extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}Database schema
CREATE TABLE IF NOT EXISTS notes (
id text PRIMARY KEY,
slug text NOT NULL CHECK (length(slug) > 0 AND length(slug) < 100),
content text NOT NULL CHECK (length(content) < 10000),
user text NOT NULL,
public BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE(slug, user)
);SQL queries
SELECT * FROM notes WHERE user = @author
AND (public = true OR user = @user)
ORDER BY created_at DESC
LIMIT coalesce(sqlc.narg('limit')::int, 50);Configuration
version: "2"
sql:
- engine: "sqlite"
schema: "database/schema.sql"
queries: "database/query.sql"
gen:
go:
package: "store"
out: "store"Playground URL
Remove the ::int and it works
What operating system are you using?
macOS
What database engines are you using?
SQLite
What type of code are you generating?
Go
Reacted by Kirill, Bayan, Nintron and Daniel
Activity
ciricc commentedon Sep 4, 2024
Same problem
waterfountain1996 commentedon Sep 4, 2024
The
::cast syntax is specific to PostgreSQL. You can usecast(<expr> AS <type>)in sqlite.EwenQuim commentedon Sep 4, 2024
Indeed, it works. Thank you very much !
Why isn't it the same API between databases ? I guess because of RDBMS compatibility (even if sqlc.xxx functions are not natively supported by the platforms...).
Another issue that #2800 will solve, I guess
Now, while #2800 is still not implemented, we should at least
maddsua commentedon May 22, 2026
This, however, is not a universal solution, as type casting a custom type that is intended for passing into a json* function will completely break this type.
Take this for an example:
ScanandValuemethods.But now comes the kicker: the
cast(sqlc.arg(id_set) as uuidsfails miserably, as sqlite doesn't know about your custom type. As the result it just turns it into a zero integer which gets passed to thejson_each, breaking the logic.Relevant repl example: