Postgresql – Why doesn’t the $1 syntax work for all queries

copyparameterpostgresql

I tried to do this:

COPY postgres_log FROM $1 WITH csv;

And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives:

ERROR:  syntax error at or near "$1"

Um… So does that mean that I'm forced to send it a scary string like this?

COPY postgres_log FROM '/full/path/to/logfileblablabla.csv' WITH csv;

If so, why? Why don't all query types support the parameterized queries interface? Isn't this like asking to enable SQL query injection vulnerabilities?

Best Answer

Yes, you cannot use parameters with COPY.

Why? Because nobody has implemented it. Feel free to come up with a patch.

Currently only SELECT, INSERT, UPDATE and DELETE support parameters.

You can use the format function with the %L format to comfortably construct an SQL string with a string literal while avoiding the danger of SQL injection.