Daniel Moerner

Where can you use a PostgreSQL parameter placeholder?

This blog post is mostly a note to future me. As I work on my latest Go project etracker, I’ve been using PostgreSQL for the first time, using the excellent pgx driver.

When writing dynamic statements, best practice is to use a parameter placeholder:

_, err = dbpool.Exec(context.Background(), 
    `INSERT INTO peers (peer_id, ip_port, info_hash) 
    VALUES ($1, $2, $3) 
    ON CONFLICT (peer_id, info_hash) 
    DO UPDATE SET ip_port = $2;`,
    announce.peer_id, announce.ip_port, announce.info_hash)

Although the pgx driver supports this with variable parameter methods, it’s just an abstraction on top of the underlying PostgreSQL PREPARE statement:

postgres=# PREPARE s AS SELECT $1 AS a;
PREPARE
postgres=# EXECUTE s(1+2);
 a
---
 3
(1 row)

However, through trial and error, I’ve learned that you cannot always use parameter placeholders for all of the dynamic statements you might want to execute. For example, suppose you want to drop a table whose name is stored in a variable. You can’t do this with a parameter placeholder, because the following fails with an error:

postgres=# PREPARE s AS DROP TABLE $1 AS d;
ERROR:  syntax error at or near "DROP"

Or suppose you need to insert into a string literal, like when you’re manipulating dates. The following gives the wrong answer, but does not give any error at all!

postgres=# PREPARE s AS SELECT NOW() - INTERVAL '$1' AS i;
PREPARE
postgres=# EXECUTE s('1 hour');
              i               
------------------------------
 2024-12-11 21:19:09.51118+00
(1 row)
postgres=# SELECT NOW();
              now              
-------------------------------
 2024-12-11 21:19:52.488616+00
(1 row)

After searching in the official documentation I could not find an answer for when parameter placeholders are allowed (although I’m sure it’s there and would love a pointer to where). However, I did find this site which provides an answer in passing: https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/

You can use parameter placeholders like $1 instead of an unknown or variable value. But there are certain restrictions:

  1. You can use parameters only with the statements SELECT, INSERT, UPDATE, DELETE and VALUES.
  2. You can only use parameters instead of constants (literals). You can’t use parameters instead of identifiers (object names) or keywords, among other things.

Note that the second constraint entails that you can’t use constants inside of literals, but only instead of literals. That’s what went wrong in the INTERVAL case.

So now that I know, how do I accommodate this in my code? I have to prepare the statement in an intermediate step using Go’s built-in tools. For example, this is how I’m approaching INTERVAL right now:

query := fmt.Sprintf(`SELECT ip_port FROM peers 
        WHERE info_hash = $1 
        AND peer_id <> $2 
        AND last_announce >= NOW() - INTERVAL '%s';`, 
    interval)
rows, err := dbpool.Query(context.Background(), query, a.info_hash, a.peer_id)

This is obviously a little awkward, but I liked the idea of using PostgreSQL parameter placeholders when I can instead of doing it all with fmt.Sprintf. However, even as I write this blog post I find the separation of the parameters from their locations too fragile, so I think the way forward is just to use fmt.Sprintf for preparing the entire query.