How to custom the interval data type?

[katatrina]

I have this query:

-- name: CreateSnippet :one
INSERT INTO snippets (title, content, created_at, expires)
VALUES($1, $2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '7 days')
RETURNING *;

I want the method CreateSnippet also taking a day number (like 7) as an argument or even the whole interval as an argument. What should I do?

[orisano]

Please use MAKE_INTERVAL or ($3 * INTERVAL '1 day')
https://stackoverflow.com/a/74481697

[andrewmbenton]

If you use pgx/v5 as your driver then it looks like sqlc will give you a pgtype.Interval to use as a parameter, which is probably pretty comfortable to use. https://play.sqlc.dev/p/0ae27106633b32760f9c1072009605e1b294a1ed7563290509c7daa462796a31

If you're not using pgx/v5 then I think the above suggestion from @orisano to use MAKE_INTERVAL is best: https://play.sqlc.dev/p/147a2e77fe7e64ffd21af152bb67e447b616bf388410a225df25bde7ed1f0f5e

[soasada]

hi, I tried the approach with a SELECT query like the following:

SELECT *
FROM table
WHERE create_date >= NOW() - INTERVAL $1

And is failing with sqlc 1.26.0 with the following error:
ERROR: syntax error at or near \"$1\" (SQLSTATE 42601).

I'm using the generated code:

queries.TestQuery(ctx, pgtype.Interval{Days: 7, Valid: true})

Do you know what I'm missing?

[brahmlower]

I had the same issue as @soasada and solved it by type casting the parameter like:

SELECT *
FROM table
WHERE create_date >= NOW() - $1::INTERVAL

Versions:

• pgx v5.7.4
• sqlc v1.29.0
• go 1.24.2
• postgresql 17.5