Error while using placeholders with generate_series and intervals

[jreyeshdez]

I am having issues while trying to build a SQL using placeholders in intervals.

I have following piece of code:

seriesStatement := " FROM generate_series(date_trunc($1, current_timestamp - interval $2::interval), 
date_trunc($3, current_timestamp - interval $4::interval), $5::interval) as series 
LEFT JOIN %s ON %s = series" 

seriesTimeTick := fmt.Sprintf("%s", "1 "+timeTickSql[tick])

sqlStatement += fmt.Sprintf(seriesStatement, fmt.Sprintf(fromTableSql[tick], backendType), seriesSelector[tick])

args = append(args,
    "day",
    fmt.Sprintf("%s", strconv.Itoa(fromVal)+" day"),
    "day",
    "0 day",
    seriesTimeTick,
)

rows, err := postgres.Query(q, args...)

I get pq: syntax error at or near "$2" over and over

I have also tried using '$2'::interval but still getting same issue. Also went through the existing closed issues and tried to follow what it was described in them however I still have no success.

Everything could be replaced by using string replacement Sprintf but I'd like to move it to placeholders instead.

The resulting query would look like:

SELECT series as ts, COALESCE(SUM(status), 0) 
FROM generate_series(date_trunc('day', current_timestamp - interval '10 day'::interval), 
date_trunc('day', current_timestamp - interval '0 day'::interval), '1 day'::interval) as series 
LEFT JOIN test_table sd ON DATE(TIMEZONE('UTC'::text, sd.start)) = series 
AND sd.id = '12345678' GROUP BY series ORDER BY series ASC;

Am I missing something?

[cbandy]

The interval 'n' syntax doesn't work for prepared statements. I expected interval '10 day'::interval would be a syntax error, but I think it is being interpreted as (interval '10 day')::interval.

Use $2::interval which is a type cast.

psql> prepare x AS select current_timestamp - interval $1;
ERROR:  syntax error at or near "$1"
LINE 1: prepare x AS select current_timestamp - interval $1;

psql> prepare x AS select current_timestamp - $1::interval;
PREPARE

[jreyeshdez]

Changing to this does the trick. Thanks.

seriesStatement := ` FROM generate_series(date_trunc($1, current_timestamp - $2::interval), 
    date_trunc($3, current_timestamp - $4::interval), $5::interval) as series 
    LEFT JOIN %s ON %s = series
`