Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to run a parameterized query from the PostgreSQL command line

I want to run the following parameterized query from the psql command line:

SELECT *
FROM users
WHERE username = :username;

How do I set the username parameter on the command line?

I tried this:

\set username 'john'

But when I run the query, I get the following error message:

ERROR:  column "john" does not exist
LINE 3: WHERE username = john;
                         ^
like image 252
Naresh Avatar asked Nov 24 '15 23:11

Naresh


People also ask

How do I create a parameterized view in PostgreSQL?

To create a PostgreSQL view, we use the CREATE VIEW statement. Here is the syntax for this statement: CREATE [OR REPLACE] VIEW view-name AS SELECT column(s) FROM table(s) [WHERE condition(s)]; The OR REPLACE parameter will replace the view if it already exists.


1 Answers

Per the psql documentation, to substitute a psql variable into a string as a literal, use :'variablename'

This isn't really a parameterised query in the usual sense, as the variable is interpolated into the query string. psql knows to escape single quotes, though, so a variable value ');DROP TABLE users;-- will appear literally instead of ending the string and running unwanted SQL.

like image 171
Craig Ringer Avatar answered Nov 15 '22 15:11

Craig Ringer